[20241002]21c Temporary Tables and RedoUndo.txt

来源：这里教程网时间：2026-03-03 20:41:13 作者：

[20241002]21c Temporary Tables and RedoUndo.txt --//[20230317]12c Temporary Tables and RedoUndo.txt，以前的测试，正好有朋友问及类似的问题，在21c下重复测试看看。 --//我发现21c下TEMP_UNDO_ENABLED=false，我奇怪设置TEMP_UNDO_ENABLED=true能大大减少redo生成，为什么oracle经过许多版本的升 --//级，缺省设置false,有点不理解为什么？ --//对于临时表dml对比普通表产生的日志较少，但是临时表的dml操作oracle要记录回滚操作，要记录前映像(注不需要记录后映像)这样 --//根据操作的类型产生的日志不同。 --//对于insert操作，要回滚仅仅需要记录rowid，因为delete操作知道rowid就可以了。 --//对于update操作，要回滚要记录修改前映像，要看修改字段多少以及原来的占用空间，如果修改字段很多，日志相对就大。 --//如果修改前是null，修改后日子相对较少。 --//对于delete操作，要回滚要记录整个记录信息，这样记录的日志相对要大。 --//以上设置TEMP_UNDO_ENABLED=false的情况，如果设置TEMP_UNDO_ENABLED=true，日记记录在临时表空间，产生的日志大大减少。 1.环境： SCOTT@book01p> @ver2 ============================== PORT_STRING : x86_64/Linux 2.4.xx VERSION : 21.0.0.0.0 BANNER : Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production BANNER_FULL : Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production Version 21.3.0.0.0 BANNER_LEGACY : Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production CON_ID : 0 PL/SQL procedure successfully completed. --//sys用户pdb=book01p下执行: SYS@book01p> grant select on v_$statname to scott; Grant succeeded. SYS@book01p> 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.开始测试： --//temp_undo_enabled=true. 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,323,140 bytes of redo generated for "insert into perm select 1"... 384 bytes of redo generated for "insert into temp select 1"... 4,837,020 bytes of redo generated for "update perm set x = 2"... 384 bytes of redo generated for "update temp set x = 2"... 3,251,288 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开销应该是别的因素导致的。 --//如果生产系统有这样需求，使用临时表设置TEMP_UNDO_ENABLED=true大大减少日志的产生。 --//temp_undo_enabled=false set serveroutput on format wrapped alter session set temp_undo_enabled=false; 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,271,828 bytes of redo generated for "insert into perm select 1"... 72,548 bytes of redo generated for "insert into temp select 1"... 4,699,412 bytes of redo generated for "update perm set x = 2"... 1,108,232 bytes of redo generated for "update temp set x = 2"... 3,251,932 bytes of redo generated for "delete from perm"... 3,225,500 bytes of redo generated for "delete from temp"... PL/SQL procedure successfully completed. --//在temp_undo_enabled=false的情况下，回到11g下的模式，对于临时表insert产生的日志不大，因为前映像仅仅rowid，知道rowid， --//回滚操作delete就已经足够了.而update要记录前映像x的值，注意类型char(2000)，产生的redo不少，如果修改字段很多，产生的日 --//志更大。而delete操作要记录的前映像是整个记录，产生的日志相对大一些。而且仔细看delete perm，temp表两者产生的日志相近。 --//对于正常表，delete的后映像是记录rowid就可以，两者很接近。 --//注意看正常表perm，update要记录前后映像，产生的日志有4,699,412/1,108,232 = 4.24 倍的差距。 --//许多开发不了解这些细节，把数据库当作垃圾桶，产生大量垃圾日志。 --//修改3个字段的情况： --//temp_undo_enabled=false set serveroutput on format wrapped alter session set temp_undo_enabled=false; 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,y=2,z=2' ); do_sql( 'update temp set x = 2,y=2,z=2' ); end; / 3,275,900 bytes of redo generated for "insert into perm select 1"... 72,548 bytes of redo generated for "insert into temp select 1"... 6,428,360 bytes of redo generated for "update perm set x = 2,y=2"... 3,961,572 bytes of redo generated for "update temp set x = 2,y=2"... PL/SQL procedure successfully completed. --//注意修改字段比较多，开始记录占用空间大，导致临时表update生产的日志不少，甚至大于前面的delte操作。 --//修改前x=NULL --//temp_undo_enabled=false set serveroutput on format wrapped alter session set temp_undo_enabled=false; begin do_sql( 'insert into perm select null,1,1 from all_objects where rownum <= 500' ); do_sql( 'insert into temp select null,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' ); end; / 2,286,464 bytes of redo generated for "insert into perm select n"... 72,548 bytes of redo generated for "insert into temp select n"... 1,549,288 bytes of redo generated for "update perm set x = 2"... 80,616 bytes of redo generated for "update temp set x = 2"... PL/SQL procedure successfully completed. --//修改前x字段记录的null，这样对于临时表回滚记录的信息就很少，update临时表产生的日志就很少。 --//而普通表后映像记录的占用空间大，这样普通表产生的日志就很大。 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操作发生在一个事务里面。 --//temp_undo_enabled=true 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. 4.如果临时表发生行迁移呢？ --//生产系统表还存在大量行迁移.测试临时表出现行迁移呢? --//drop table temp; 要退出才能drop，否则报如下错误。 SCOTT@book01p> drop table temp; drop table temp * ERROR at line 1: ORA-14452: attempt to create, alter or drop an index on temporary table already in use SCOTT@book01p> create global temporary table temp ( x varchar2(2000) , y varchar2(2000) , z varchar2(2000) ) on commit preserve rows; Table created. 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大小基本不变. 5.测试中遇到的问题，如果会话中间修改temp_undo_enabled设置，好像不起作用。 SCOTT@book01p> drop table temp; Table dropped. SCOTT@book01p> create global temporary table temp ( x char(2000) , y char(2000) , z char(2000) ) on commit preserve rows; Table created. set serveroutput on format wrapped alter session set temp_undo_enabled=false; 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; / 72,444 bytes of redo generated for "insert into temp select 1"... 1,149,340 bytes of redo generated for "update temp set x = 2"... 3,217,392 bytes of redo generated for "delete from temp"... 132 bytes of redo generated for commit ... PL/SQL procedure successfully completed. 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; / 72,444 bytes of redo generated for "insert into temp select 1"... 1,111,436 bytes of redo generated for "update temp set x = 2"... 3,216,232 bytes of redo generated for "delete from temp"... 132 bytes of redo generated for commit ... PL/SQL procedure successfully completed. --//这种情况在生产系统很难遇到。 --//原始的链接还做了许多测试，大家可以看原始的链接[20230317]12c Temporary Tables and RedoUndo.txt 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' ); dbms_session.sleep(5) ; do_sql( 'update temp set x = 2' ); dbms_session.sleep(5) ; do_sql( 'delete from temp' ); dbms_session.sleep(5) ; :a := get_stat_val( 'redo size' ); commit ; dbms_session.sleep(5) ; :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"... 132 bytes of redo generated for commit ... PL/SQL procedure successfully completed.