undo_redo_size

--1,版本
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE    10.2.0.1.0      Production
TNS for Linux: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production

--2,归档模式
SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     441
Next log sequence to archive   443
Current log sequence           443

--改为非归档模式
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.

Total System Global Area 1207959552 bytes
Fixed Size                  1219088 bytes
Variable Size             184550896 bytes
Database Buffers         1006632960 bytes
Redo Buffers               15556608 bytes
Database mounted.
SQL> alter database noarchivelog;

Database altered.

SQL> alter database open;

Database altered.

SQL> archive log list
Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     448
Current log sequence           450

--3,使用如下语句检测redo size,undo size:
SELECT sn.NAME, st.VALUE
  FROM v$statname sn, v$sesstat st
 WHERE sn.statistic# = st.statistic#
   AND sn.NAME in('redo size','undo change vector size')
   AND st.sid = 153
 ORDER BY sn.NAME;

--4,统计结果：
归档模式
                                    table(logging)            table(nologging)
insert1--redo size:                 5794304                   5792616
insert1--undo size:                 233140                    232760
insert2--redo size(/*+ append */):  5809932                   11620       <<<<<<<<<<
insert2--undo size(/*+ append */):  2912                      2656        <<<<<<<<<<
insert3--redo size(/*+ parallel */):5792484                   5791292
insert3--undo size(/*+ parallel */):232896                    232368
insert4--redo size(/*+ parallel */):49956                     49916       <<<<<<<<<<
insert4--undo size(/*+ parallel */):34156                     34108       <<<<<<<<<<
ctas5  --redo size:                 5860048                   65668       <<<<<<<<<<
ctas5  --undo size:                 17600                     18420       <<<<<<<<<<
update6--redo size:                 28551176                  28550960
update6--undo size:                 12059276                  12059160
delete7--redo size:                 40180840                  40181172
delete7--undo size:                 25332232                  25332596

非归档模式
                                    table(logging)         table(nologging)
insert1--redo size:                 5791988                   5792344
insert1--undo size:                 232644                    232816
insert2--redo size(/*+ append */):  11812                     11812       <<<<<<<<<<
insert2--undo size(/*+ append */):  2732                      2732        <<<<<<<<<<
insert3--redo size(/*+ parallel */):5792332                   5792540
insert3--undo size(/*+ parallel */):232644                    232988
insert4--redo size(/*+ parallel */):50020                     49808       <<<<<<<<<<
insert4--undo size(/*+ parallel */):34156                     34108       <<<<<<<<<<
ctas5  --redo size:                 62652                     61992       <<<<<<<<<<
ctas5  --undo size:                 17408                     17232       <<<<<<<<<<
update6--redo size:                 28580192                  28565588
update6--undo size:                 12064544                  12061852
delete7--redo size:                 40195896                  40279532
delete7--undo size:                 25335096                  25364372

其中sql如下：
create table test0 as select * from dba_objects;
create table test [nologging] as select * from test0 where rownum<1;
--create table test_nolog nologging as select * from test where rownum<1;
insert1:insert into test select * from test0;
insert2:insert /*+ append */ into test select * from test0;
insert3:insert /*+ parallel(t 4) */ into test t select * from test0;
insert4:alter session enable parallel dml;
        insert /*+ parallel(t 4) */ into test t select * from test0;
ctas5:create table test [nologging] as select * from test0;
update6:update test set wner='a';
delete7:delete from test;

总结如下：
1，undo change vector size:
  在append插入(包括append hint、parallel hint、CTAS)方式下undo change vector size大大减少，与数据库是否归档模式、表是否logging无关；
  其他dml操作所生成undo change vector size无法减少。
2，redo size：
   在非归档模式下，在append插入(包括append hint、parallel hint、CTAS)方式下redo size大大减少，与表是否logging无关；
   在归档模式下，在append插入(包括append hint、parallel hint、CTAS)方式下且表为nologging时redo size大大减少，logging属性时redo size与append无关。

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/18922393/viewspace-710384/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/18922393/viewspace-710384/