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/,如需转载,请注明出处,否则将追究法律责任。