關于insert /*+ append */我們需要注意以下三點： a、非歸檔模式下，只需append就能大量減少redo的產生；歸檔模式下，只有append+nologging才能大量減少redo。 b、insert /*+ append */時會對表加鎖（排它鎖），會阻塞表上的除了select以外所有DML語句；傳統
關于insert /*+ append */我們需要注意以下三點：
a、非歸檔模式下，只需append就能大量減少redo的產生；歸檔模式下，只有append+nologging才能大量減少redo。
b、insert /*+ append */?時會對表加鎖（排它鎖），會阻塞表上的除了select以外所有DML語句；傳統的DML在TM enqueue上使用模式3（row exclusive），其允許其他DML在相同的模式上獲得TM enqueue。但是直接路徑加載在TM enqueue使用模式6（exclusive），這使其他DML在直接路徑加載期間將被阻塞。
c、insert /*+ append */?直接路徑加載，速度比常規加載方式快。因為是從HWM的位置開始插入，也許會造成空間浪費。
1.環境介紹
數據庫版本：
| 1 2 3 4 5 6 7 8 |
SQL>?select?*?from?v$version;
BANNER
---------------------------------------------------------------------
Oracle?Database?11g Enterprise Edition Release 11.2.0.1.0 - Production
PL/SQL Release 11.2.0.1.0 - Production
CORE??? 11.2.0.1.0????? Production
TNS?for?Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production
|
網上說測試時不要使用auto trace來查看redo size，這個值是有偏差的.建議建立一個視圖：
| 1 2 3 4 5 6 |
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';
-- 視圖已創建。
|
2.示例演示：
2.1 非歸檔模式
| 1 2 3 4 5 6 |
SQL> archive log list
數據庫日志模式???????????? 非存檔模式
自動存檔???????????? 禁用
存檔終點??????????? USE_DB_RECOVERY_FILE_DEST
最早的聯機日志序列???? 95
當前日志序列?????????? 97
|
2.1.1 nologging表
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 |
SQL>?create?table?test1 nologging?as?select?*?from?dba_objects?where?1=0;
表已創建。
??
SQL>?select?*?from?redo_size;
?????VALUE
----------
??25714940
??
SQL>?insert?into?test1?select?*?from?dba_objects;
已創建72753行。
??
SQL>?select?*?from?redo_size;
?????VALUE
----------
??34216916
??
SQL>?insert?/*+ APPEND */??into?test1?select?*?from?dba_objects;
已創建72753行。
??
SQL>?select?*?from?redo_size;
?????VALUE
----------
??34231736
??
SQL>?select?(34231736-34216916) redo_append , (34216916-25714940) redo_normalfrom?dual;
REDO_APPEND REDO_NORMAL
----------- -----------
??????14820???? 8501976
|
2.1.2 logging表：
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 |
SQL>?create?table?test2?as?select?*?from?dba_objects?where?1=0;
??
表已創建。
??
SQL>?select?*?from?redo_size;
?????VALUE
----------
??34273348
??
SQL>?insert?into?test2?select?*?from?dba_objects;
??
已創建72754行。
??
SQL>?select?*?from?redo_size;
?????VALUE
----------
??42775336
??
SQL>?insert?/*+ APPEND */??into?test2?select?*?from?dba_objects;
已創建72754行。
??
SQL>?select?*?from?redo_size;
?????VALUE
----------
??42790156
??
SQL>?select?(42790156-42775336) redo_append , (42775336-34273348) redo_normalfrom?dual;
REDO_APPEND REDO_NORMAL
----------- -----------
??????14820???? 8501988
|
2.2歸檔模式下：
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 |
SQL> shutdown immediate
數據庫已經關閉。
已經卸載數據庫。
ORACLE例程已經關閉。
SQL> startup mount
ORACLE例程已經啟動。
??
Total System?Global?Area? 477073408 bytes
Fixed?Size??????????????????1337324 bytes
Variable?Size?????????????293603348 bytes
Database?Buffers????????? 176160768 bytes
Redo Buffers??????????????? 5971968 bytes
數據庫裝載完畢。
SQL>?alter?database?archivelog;
數據庫已更改。
??
SQL>?alter?database?open;
數據庫已更改。
??
SQL> archive log list
數據庫日志模式??????????? 存檔模式
自動存檔???????????? 啟用
存檔終點??????????? USE_DB_RECOVERY_FILE_DEST
最早的聯機日志序列???? 95
下一個存檔日志序列?? 97
當前日志序列?????????? 97
|
2.2.1 nologging表
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 |
SQL>?select?*?from?redo_size;
????VALUE
----------
?????17936
??
SQL>?insert?into?test1?select?*?from?dba_objects;
已創建72754行。
??
SQL>?select?*?from?redo_size;
?????VALUE
----------
???8490972
??
SQL>?insert?/*+ APPEND */??into?test1?select?*?from?dba_objects;
已創建72754行。
??
SQL>?select?*?from?redo_size;
?????VALUE
----------
???8506164
??
SQL>?select?(8506164-8490972) redo_append , (8490972-17936) redo_normal?fromdual;
REDO_APPEND REDO_NORMAL
----------- -----------
??????15192???? 8473036
|
2.2.2 logging表
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 |
SQL>?select?*?from?redo_size;
?????VALUE
----------
???8506780
??
SQL>?insert?into?test2?select?*?from?dba_objects;
已創建72754行。
??
SQL>?select?*?from?redo_size;
?????VALUE
----------
??16979516
??
SQL>?insert?/*+ APPEND */??into?test2?select?*?from?dba_objects;
已創建72754行。
??
SQL>?select?*?from?redo_size;
?????VALUE
----------
??25518172
??
SQL>?select?(25518172-16979516) redo_append , (16979516-8506780) redo_normalfrom?dual;
REDO_APPEND REDO_NORMAL
----------- -----------
????8538656???? 8472736
|
在歸檔模式下,對于常規表的insert append產生和insert同樣的redo
此時的insert append實際上并不會有性能提高.
但是此時的append是生效了的。
3.insert /*+ append */會阻塞除select以外的DML語句，direct-path insert操作是單獨一個事務。
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 |
SQL>?select?count(*)?from?test2;
??COUNT(*)
----------
????291016
??
SQL>?insert?into?test2?select?*?from?dba_objects;
已創建72754行。
??
SQL>?select?count(*)?from?test2;
??COUNT(*)
----------
????363770
??
SQL>?insert?/*+ APPEND */?into?test2?select?*?from?dba_objects;
已創建72754行
??
同一個session下：
??
SQL>?select?count(*)?from?test2;
select?count(*)?from?test2
*
第 1 行出現錯誤:
ORA-12838: 無法在并行模式下修改之后讀/修改對象
??
SQL>?commit;
提交完成。
??
SQL>?select?count(*)?from?test2;
??COUNT(*)
----------
????436524
??
SQL>?insert?/*+ APPEND */?into?test2?select?*?from?dba_objects;
已創建72754行。
??
SQL> shutdown immediate
ORA-01097: 無法在事務處理過程中關閉 - 請首先提交或回退
??
SQL>?select??*?from?v$mystat?where?rownum<2;
??
???????SID STATISTIC#????? VALUE
??
---------- ---------- ----------
??
???????224????????? 0????????? 1
??
SQL>?select?KADDR,TYPE,LMODE?from?v$lock?where?sid=224;
??
KADDR??????????? TY????? LMODE
---------------- -- ----------
0000000071BAE180 TM????????? 6
0000000070CB11B8 TX????????? 6
|
另外開啟一個會話，就會發現只能select，其他DML全部阻塞。
?
APP 