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，否则将无法进行直接路径写入。

测试如下：

[sql]  view plain copy

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生成的量，以及直接路径阻塞传统写入的场景

[sql]  view plain copy

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，

[sql]  view plain copy

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，查看锁的情况：

[sql]  view plain copy

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>  

[sql]  view plain copy

1. Sid为44的正在执行直接路径插入，当时还没有提交，而58号 会话在执行一个传统路径写入  

提交44号会话，58号 会话完成

2、测试append，parallel的默认插入方式

上面的测试已经表名，在表默认的并行度为1的情况下，直接append是会选择直接路径写入方式。下面现将表t_direct_test的并行度设置为4.

[sql]  view plain copy

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>