Oracle DML产生 REDO SIZE测试 博客分类： Oracle 优化

--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