Direct Path Insert與APPEND, PARALLEL的梳理與小結

hd_system發表於2017-02-16
APPParallel

裡面的寫入資料方式分為2種，一個是傳統方式寫入(conventional insert)，另外一個是直接路徑寫入(direct path insert)。

            傳統方式寫入: Oracle會重用表裡面空閒空間，並且寫入會先寫入到buffer cache。

            直接路徑寫入：Oracle不重用表裡面的空閒空間，直接寫入到新分配的塊，並且資料直接寫入到data file，不寫入到buffer cache。效率更高。如果表的並行度不為1，那麼直接路徑寫入是預設的寫入行為（需要開啟會話並行dml）。直接路徑寫入為後設資料（例如空間擴充套件引起的資料字典資料變化）變更產生REDO, UNDO。而資料變更，redo，undo生成情況如下：

@  不為資料寫入生成undo

@  如果為非歸檔或者沒有開啟force logging，那麼直接路徑寫入時，不為資料寫入產生redo，這時與表是否設定nologging屬性無關。

@  如果資料庫為歸檔，但是沒有開啟force logging，logging的表會產生redo，nologging的表不會產生redo。

@  如果資料庫為歸檔並且開啟了force logging，資料寫入會產生redo，無論是否設定logging，nologging。


            直接路徑寫入的一些限制：

@ 在一個事務裡面，可以執行多次直接路徑寫入，一旦該事物並行修改了表，分割槽或者索引，當前會話將無法再次方位該物件。例如會話A在表T上進行直接路徑寫入後，會話A無法再次訪問表T，但是會話B可以查詢表T，此時如果會話A執行一個傳統路徑寫入，會被阻塞，等待事件為：enq: TM – contention。

@  目標表不能是cluster，不能含有object型別欄位，不能含有外來鍵約束，觸發器。

@ 對於目標表是IOT表，也有一些限制。

@  分散式寫入無法進行直接路徑寫入。


APPEND,PARALLE和直接路徑寫入關係：

                Append預設也是採用直接路徑寫入，不要求session enable parallel。

                Parallel 要求會話級別enable parallel，否則將無法進行直接路徑寫入。

如下：

點選(此處)摺疊或開啟

  1. SQL> select log_mode, force_logging from v$database;
  2.       
  3.     LOG_MODE FORCE_
  4.     ------------------------ ------
  5.     ARCHIVELOG NO
  6.       
  7.     SQL> select * from v$version;
  8.       
  9.     BANNER
  10.     -------------------------------------------------------------------------------------------------------------------
  11.     Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
  12.     PL/SQL Release 11.2.0.3.0 - Production
  13.     CORE 11.2.0.3.0 Production
  14.     TNS for Linux: Version 11.2.0.3.0 - Production
  15.     NLSRTL Version 11.2.0.3.0 - Production
  16.       
  17.     ===scott測試使用者，構造測試資料來源和資料表
  18.     SQL> conn scott/tiger;
  19.     Connected.
  20.     SQL> create table t_data_pool as select * from dba_objects;
  21.       
  22.     Table created.
  23.       
  24.     SQL> create table t_direct_test as select * from t_data_pool where 1=2;
  25.       
  26.     Table created.
  27.       
  28.     SQL>
1、測試REDO生成的量，以及直接路徑阻塞傳統寫入的場景

