Oracle DML产生 REDO SIZE测试

yqlanmdgzz

--DML redo测试
/*
*普通表的redo size 为insert 5M,delete 18m, update 24M
*临时表redo size 为insert 276096字节， Delete 为15M ,update 为 8M,
*全局临时表与基于事务的临时表 产生的redo size差不多。
*
*/
/*
*另外的备注，若普通表DML每一步，不commit。测试的结果为 insert 5M, update 12M, delete 15M
*/
--需要sysdba权限
SQL> grant all on v_$mystat to admin;
授权成功。
SQL> grant all on v_$statname to admin;
授权成功。
--创建视图，访问当前session 的redo size
admin@ORCL> CREATE VIEW V_USER_REDO
2 AS
3 SELECT NAME,VALUE FROM V$MYSTAT CUR_STAT INNER JOIN V$STATNAME STATE_NAME ON
4 CUR_STAT.STATISTIC# = STATE_NAME.STATISTIC#
5 WHERE STATE_NAME.NAME = 'redo size';
视图已创建。
--创建表结构，不生成数据
admin@ORCL> CREATE TABLE T AS SELECT * FROM DBA_OBJECTS WHERE 1=0;
表已创建。
--创建临时表，用于insert 的数据
admin@ORCL> CREATE TABLE TMP_INSERT AS SELECT * FROM DBA_OBJECTS;
表已创建。
--查看当前redo
admin@ORCL> column name for a20
admin@ORCL> SELECT NAME,VALUE FROM V_USER_REDO;
NAME VALUE
-------------------- ----------
redo size 147500
--执行INSERT，查看产生的redo日志大小
admin@ORCL> INSERT INTO T SELECT * FROM TMP_INSERT;
已创建52373行。
admin@ORCL> COMMIT;
提交完成。
--查看redo，产生了5924328 字节日志,大约5M多大小
admin@ORCL> SELECT NAME,VALUE FROM V_USER_REDO;
NAME VALUE
-------------------- ----------
redo size 6071828

--查看DELETE产生的日志大小,为18936864字节的redo日志，大小约为18M大小
admin@ORCL> DELETE FROM T;
已删除52373行。
admin@ORCL> COMMIT;
提交完成。
admin@ORCL> SELECT NAME,VALUE FROM V_USER_REDO;
NAME VALUE
-------------------- ----------
redo size 25008692

--查看UPDATE产生的日志,重新插入数据.
--产生了24160520字节的日志,大约24M
admin@ORCL> DROP TABLE T PURGE;
表已删除。
admin@ORCL> CREATE TABLE T AS SELECT * FROM DBA_OBJECTS;
表已创建。
admin@ORCL> SELECT NAME,VALUE FROM V_USER_REDO;
NAME VALUE
-------------------- ----------
redo size 53034948
admin@ORCL> UPDATE T SET OBJECT_ID = 12;
已更新52373行。
admin@ORCL> COMMIT;
提交完成。
admin@ORCL> SELECT NAME,VALUE FROM V_USER_REDO;
NAME VALUE
-------------------- ----------
redo size 77195468
--测试临时表DML产生的redo size
--总体来看临时的redo 为 insert 276096字节，update 为 8M, Delete 为15M
--创建基于session的全局临时表
admin@ORCL> CREATE GLOBAL TEMPORARY TABLE T_TMP_SESSION ON COMMIT PRESERVE ROWS
2  AS
3  SELECT * FROM DBA_OBJECTS;
表已创建。
--基于事务的全局临时表
admin@ORCL> CREATE GLOBAL TEMPORARY TABLE T_TMP_TRANSACTION ON COMMIT DELETE ROWS
2  AS
3  SELECT * FROM DBA_OBJECTS;
表已创建。
--当前的redo size，分析全局临时表插入产生的redo size
admin@ORCL> select * from v_user_redo;
NAME                                                                  VALUE
---------------------------------------------------------------- ----------
redo size                                                             15268
admin@ORCL> insert into T_TMP_SESSION select * from dba_objects;
已创建50779行。
admin@ORCL> COMMIT;
提交完成。
--查看insert后的，全局redo大小为291468-15268 = 276200
admin@ORCL> select * from v_user_redo;
NAME                                                                  VALUE
---------------------------------------------------------------- ----------
redo size                                                            291468
--分析基于transaction insert产生的redo size，为276096-0 = 276096
--打开另一个session
admin@ORCL> select * from v_user_redo;
NAME                                                                  VALUE
---------------------------------------------------------------- ----------
redo size                                                                 0

admin@ORCL>  insert into T_TMP_TRANSACTION select * from dba_objects;
已创建50779行。
admin@ORCL> commit;
提交完成。
admin@ORCL> select * from v_user_redo;
NAME                                                                  VALUE
---------------------------------------------------------------- ----------
redo size                                                            276096
--分析update，全局临时表的redo size 为 8122436,而基于事务的临时表为 8122316
admin@ORCL> select * from v_user_redo;
NAME                                                                  VALUE
---------------------------------------------------------------- ----------
redo size                                                            276060
admin@ORCL> update t_tmp_session set object_id = rownum;
已更新50779行。
admin@ORCL> select * from v_user_redo;
NAME                                                                  VALUE
---------------------------------------------------------------- ----------
redo size                                                           8398496

admin@ORCL> select * from v_user_redo;
NAME                                                                  VALUE
---------------------------------------------------------------- ----------
redo size                                                            276100
admin@ORCL> update t_tmp_transaction set object_id = rownum;
已更新50779行。
admin@ORCL> select * from v_user_redo;
NAME                                                                  VALUE
---------------------------------------------------------------- ----------
redo size                                                           8398416
--delete产生的redo size 全局临时表的redo size 为 15056824 ,而基于事务的临时表为 15061984
admin@ORCL> select * from v_user_redo;
NAME                                                                  VALUE
---------------------------------------------------------------- ----------
redo size                                                           8398644
admin@ORCL> select count(*) from t_tmp_session;
COUNT(*)
----------
50779
admin@ORCL> delete t_tmp_session;
已删除50779行。
admin@ORCL> select * from v_user_redo;
NAME                                                                  VALUE
---------------------------------------------------------------- ----------
redo size                                                          23455468

admin@ORCL> select * from v_user_redo;
NAME                                                                  VALUE
---------------------------------------------------------------- ----------
redo size                                                           8398416
admin@ORCL> delete T_tmp_transaction;
已删除50779行。
admin@ORCL> select * from v_user_redo;
NAME                                                                  VALUE
---------------------------------------------------------------- ----------
redo size                                                          23460400