【最佳化】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;