點選(此處)摺疊或開啟

  1. ==場景1，Redo的量
  2.     SQL> set autot on;
  3.     SQL> insert into t_direct_test select * from t_data_pool;
  4.       
  5.     75696 rows created.
  6.       
  7.       
  8.     Execution Plan
  9.     ----------------------------------------------------------
  10.     Plan hash value: 2724272089
  11.       
  12.     ------------------------------------------------------------------------------------------
  13.     | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
  14.     ------------------------------------------------------------------------------------------
  15.     | 0 | INSERT STATEMENT | | 76299 | 15M| 216 (1)| 00:00:03 |
  16.     | 1 | LOAD TABLE CONVENTIONAL | T_DIRECT_TEST | | | | |
  17.     | 2 | TABLE ACCESS FULL | T_DATA_POOL | 76299 | 15M| 216 (1)| 00:00:03 |
  18.     ------------------------------------------------------------------------------------------
  19.       
  20.     Note
  21.     -----
  22.        - dynamic sampling used for this statement (level=2)
  23.       
  24.       
  25.     Statistics
  26.     ----------------------------------------------------------
  27.         266 recursive calls
  28.           10388 db block gets
  29.            3228 consistent gets
  30.         784 physical reads
  31.         8845208 redo size ==> redo 量
  32.         837 bytes sent via SQL*Net to client
  33.         807 bytes received via SQL*Net from client
  34.           3 SQL*Net roundtrips to/from client
  35.           2 sorts (memory)
  36.           0 sorts (disk)
  37.           75696 rows processed
  38.       
  39.     SQL> commit;
  40.       
  41.     Commit complete.
  42.       
  43.     ==使用append， logging時，redo量測試如下：
  44.     SQL> insert /*+append*/ into t_direct_test select * from t_data_pool;
  45.       
  46.     75696 rows created.
  47.       
  48.       
  49.     Execution Plan
  50.     ----------------------------------------------------------
  51.     ERROR:
  52.     ORA-12838: cannot read/modify an object after modifying it in parallel
  53.       
  54.       
  55.     SP2-0612: Error generating AUTOTRACE EXPLAIN report
  56.       
  57.     Statistics
  58.     ----------------------------------------------------------
  59.          94 recursive calls
  60.            1397 db block gets
  61.            1204 consistent gets
  62.           0 physical reads
  63.         8890936 redo size ==>redo 量
  64.         823 bytes sent via SQL*Net to client
  65.         819 bytes received via SQL*Net from client
  66.           3 SQL*Net roundtrips to/from client
  67.           1 sorts (memory)
  68.           0 sorts (disk)
  69.           75696 rows processed
  70.       
  71.     SQL> commit;
  72.       
  73.     Commit complete
  74.       
  75.       
  76.     ==nologging append量，明顯很少了
  77.     SQL> alter table t_direct_test nologging;
  78.       
  79.     Table altered.
  80.       
  81.     SQL> insert /*+append*/ into t_direct_test select * from t_data_pool;
  82.       
  83.     75696 rows created.
  84.       
  85.       
  86.     Execution Plan
  87.     ----------------------------------------------------------
  88.     ERROR:
  89.     ORA-12838: cannot read/modify an object after modifying it in parallel
  90.       
  91.       
  92.     SP2-0612: Error generating AUTOTRACE EXPLAIN report
  93.       
  94.     Statistics
  95.     ----------------------------------------------------------
  96.         139 recursive calls
  97.            1361 db block gets
  98.            1229 consistent gets
  99.           0 physical reads
  100.          20904 redo size ==>Redo 量
  101.         824 bytes sent via SQL*Net to client
  102.         819 bytes received via SQL*Net from client
  103.           3 SQL*Net roundtrips to/from client
  104.           7 sorts (memory)
  105.           0 sorts (disk)
  106.           75696 rows processed
  107.       
  108.     SQL> select count(1) from t_direct_test;
  109.     select count(1) from t_direct_test
  110.                          *
  111.     ERROR at line 1:
  112.     ORA-12838: cannot read/modify an object after modifying it in parallel
  113.     ==此時不提交，當前事務是無法訪問到該物件的，其他session可以。

新開會話B，

點選(此處)摺疊或開啟

  1. oracle@ora11gr2 ~]$sqlplus scott/tiger
  2.     SQL*Plus: Release 11.2.0.3.0 Production on Wed Jun 11 12:30:31 2014
  3.       
  4.     Copyright (c) 1982, 2011, Oracle. All rights reserved.
  5.       
  6.       
  7.     Connected to:
  8.     Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
  9.     With the Partitioning, OLAP, Data Mining and Real Application Testing options
  10.       
  11.     SQL> select count(1) from t_direct_test;
  12.       
  13.       COUNT(1)
  14.     ----------
  15.         151392
  16.       
  17.     SQL> insert into t_direct_test select * from t_data_pool; ==會被阻塞
新開會話C，檢視鎖的情況：

