ORACLE数据库性能优化之表的NOLOGGING

来源：互联网发布：3.35 魔兽数据库编辑：程序博客网时间：2024/05/22 11:13

通过设置表的NOLOGGING来产生更少的REDO

ORACLE数据库会对产生改变的操作记录REDO，比如DDL语句、DML语句，这些操作首先会放在redo buffer中，然后由LGER进程根据触发条件写到联机日志文件，如果数据库开启归档的话，还要在日志切换的时候归档。在这样一个完整的链条上的每一个环节，都可能会成为性能的瓶颈，所以需要引起DBA和数据库应用人员的注意。

对于我们来说，最好的调优方式就是不产生REDO或者尽量少的产生REDO。

这里我们需要搞明白，有些操作不管你怎样都会产生REDO的，比如普通的DML语句（insert、update、delete等），而有些操作我们是可以考虑不产生redo或者少产生redo的。

比如：1、创建或者重建索引
2、直接路径装载数据，比如SQL*Loader的direct=y方式、insert into APPEND
3、CTAS方式创建表时

以上列出的不是所有的操作。

下面是一个小的例子，当我们把一个表设置成NOLOGGING模式的时候，通过合适的操作，可以让oracle产生很少的REDO。

首先确认数据库处于归档模式
system@DB01> conn / as sysdba
Connected.
sys@DB01> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     174
Next log sequence to archive   176
Current log sequence           176

一、如果表采用默认的方式，也就是记录日志的方式，不管你是否使用insert into， oracle产生的日志都会很多
scott@DB01> conn system/oracle
Connected.

system@DB01> create table tj as select * from dba_objects where 1=2;
Table created.

system@DB01> select count(*) from tj;

COUNT(*)
----------
0

system@DB01> select table_name,logging from user_tables where table_name='TJ';
TABLE_NAME LOG
------------------------------ ---
TJ YES

通过autotrace统计redo生成
system@DB01> set autotrace trace stat

1.不采用append 提示
system@DB01> insert into tj select * from dba_objects;
50330 rows created.

Statistics
----------------------------------------------------------
       1795 recursive calls
       4188 db block gets
       6913 consistent gets
          8 physical reads
    5690832 redo size
       1131 bytes sent via SQL*Net to client
       1199 bytes received via SQL*Net from client
          6 SQL*Net roundtrips to/from client
          4 sorts (memory)
          0 sorts (disk)
      50330 rows processed

system@DB01> rollback;
Rollback complete.

2.采用append 提示的方式
system@DB01> insert --+append

into tj select * from dba_objects;
50330 rows created.

Statistics
----------------------------------------------------------
        228 recursive calls
        762 db block gets
       5392 consistent gets
          0 physical reads
    5712972 redo size
       1117 bytes sent via SQL*Net to client
       1211 bytes received via SQL*Net from client
          6 SQL*Net roundtrips to/from client
          1 sorts (memory)
          0 sorts (disk)
      50330 rows processed

system@DB01> rollback;
Rollback complete.

通过以上1和2两种不同方式的比较，我们发现产生的日志量是差不多的1.5690832 redo size 2.5712972 redo size

二、修改表为不记录日志，这个时候insert into就会体现出他的优势
system@DB01> alter table tj nologging;
Table altered.

1.不采用append 提示
system@DB01> insert into tj select * from dba_objects;
50330 rows created.

Statistics
----------------------------------------------------------
        211 recursive calls
       2732 db block gets
       6770 consistent gets
          0 physical reads
    5542844 redo size
       1136 bytes sent via SQL*Net to client
       1199 bytes received via SQL*Net from client
          6 SQL*Net roundtrips to/from client
          6 sorts (memory)
          0 sorts (disk)
      50330 rows processed

system@DB01> rollback;
Rollback complete.

2.采用append 提示的方式
system@DB01> insert --+append

into tj select * from dba_objects;
50330 rows created.

Statistics
----------------------------------------------------------
          8 recursive calls
        698 db block gets
       5328 consistent gets
          0 physical reads
       1524 redo size
       1121 bytes sent via SQL*Net to client
       1211 bytes received via SQL*Net from client
          6 SQL*Net roundtrips to/from client
          1 sorts (memory)
          0 sorts (disk)
      50330 rows processed

通过以上1和2两种不同方式的比较，我们发现产生的日志量APPEND的方式明显会少，1.5542844 redo size 2. 1524 redo size。

根据上面的实验我们可以发现，为了通过减少REDO而提高语句的性能，我们要满足两个条件1.表NOLOGGING 2.在语句中使用APPEND提示。

当然我们这里只是在强调性能，作为一个DBA，在性能和安全之间一定要做一个平衡，当你选择了NOLOGGING的时候，由于表是不记录日志的，那如果数据库崩溃，这些数据是不能被恢复的。