n
[@more@]
测试环境9i+XP,参考/showthread.php?threadid=242761&perpage=15&pagenumber=1
SQL> conn sys/oracle as sysdba;
Connected.
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination D:oracleoradatafoxarclog
Oldest online log sequence 17
Next log sequence to archive 18
Current log sequence 18
SQL> create or replace view redo_size as select value
from v$mystat, v$statname
where v$mystat.statistic# = v$statname.statistic#
and v$statname.name = 'redo size'
SQL> create user jane identified by jane default tablespace user_data;
User created.
SQL> grant connect to jane;
Grant succeeded.
SQL> grant resource to jane;
Grant succeeded.
SQL> grant dba to jane;
Grant succeeded.
此处可以看出autotrace是SESSION级别的
sys用户打开的autotrace对jane用户不起作用
SQL> set autotrace on statistics;
SQL> conn jane/jane
Connected.
SQL> select * from jane_test;
no rows selected
只有jane用户打开autotrace，才生效
SQL> set autotrace on statistics;
SQL> select * from jane_test;
no rows selected
Statistics
----------------------------------------------------------
0 recursive calls
1 db block gets
5 consistent gets
0 physical reads
40 redo size
1403 bytes sent via SQL*Net to client
534 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
0 rows processed
LOGGING模式下：SQL> alter table jane_test logging;
Table altered.
SQL> select * from jane_test;
no rows selected
Statistics
----------------------------------------------------------
144 recursive calls
0 db block gets
15 consistent gets
0 physical reads
0 redo size
1404 bytes sent via SQL*Net to client
534 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
3 sorts (memory)
0 sorts (disk)
0 rows processed
SQL> select * from redo_size;
select * from redo_size
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> select * from sys.redo_size;
VALUE
----------
5106920
Statistics
----------------------------------------------------------
164 recursive calls
0 db block gets
46 consistent gets
3 physical reads
0 redo size
865 bytes sent via SQL*Net to client
669 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
13 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> insert into jane_test select * from dba_objects;
6324 rows created.
Statistics
----------------------------------------------------------
207 recursive calls
578 db block gets
4265 consistent gets
0 physical reads
643508 redo size
623 bytes sent via SQL*Net to client
544 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
4 sorts (memory)
0 sorts (disk)
6324 rows processed
SQL> commit;
Commit complete.
SQL> select * from redo_size;
select * from redo_size
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> select * from sys.redo_size;
VALUE
----------
5750512
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
0 consistent gets
0 physical reads
0 redo size
866 bytes sent via SQL*Net to client
669 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> insert /*+ append */ into jane_test select * from dba_objects;
6324 rows created.
Statistics
----------------------------------------------------------
154 recursive calls
91 db block gets
4172 consistent gets
0 physical reads
654564 redo size
608 bytes sent via SQL*Net to client
557 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
6324 rows processed
SQL> commit;
Commit complete.
SQL> select * from sys.redo_size;
VALUE
----------
6405524
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
0 consistent gets
0 physical reads
0 redo size
866 bytes sent via SQL*Net to client
669 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
1 rows processed
LOGGING模式下
SQL> alter table jane_test nologging;
Table altered.
SQL> select * from sys.redo_size;
VALUE
----------
6407400
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
0 consistent gets
0 physical reads
0 redo size
864 bytes sent via SQL*Net to client
669 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> insert into jane_test select * from dba_objects;
6324 rows created.
Statistics
----------------------------------------------------------
171 recursive calls
420 db block gets
4219 consistent gets
0 physical reads
627740 redo size
623 bytes sent via SQL*Net to client
544 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
6 sorts (memory)
0 sorts (disk)
6324 rows processed
SQL> commit;
Commit complete.
SQL> select * from sys.redo_size;
VALUE
----------
7035252
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
0 consistent gets
0 physical reads
0 redo size
866 bytes sent via SQL*Net to client
669 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> insert /*+ append */ into jane_test select * from dba_objects;
6324 rows created.
Statistics
----------------------------------------------------------
28 recursive calls
13 db block gets
4122 consistent gets
0 physical reads
1792 redo size
608 bytes sent via SQL*Net to client
557 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
6324 rows processed
SQL> commit;
Commit complete.
SQL> select * from sys.redo_size;
VALUE
----------
7037520
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
0 consistent gets
0 physical reads
0 redo size
866 bytes sent via SQL*Net to client
669 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> select (5750512-5106920) redo1,(6405524-5750512) redo2,(7035252-6407400) redo3,(7037520-7035252) redo4 from dual;
REDO1 REDO2 REDO3 REDO4
---------- ---------- ---------- ----------
643592 655012 627852 2268
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
3 consistent gets
0 physical reads
0 redo size
1038 bytes sent via SQL*Net to client
754 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
1 rows processed
由此可见：归档模式下，只有NOLOGGING+APPEND才能减少redo_size
补充：append方式是并行方式，一定要提交后才能执行其他的insert操作，测试如下：
SQL> insert into jane_test select * from dba_objects;
6324 rows created.
SQL> insert /*+ append */ into jane_test select * from dba_objects;
6324 rows created.
SQL> insert /*+ append */ into jane_test select * from dba_objects;
insert /*+ append */ into jane_test select * from dba_objects
*
ERROR at line 1:
ORA-12838: cannot read/modify an object after modifying it in parallel
SQL> commit;
Commit complete.
SQL> insert /*+ append */ into jane_test select * from dba_objects;
6324 rows created.
SQL> commit;
Commit complete.
SQL> insert /*+ append */ into jane_test select * from dba_objects;
6324 rows created.
SQL> insert /*+ append */ into jane_test select * from dba_objects;
insert /*+ append */ into jane_test select * from dba_objects
*
ERROR at line 1:
ORA-12838: cannot read/modify an object after modifying it in parallel
SQL> insert into jane_test select * from dba_objects;
insert into jane_test select * from dba_objects
*
ERROR at line 1:
ORA-12838: cannot read/modify an object after modifying it in parallel
SQL> commit;
Commit complete.
关于create as(CTAS) 引自糖醋兔子的测试 （8i环境下）
noarchivelog下的CTAS 的redo＝noarchivelog下的CTAS nologging的redo ＝archivelog下的CTAS nologging的redo
这三种情况下，都对系统产生了较少的redo size
只有在archivelog 下CTAS，才产生了较多的redo size比较了CTAS和CTAS＋insert append后，实际上（archivelog mode）：
CTAS nologging redo ＝CTAS(no data) nologing +insert append redo size;
CTAS redo ＝CTAS(no data) +insert append redo size;
来自 “ ITPUB博客 ” ，链接：/8013558/viewspace-1028753/，如需转载，请注明出处，否则将追究法律责任。