點選(此處)摺疊或開啟

  1. 當前會話申請0號TM鎖被直接路徑寫入會話阻塞，直接路徑寫入會話持有Exclusive(6) TM鎖。
  2.     col resource format a20
  3.     col sid format 9999
  4.     col request for a15
  5.     Select Type || '-' || Id1 || '-' || Id2 "resource",
  6.            Sid,
  7.            Decode(Lmode,
  8.                   0,
  9.                   'None',
  10.                   1,
  11.                   'Null',
  12.                   2,
  13.                   'Row share',
  14.                   3,
  15.                   'Row Exclusive',
  16.                   4,
  17.                   'Share',
  18.                   5,
  19.                   'Share Row Exclusive',
  20.                   6,
  21.                   'Exlusive') Lock_Type,
  22.            Decode(Request,
  23.                   0,
  24.                   'None',
  25.                   1,
  26.                   'Null',
  27.                   2,
  28.                   'Row share',
  29.                   3,
  30.                   'Row Exclusive',
  31.                   4,
  32.                   'Share',
  33.                   5,
  34.                   'Share Row Exclusive',
  35.                   6,
  36.                   'Exlusive') Request,
  37.            Ctime,
  38.            Block
  39.       From V$lock
  40.      Where Type In ('TX', 'TM')
  41.      37 Order By "resource", Ctime Desc;
  42.       
  43.     resource SID LOCK_TYPE REQUEST CTIME BLOCK
  44.     -------------------- ----- -------------------------------------- --------------- ---------- ----------
  45.     TM-5124-0 44 Row Exclusive None 905 0
  46.     TM-78585-0 29 Row Exclusive None 2912 0
  47.     TM-78587-0 44 Exlusive None 906 1 ====> TM上持有6號鎖
  48.     TM-78587-0 58 None Row Exclusive 380 0 ====> TM上被阻塞
  49.     TX-262166-8704 44 Exlusive None 906 0
  50.     TX-458782-8781 29 Exlusive None 2912 0
  51.       
  52.     6 rows selected.
  53.       
  54.     SQL>

點選(此處)摺疊或開啟

  1. Sid為44的正在執行直接路徑插入，當時還沒有提交，而58號 會話在執行一個傳統路徑寫入
提交44號會話，58號 會話完成


2、測試append，parallel的預設插入方式

上面的測試已經表名，在表預設的並行度為1的情況下，直接append是會選擇直接路徑寫入方式。下面現將表t_direct_test的並行度設定為4.

點選(此處)摺疊或開啟

  1. SQL> conn scott/tiger
  2.     Connected.
  3.     SQL> col owner for a10
  4.     SQL> col degree for 999
  5.     SQL> col table_name for a20
  6.     SQL> select owner, table_name, Degree from dba_tables Where table_name = Upper('t_direct_test');
  7.       
  8.     OWNER TABLE_NAME DEGREE
  9.     ---------- -------------------- --------------------------------------------------------------------------------
  10.     SCOTT T_DIRECT_TEST 1
  11.       
  12.     SQL> alter table t_direct_test parallel 4;
  13.       
  14.     Table altered.
  15.       
  16.     SQL> select owner, table_name, Degree from dba_tables Where table_name = Upper('t_direct_test');
  17.       
  18.     OWNER TABLE_NAME DEGREE
  19.     ---------- -------------------- --------------------------------------------------------------------------------
  20.     SCOTT T_DIRECT_TEST 4
  21.       
  22.     SQL> insert into t_direct_test select * from t_data_pool;
  23.       
  24.     75696 rows created.
  25.       
  26.     SQL> select count(1) from t_direct_test; ==>沒有報錯，即表明沒有用到直接路徑寫入
  27.       
  28.       COUNT(1)
  29.     ----------
  30.     378480
  31.       
  32.     SQL> commit;
  33.       
  34.     Commit complete.
  35.       
  36.     SQL> alter session enable parallel dml;
  37.       
  38.     Session altered.
  39.       
  40.     SQL> insert into t_direct_test select * from t_data_pool;
  41.       
  42.     75696 rows created.
  43.       
  44.     SQL> select count(1) from t_direct_test;
  45.     ==報錯，說明在會話開啟允許並行dml的情況下，會直接使用直接路徑寫入方式。
  46.     select count(1) from t_direct_test
  47.     *
  48.     ERROR at line 1:
  49.     ORA-12838: cannot read/modify an object after modifying it in parallel
  50.       
  51.     SQL>


來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29209863/viewspace-2133642/，如需轉載，請註明出處，否則將追究法律責任。

相關文章