随笔-314  评论-209  文章-0  trackbacks-0
alter table test nologging
insert /*+ append */ into test select
 
 
ask tom上有过一篇文章,是说Oracle实际上需要满足表是nologging和insert /*+append*/两个条件才真正实现nologging的
 
 
在insert数据量很大的时候(千万级),减少redo的产生对性能应该有很大的提高。
这是一个使用append和nologging对redo产生情况的实验。
结论:
-------------------------------
一、非归档模式下:
没有优化前    (1281372  redo size)
1、单一的使用nologging参数,对redo的产生没有什么影响。  (1214836  redo size)
2、单一的使用append提示,redo的减少很显著              (43872  redo size)
3、nologging+append,更显著                             (1108  redo size)
二、归档模式下:
没有优化前:           
1、单独使用nologging参数,(1231904  redo size)
2、单独使用append提示,  (1245804  redo size)
3、nologging + append,     (3748  redo size)

a、使用nologging参数并不代表在dml操作中,oracle不产生redo,只是对于指定表的更新数据不产生redo,但是oracle还是要记录这些操作,所以无论怎么优化,dml操作肯定要产生redo,但是使用这些参数对redo size的影响还是非常可观的。
b、单独使用nologging参数,对redo size没有多少影响,只有和append配合时,才能产生效果。
c、单独使用append提示,对redo的产生影响很大,这是我到现在都不明白的道理,按说append是绕过freelists,直接去寻找新块,能减少对freelists的争用,为什么会少这么多redo呢?
d、归档模式和非归档模式下,参数影响不一样,尤其是单独使用append参数时,看来oracle对归档模式下出于安全考虑还是要多一些。
文章出处:http://www.diybl.com/course/7_databases/oracle/Oracleshl/2008810/135707.html
 
 
1.Nologging的设置跟数据库的运行模式有关

a.数据库运行在非归档模式下:

SQL> archive log list;

Database log mode              No Archive Mode
Automatic archival             Enabled
Archive destination            /opt/oracle/oradata/hsjf/archive
Oldest online log sequence     155
Current log sequence           157

SQL> @redo

SQL> create table test as select * from dba_objects where 1=0;

Table created.

SQL> select * from redo_size;

     VALUE
----------
     63392

SQL>

SQL> insert into test select * from dba_objects;

10470 rows created.

SQL> select * from redo_size;

     VALUE
----------
   1150988

SQL>

SQL> insert into test select * from dba_objects;

10470 rows created.

SQL> select * from redo_size;

     VALUE
----------
   1152368

SQL> select (1152368 -1150988) redo_append,(1150988 -63392) redo from dual;

REDO_APPEND       REDO
----------- ----------
       1380    1087596

SQL> drop table test;

Table dropped. 

我们看到在Noarchivelog模式下,对于常规表的insert append只产生少量redo

b.在归档模式下

SQL> shutdown immediate

Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup mount

ORACLE instance started.

Total System Global Area  235999908 bytes

Fixed Size                   451236 bytes
Variable Size             201326592 bytes
Database Buffers           33554432 bytes
Redo Buffers                 667648 bytes
Database mounted.

SQL> alter database archivelog;

Database altered.

SQL> alter database open;

Database altered.

SQL> @redo

SQL> create table test as select * from dba_objects where 1=0;

Table created.

SQL> select * from redo_size;

     VALUE
----------
     56288

SQL>

SQL> insert into test select * from dba_objects;

10470 rows created.

SQL> select * from redo_size;

     VALUE
----------
   1143948

SQL>

SQL> insert into test select * from dba_objects;

10470 rows created.

SQL> select * from redo_size;

     VALUE
----------
   2227712

SQL> select (2227712 -1143948) redo_append,(1143948 -56288) redo from dual;

REDO_APPEND       REDO
----------- ----------
    1083764    1087660

SQL> drop table test;

Table dropped. 

我们看到在归档模式下,对于常规表的insert append产生和insert同样的redo
此时的insert append实际上并不会有性能提高.
但是此时的append是生效了的

通过Logmnr分析日志得到以下结果:

SQL> select operation,count(*)
  from v$logmnr_contents
  group by operation;

OPERATION                          COUNT(*)
-------------------------------- ----------
COMMIT                                   17
DIRECT INSERT                         10470 
INTERNAL                                 49
START                                    17

我们注意到这里是DIRECT INSERT,而且是10470条记录,也就是每条记录都记录了redo.

2.对于Nologging的table的处理

a. 在归档模式下:

SQL> create table test nologging as select * from dba_objects where 1=0;

Table created.

SQL> select * from redo_size;

     VALUE
----------
   2270284

SQL>

SQL> insert into test select * from dba_objects;

10470 rows created.

SQL> select * from redo_size;

     VALUE
----------
   3357644

SQL>

SQL> insert into test select * from dba_objects;

10470 rows created.

SQL> select * from redo_size;

     VALUE
----------
   3359024

SQL> select (3359024 -3357644) redo_append,(3357644 - 2270284) redo from dual;

REDO_APPEND       REDO
----------- ----------
       1380    1087360

SQL> drop table test;

Table dropped.  

我们注意到,只有append才能减少redo

b.在非归档模式下:

SQL> shutdown immediate

Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup mount

ORACLE instance started.

Total System Global Area  235999908 bytes
Fixed Size                   451236 bytes
Variable Size             201326592 bytes
Database Buffers           33554432 bytes
Redo Buffers                 667648 bytes
Database mounted.

SQL> alter database noarchivelog;

Database altered.

SQL> alter database open;

Database altered.

SQL> @redo

SQL> create table test nologging as select * from dba_objects where 1=0;

Table created.

SQL> select * from redo_size;

     VALUE
----------
     56580

SQL>

SQL> insert into test select * from dba_objects;

10470 rows created.

SQL> select * from redo_size;

     VALUE
----------
   1144148

SQL>

SQL> insert into test select * from dba_objects;

10470 rows created.

SQL> select * from redo_size;

     VALUE
----------
   1145528

SQL> select (1145528 -1144148) redo_append,(1144148 -56580) redo from dual;

REDO_APPEND       REDO
----------- ----------
       1380    1087568

SQL>

posted on 2010-12-07 17:24 xzc 阅读(1882) 评论(0)  编辑  收藏 所属分类: Oracle

只有注册用户登录后才能发表评论。


网站导航: