Nologging到底何时才能生效?

清疚 2022-09-30 03:48 7阅读 0赞

查看数据库版本和系统：  
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][http_www.eygle.com_faq_Nologging_append.htm]

**总结后就是一句话：**

在归档模式下，对于常规表的insert append产生和insert同样的redo，此时的insert append实际上并不会有性能提高.

只有在表Nologging的情况下并且append才能减少redo，提升性能。

在非归档模式下，同样只有在表Nologging的情况下的append才能减少redo的生成.

[http_www.eygle.com_faq_Nologging_append.htm]: http://www.eygle.com/faq/Nologging&append.htm