查看数据库版本和系统:
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 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production
归档方式查看:
SQL> archive log list;
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 40
Current log sequence 42
(1)使用STATICS收集信息的测试步骤和结果:
SQL> set autotrace on statistics
SQL> create table test as select * from all_objects where 1=2;
Table created.
SQL> insert into test select * from all_objects;
49562 rows created.
Statistics
----------------------------------------------------------
7267 recursive calls
4069 db block gets
37726 consistent gets
0 physical reads
5582676 redo size
678 bytes sent via SQL*Net to client
579 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
21 sorts (memory)
0 sorts (disk)
49562 rows processed
--使用HINT附加
SQL> insert /*+ append */ into test select * from all_objects;
49562 rows created.
Statistics
----------------------------------------------------------
5491 recursive calls
771 db block gets
35087 consistent gets
0 physical reads
11384 redo size
664 bytes sent via SQL*Net to client
594 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
6 sorts (memory)
0 sorts (disk)
49562 rows processed
修改表LOG模式
SQL> alter table test nologging;
Table altered.
SQL> insert /*+ append */ into test select * from all_objects;
49562 rows created.
Statistics
----------------------------------------------------------
5661 recursive calls
746 db block gets
35245 consistent gets
0 physical reads
9992 redo size
664 bytes sent via SQL*Net to client
593 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
5 sorts (memory)
0 sorts (disk)
49562 rows processed
加索引的情况:
SQL> create index ind_test on test (object_id);
Index created.
SQL> insert /*+ append */ into test select * from all_objects;
49563 rows created.
Statistics
----------------------------------------------------------
5939 recursive calls
8312 db block gets
36157 consistent gets
712 physical reads
7715620 redo size
665 bytes sent via SQL*Net to client
593 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
9 sorts (memory)
1 sorts (disk)
49563 rows processed
(2)使用统计视图显示回滚段大小
这个测试结果可疑的很,注意不要使用auto trace来查看redo size,这个值是有偏差的.
看看另外一个测试结果:首先建立这样一个视图给所有用户用:
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';
--------------------
View created.
SQL> create table test2 as select * from dba_objects where 1=2;
Table created
SQL> select * from redo_size;
VALUE
----------
36284
SQL> insert into test2 select * from dba_objects;
50009 rows inserted
SQL> select * from redo_size;
VALUE
----------
5673156
SQL> insert /*+ append */ into test2 select * from dba_objects;
50009 rows inserted
SQL> select * from redo_size;
VALUE
----------
5682932
SQL> select (5682932 -5673156) redo2,(5673156 -36284) redo1 from dual;
REDO2 REDO1
---------- ----------
9776 5636872
SQL> alter table test2 nologging;
Table altered
SQL> select * from redo_size;
VALUE
----------
5685500
SQL> insert /*+ append */ into test2 select * from dba_objects;
50009 rows inserted
SQL> select * from redo_size;
VALUE
----------
5698760
SQL> select (4491892 -3408128) redo2,(3408128 -2320828) redo1 ,(5698760 -5685500) redo3 from dual;
REDO2 REDO1 REDO3
---------- ---------- ----------
1083764 1087300 13260
EYGLE大师的结论和实验:http://www.eygle.com/faq/Nologging&append.htm
总结后就是一句话:
在归档模式下,对于常规表的insert append产生和insert同样的redo,此时的insert append实际上并不会有性能提高.
只有在表Nologging的情况下并且append才能减少redo,提升性能。
在非归档模式下,同样只有在表Nologging的情况下的append才能减少redo的生成.