【优化】hints之/*+append*/作业

Hints之/*+append*/作业：

使用append提示进行insert叫做直接路径加载插入

特点：

1、使用/*+append*/提示系统直接在高水标记位以上插入数据，因此速度比较快。

2、它不记录日志，因此对于需要保证断电等意外情况发生能进行实例恢复的操作，不要进行此操作

3、/*+append*/后，需要马上提交，否则会影响下一次修改失败（insert,update,delete）

4.数据库是否归档及是日志是longging（默认），还是nologging对redo日志生成量有影响；

——检查是否归档;

SYS@ORA11GR2>archive log list;

Database log mode              Archive Mode

Automatic archival             Enabled

Archive destination            USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence     17

Next log sequence to archive   19

Current log sequence           19

 

——创建表t：

SYS@ORA11GR2>create table t as select * from all_objects;

 

Table created.

 

——查询表t的logging属性为yes（默认记录日志）

SYS@ORA11GR2>select table_name,logging from user_tables where table_name='T';

 

TABLE_NAME                     LOG

------------------------------ ---

T                              YES

 

——打开trace功能以显示执行语句的sql执行计划；

SYS@ORA11GR2>set autot on

SYS@ORA11GR2>insert into t select * from t;

 

85178 rows created.

 

 

Execution Plan

----------------------------------------------------------

Plan hash value: 1601196873

 

--------------------------------------------------------------------------------

-

 

| Id  | Operation                | Name | Rows  | Bytes | Cost (%CPU)| Time

|

 

--------------------------------------------------------------------------------

-

 

|   0 | INSERT STATEMENT         |      |   163K|    24M|   661   (1)| 00:00:08

|

 

|   1 |  LOAD TABLE CONVENTIONAL | T    |       |       |          |

|

 

|   2 |   TABLE ACCESS FULL      | T    |   163K|    24M|   661   (1)| 00:00:08

|

 

--------------------------------------------------------------------------------

-

 

 

Note

-----

   - dynamic sampling used for this statement (level=2)

 

 

Statistics

----------------------------------------------------------

         29  recursive calls

       6830  db block gets

       3757  consistent gets

        914  physical reads

    9941336  redo size（日志大小）

       1133  bytes sent via SQL*Net to client

       1277  bytes received via SQL*Net from client

          4  SQL*Net roundtrips to/from client

          1  sorts (memory)

          0  sorts (disk)

      85178  rows processed

（显示日志生成量9941336）

 

——现在将表t改为nologging模式：

SYS@ORA11GR2>alter table t nologging;

 

Table altered.

 

SYS@ORA11GR2>select table_name,logging from user_tables where table_name='T';

 

TABLE_NAME                     LOG

------------------------------ ---

T                              NO

 

——执行相同sql语句，但是加上/*+append*/

SYS@ORA11GR2>insert /*+append*/ into t select * from t;

 

170356 rows created.

 

 

Execution Plan

----------------------------------------------------------

ERROR:

ORA-12838: cannot read/modify an object after modifying it in parallel

 

 

SP2-0612: Error generating AUTOTRACE EXPLAIN report

 

Statistics

----------------------------------------------------------

         93  recursive calls

       2670  db block gets

       2576  consistent gets

          0  physical reads

      27144  redo size

       1117  bytes sent via SQL*Net to client

       1289  bytes received via SQL*Net from client

          4  SQL*Net roundtrips to/from client

          6  sorts (memory)

          0  sorts (disk)

     170356  rows processed

（显示相同的sql语句生成的redo日志信息显著下降27144）

SYS@ORA11GR2>set autot off

 

——再修改

SYS@ORA11GR2>alter table t logging;

 

Table altered.

 

SYS@ORA11GR2>set autot on

SYS@ORA11GR2>insert /*+append*/ into t select * from t;

 

340712 rows created.

 

 

Execution Plan

----------------------------------------------------------

ERROR:

ORA-12838: cannot read/modify an object after modifying it in parallel

 

 

SP2-0612: Error generating AUTOTRACE EXPLAIN report

 

Statistics

----------------------------------------------------------

        134  recursive calls

       5264  db block gets

       5038  consistent gets

       2430  physical reads

   40027964  redo size

       1118  bytes sent via SQL*Net to client

       1289  bytes received via SQL*Net from client

          4  SQL*Net roundtrips to/from client

          6  sorts (memory)

          0  sorts (disk)

     340712  rows processed

 

归档模式，logging模式及加/*+append*/还是生成了大量的redo日志；

 

测试结论：

1、无论归档模式、还是非归档模式，append+nologing插入方式效果最好，生成最小的redo日志

2、非归档模式下，append插入模式效果不错

3、归档模式下，append插入模式没有效果

普通插入方式：

insert into t select * from dba_objects;

append插入方式：

insert /*+append*/ into t select * from dba_objects;

Append+nologing插入方式：

insert /*+append*/ into t nologging select * from dba_objects;

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/31397003/viewspace-2126418/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/31397003/viewspace-2126418/