[20230317]12c Temporary Tables and RedoUndo.txt

[20230317]12c Temporary Tables and RedoUndo.txt --//12c加入了temp_undo_enabled 参数,可以把临时表的产生的undo放在临时表空间.缺省设置是false. --//最近探究一套生产系统产生redo很大的问题，我突然想起来12c以后加入了temp_undo_enabled 参数， --//如果临时表上的数据记录多次发生dml，产生的记录到底是多少呢？我翻看了以前的测试记录，发现似乎临时表也是记录的undo信息。 --//我决定以前的测试重复测试看看。 1.建立测试环境： SCOTT@test01p> @ ver1 PORT_STRING                    VERSION        BANNER                                                                               CON_ID ------------------------------ -------------- -------------------------------------------------------------------------------- ---------- IBMPC/WIN_NT64-9.1.0           12.2.0.1.0     Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production              0 --//12c 加入了temp_undo_enabled 参数,可以把临时表的产生的undo放在临时表空间.缺省设置是false. --//sys用户下执行: SYS@test01p> grant select on v_$statname to scott; Grant succeeded. SYS@test01p> grant select on v_$mystat to scott; Grant succeeded. --//scott用户下执行: create table perm( x char(2000) ,  y char(2000) ,  z char(2000)  ); create global temporary table temp ( x char(2000) , y char(2000) , z char(2000)  ) on commit preserve rows; create or replace function get_stat_val( p_name in varchar2 ) return number as l_val number; begin     select b.value into l_val from v$statname a, v$mystat b where a.statistic# = b.statistic# and a.name = p_name; return l_val; end; / create or replace procedure do_sql( p_sql in varchar2 ) as     l_start_redo    number;     l_redo            number; begin     l_start_redo := get_stat_val( 'redo size' );     execute immediate p_sql;     commit;     l_redo := get_stat_val( 'redo size' ) - l_start_redo;     dbms_output.put_line     ( to_char(l_redo,'99,999,999') ||' bytes of redo generated for "' ||       substr( replace( p_sql, chr(10), ' '), 1, 25 ) || '"...' ); end; / 2.开始测试： set serveroutput on format wrapped alter session set temp_undo_enabled=true; begin     do_sql( 'insert into perm select 1,1,1 from all_objects where rownum <= 500' );     do_sql( 'insert into temp select 1,1,1 from all_objects where rownum <= 500' );     dbms_output.new_line;     do_sql( 'update perm set x = 2' );     do_sql( 'update temp set x = 2' );     dbms_output.new_line;     do_sql( 'delete from perm' );     do_sql( 'delete from temp' ); end; /   3,320,808 bytes of redo generated for "insert into perm select 1"...         384 bytes of redo generated for "insert into temp select 1"...   3,420,560 bytes of redo generated for "update perm set x = 2"...         384 bytes of redo generated for "update temp set x = 2"...   3,248,252 bytes of redo generated for "delete from perm"...         340 bytes of redo generated for "delete from temp"... PL/SQL procedure successfully completed. --//可以发现对于临时表生成的redo size都是一样的.无论是insert,update,delete.不像11g的情况. --//另外我以前忽略了一个细节无论是insert,update,delete，开启temp_undo_enabled=true的情况下，产生的redo大小都是38X。 --//delete 才340. --//这样可以得出实际上对于临时表产生的redo日志大部分也是记录在临时表空间里面的，redo size＝38X开销应该是别的因素导致的。 3.继续测试： --//注解commit. create or replace procedure do_sql( p_sql in varchar2 ) as     l_start_redo    number;     l_redo            number; begin     l_start_redo := get_stat_val( 'redo size' );     execute immediate p_sql; --// commit;     l_redo := get_stat_val( 'redo size' ) - l_start_redo;     dbms_output.put_line     ( to_char(l_redo,'99,999,999') ||' bytes of redo generated for "' ||       substr( replace( p_sql, chr(10), ' '), 1, 25 ) || '"...' ); end; / --//执行如下，这样insert，update，delete操作发生在一个事务里面。 set serveroutput on format wrapped alter session set temp_undo_enabled=true; variable a number ; variable b number ; begin     do_sql( 'insert into temp select 1,1,1 from all_objects where rownum <= 500' );     do_sql( 'update temp set x = 2' );     do_sql( 'delete from temp' );     :a := get_stat_val( 'redo size' );     commit ;     :b := get_stat_val( 'redo size' );     dbms_output.put_line ( to_char(:b - :a,'99,999,999')||' bytes of redo generated for commit ...' ); end; /  280 bytes of redo generated for "insert into temp select 1"...    0 bytes of redo generated for "update temp set x = 2"...    0 bytes of redo generated for "delete from temp"...  104 bytes of redo generated for commit ... PL/SQL procedure successfully completed. ---//可以发现几个特点,仅仅开始执行的insert存在少量redo,而后续在一个update,delete,没有产生redo.提交产生少量redo. --//commit换成rollback; SCOTT@test01p> insert into temp select 2,2,2 from all_objects where rownum <= 1; 1 row created. SCOTT@test01p> commit ; Commit complete. --//先插入1条记录. set serveroutput on format wrapped alter session set temp_undo_enabled=true; variable a number ; variable b number ; begin     do_sql( 'insert into temp select 1,1,1 from all_objects where rownum <= 500' );     do_sql( 'update temp set x = 2' );     do_sql( 'delete from temp' );     :a := get_stat_val( 'redo size' );     rollback ;     :b := get_stat_val( 'redo size' );     dbms_output.put_line ( to_char(:b - :a,'99,999,999')||' bytes of redo generated for rollback ...' ); end; /  280 bytes of redo generated for "insert into temp select 1"...    0 bytes of redo generated for "update temp set x = 2"...    0 bytes of redo generated for "delete from temp"...   88 bytes of redo generated forrollback ... PL/SQL procedure successfully completed. SCOTT@test01p> select count(*) from temp;   COUNT(*) ----------          1 --//使用rollback,也是小量日志,也就是仅仅开始进行dml事务操作时产生少量redo size. --//后续对于临时表产生dml的redo=0. 4.混合普通表与临时表的情况呢? set serveroutput on format wrapped alter session set temp_undo_enabled=true; begin     do_sql( 'insert into perm select 1,1,1 from all_objects where rownum <= 500' );     do_sql( 'insert into temp select 1,1,1 from all_objects where rownum <= 500' );     dbms_output.new_line;     do_sql( 'update perm set x = 2' );     do_sql( 'update temp set x = 2' );     dbms_output.new_line;     do_sql( 'delete from perm' );     do_sql( 'delete from temp' ); end; /   3,264,800 bytes of redo generated for "insert into perm select 1"...           0 bytes of redo generated for "insert into temp select 1"...   2,329,720 bytes of redo generated for "update perm set x = 2"...           0 bytes of redo generated for "update temp set x = 2"...   3,212,152 bytes of redo generated for "delete from perm"...           0 bytes of redo generated for "delete from temp"... PL/SQL procedure successfully completed. --//对于临时表一开始插入产生的redo size=0. set serveroutput on format wrapped alter session set temp_undo_enabled=true; begin     do_sql( 'insert into temp select 1,1,1 from all_objects where rownum <= 500' );     do_sql( 'insert into perm select 1,1,1 from all_objects where rownum <= 500' );     dbms_output.new_line;     do_sql( 'update temp set x = 2' );     do_sql( 'update perm set x = 2' );     dbms_output.new_line;     do_sql( 'delete from temp' );     do_sql( 'delete from perm' ); end; /         280 bytes of redo generated for "insert into temp select 1"...   3,267,624 bytes of redo generated for "insert into perm select 1"...           0 bytes of redo generated for "update temp set x = 2"...   4,995,972 bytes of redo generated for "update perm set x = 2"...           0 bytes of redo generated for "delete from temp"...   3,212,152 bytes of redo generated for "delete from perm"... PL/SQL procedure successfully completed. --//临时表先操作就是产生少量redo size. 5.如果临时表发生行迁移呢？ --//生产系统表还存在大量行迁移.测试临时表出现行迁移呢? --//drop  table temp; create global temporary table temp ( x varchar2(2000) , y varchar2(2000) , z varchar2(2000)  ) on commit preserve rows; set serveroutput on format wrapped alter session set temp_undo_enabled=true; variable a number ; variable b number ; begin     do_sql( 'insert into temp select 1,1,1 from all_objects where rownum <= 500' );     do_sql( 'update temp set y = lpad(2,2000,2)' );     do_sql( 'delete from temp' );     :a := get_stat_val( 'redo size' );     commit ;     :b := get_stat_val( 'redo size' );     dbms_output.put_line ( to_char(:b - :a,'99,999,999')||' bytes of redo generated for commit ...' ); end; /  280 bytes of redo generated for "insert into temp select 1"...    0 bytes of redo generated for "update temp set y = lpad("...    0 bytes of redo generated for "delete from temp"...  104 bytes of redo generated for commit ... PL/SQL procedure successfully completed. --//行迁移基本没有影响.redo大小基本不变. 6.总结: --//可以看出12c以后在设置temp_undo_enabled=true的情况下,善用临时表可以大大减少redo size的产生. --//另外之所以产生这个测试,主要是开发太不了解oracle了,把数据库当作垃圾场!! $ cat aa1.txt set numw 12 column id new_value v_id column item_id new_value v_item_id column scn1 new_value  v_scn1 column scn2 new_value  v_scn2 set term off select current_scn-1e4 scn1 from v$database; select id,max(item_id) item_id from LIS_RESULT where id = (select max(id) from LIS_RESULT ) group by id; host sleep &&1 select current_scn scn2 from v$database; set term on SELECT versions_starttime              ,versions_endtime              ,versions_xid              ,versions_operation              ,versions_startscn              ,versions_endscn              ,lis_result.id              ,lis_result.item_id FROM LIS_RESULT VERSIONS BETWEEN scn &v_scn1 and &v_scn2 --  FROM LIS_RESULT VERSIONS BETWEEN TIMESTAMP sysdate-1/1440 and sysdate    WHERE  id = &&v_id --and  item_id = &&v_item_id order by versions_startscn ; SYS@192.168.100.235:1521/orcl> @ aa1.txt 3 VERSIONS_STARTTIME   VERSIONS_ENDTIME     VERSIONS_XID     V VERSIONS_STARTSCN VERSIONS_ENDSCN           ID      ITEM_ID -------------------- -------------------- ---------------- - ----------------- --------------- ------------ ------------ 2023-03-01 09:12:59. 2023-03-01 09:12:59. 0A001E00B1D61800 I       44616226517     44616226539     26961902         3429 2023-03-01 09:12:59. 2023-03-01 09:12:59. 0A001E00B1D61800 I       44616226517     44616226536     26961902         3428 2023-03-01 09:12:59. 2023-03-01 09:12:59. 0A001E00B1D61800 I       44616226517     44616226527     26961902         3423 2023-03-01 09:12:59. 2023-03-01 09:12:59. 0A001E00B1D61800 I       44616226517     44616226530     26961902         3425 2023-03-01 09:12:59. 2023-03-01 09:12:59. 0A001E00B1D61800 I       44616226517     44616226542     26961902         3430 2023-03-01 09:12:59. 2023-03-01 09:12:59. 0A001E00B1D61800 I       44616226517     44616226533     26961902         3427 2023-03-01 09:12:59. 2023-03-01 09:12:59. 0A001E00B1D61800 I       44616226517     44616226548     26961902         3431 2023-03-01 09:12:59.                      060014008F631600 U       44616226527                     26961902         3423 2023-03-01 09:12:59.                      08000E0059E61600 U       44616226530                     26961902         3425 2023-03-01 09:12:59.                      0C0008005E9C1E00 U       44616226533                     26961902         3427 2023-03-01 09:12:59.                      0A001B005CD61800 U       44616226536                     26961902         3428 2023-03-01 09:12:59.                      0C001400349D1E00 U       44616226539                     26961902         3429 2023-03-01 09:12:59.                      0C000000009D1E00 U       44616226542                     26961902         3430 2023-03-01 09:12:59.                      0C000900479D1E00 U       44616226548                     26961902         3431 14 rows selected. --//insert,update基本在同一个时间点. --//先insert提交,然后每条记录做一个update后再提交. --//update对于真实表要在redo日志里面记录修改前后的image,这样导致update产生大量日志. --//这还要看update如何写. --//要想减少redo产生,理论讲就是先在内存组织好数据,然后在一次性全部插入,这样可以大大减少redo. --//而要实现这样的改动对于开发改动可能相对较多,往往不愿意做这类事情. --//而借助临时表代码改动小一些,先insert到临时表,不要提交,做后续的update操作,然后再一次性插入真实的表中,再提交. --//另外一个细节给注意临时表属性选择on commit delete rows. --//这样的改动相对较少,redo产生的也很少,而且还带来另外的好处,update后记录变长,一些记录会发生行迁移的情况,而像上面的操作 --//这样的情况可以完全避免. --//补充测试: create global temporary table temp ( x char(2000) , y char(2000) , z char(2000)  ) on commit preserve rows; set serveroutput on format wrapped alter session set temp_undo_enabled=true; variable a number ; variable b number ; begin     do_sql( 'delete from temp' );     do_sql( 'insert into temp select 1,1,1 from all_objects where rownum <= 500' );     do_sql( 'update temp set x = 2' );     do_sql( 'delete from temp' );     :a := get_stat_val( 'redo size' );     commit ;     :b := get_stat_val( 'redo size' );     dbms_output.put_line ( to_char(:b - :a,'99,999,999')||' bytes of redo generated for commit ...' ); end; /    0 bytes of redo generated for "delete from temp"...  280 bytes of redo generated for "insert into temp select 1"...    0 bytes of redo generated for "update temp set x = 2"...    0 bytes of redo generated for "delete from temp"...  104 bytes of redo generated for commit ... PL/SQL procedure successfully completed. --//这样定义临时表为on commit preserve rows也问题不大!! SCOTT@test01p> insert into temp select 2,2,2 from all_objects where rownum <= 500; 500 rows created. SCOTT@test01p> commit ; Commit complete. set serveroutput on format wrapped alter session set temp_undo_enabled=true; variable a number ; variable b number ; begin     do_sql( 'delete from temp' );     do_sql( 'insert into temp select 1,1,1 from all_objects where rownum <= 500' );     do_sql( 'update temp set x = 2' );     do_sql( 'delete from temp' );     :a := get_stat_val( 'redo size' );     commit ;     :b := get_stat_val( 'redo size' );     dbms_output.put_line ( to_char(:b - :a,'99,999,999')||' bytes of redo generated for commit ...' ); end; /  280 bytes of redo generated for "delete from temp"...    0 bytes of redo generated for "insert into temp select 1"...    0 bytes of redo generated for "update temp set x = 2"...    0 bytes of redo generated for "delete from temp"...  104 bytes of redo generated for commit ... PL/SQL procedure successfully completed. --//这样定义临时表为on commit preserve rows也问题不大!!