Direct Path Insert与APPEND, PARALLEL的梳理与小结

Oracle里面的写入数据方式分为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/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/29209863/viewspace-2133642/