技术博客 数据库、运维与工程实践笔记

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

2024-04-12GoldenGate / Oracle / RAC

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 plaincopy!1756814622976-0e93167c-959e-4638-a791-6662f3b071ea.png ico_fork.svg

  1. SQL> select log_mode, force_logging from v$database;
  3. LOG_MODE FORCE_
  4. ———————— ——
  5. ARCHIVELOG NO
  7. SQL> select * from v$version;
  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
  17. ===scott测试用户，构造测试数据源和数据表
  18. SQL> conn scott/tiger;
  19. Connected.
  20. SQL> create table t_data_pool as select * from dba_objects;
  22. Table created.
  24. SQL> create table t_direct_test as select * from t_data_pool where 1=2;
  26. Table created.
  28. SQL>

1、测试REDO生成的量，以及直接路径阻塞传统写入的场景

[sql]view plaincopy!1756814622976-0e93167c-959e-4638-a791-6662f3b071ea.png ico_fork.svg

  1. ==场景1，Redo的量
  2. SQL> set autot on;
  3. SQL> insert into t_direct_test select * from t_data_pool;
  5. 75696 rows created.
  8. Execution Plan
  9. ———————————————————-
  10. Plan hash value: 2724272089
  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. ——————————————————————————————
  20. Note
  21. —–
  22. – dynamic sampling used for this statement (level=2)
  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
  39. SQL> commit;
  41. Commit complete.
  43. ==使用append， logging时，redo量测试如下：
  44. SQL> insert /*+append*/ into t_direct_test select * from t_data_pool;
  46. 75696 rows created.
  49. Execution Plan
  50. ———————————————————-
  51. ERROR:
  52. ORA-12838: cannot read/modify an object after modifying it in parallel
  55. SP2-0612: Error generating AUTOTRACE EXPLAIN report
  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
  71. SQL> commit;
  73. Commit complete
  76. ==nologging append量，明显很少了
  77. SQL> alter table t_direct_test nologging;
  79. Table altered.
  81. SQL> insert /*+append*/ into t_direct_test select * from t_data_pool;
  83. 75696 rows created.
  86. Execution Plan
  87. ———————————————————-
  88. ERROR:
  89. ORA-12838: cannot read/modify an object after modifying it in parallel
  92. SP2-0612: Error generating AUTOTRACE EXPLAIN report
  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
  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 plaincopy!1756814622976-0e93167c-959e-4638-a791-6662f3b071ea.png ico_fork.svg

  1. oracle@ora11gr2 ~]$sqlplus scott/tiger
  2. SQL*Plus: Release 11.2.0.3.0 Production on Wed Jun 11 12:30:31 2014
  4. Copyright (c) 1982, 2011, Oracle. All rights reserved.
  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
  11. SQL> select count(1) from t_direct_test;
  13. COUNT(1)
  14. ———-
  15. 151392
  17. SQL> insert into t_direct_test select * from t_data_pool; ==会被阻塞

新开会话C，查看锁的情况：

[sql]view plaincopy!1756814622976-0e93167c-959e-4638-a791-6662f3b071ea.png ico_fork.svg

  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;
  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
  52. 6 rows selected.
  54. SQL>

[sql]view plaincopy!1756814622976-0e93167c-959e-4638-a791-6662f3b071ea.png ico_fork.svg

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

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

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

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

[sql]view plaincopy!1756814622976-0e93167c-959e-4638-a791-6662f3b071ea.png ico_fork.svg

  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');
  8. OWNER TABLE_NAME DEGREE
  9. ———- ——————– ——————————————————————————–
  10. SCOTT T_DIRECT_TEST 1
  12. SQL> alter table t_direct_test parallel 4;
  14. Table altered.
  16. SQL> select owner, table_name, Degree from dba_tables Where table_name = Upper('t_direct_test');
  18. OWNER TABLE_NAME DEGREE
  19. ———- ——————– ——————————————————————————–
  20. SCOTT T_DIRECT_TEST 4
  22. SQL> insert into t_direct_test select * from t_data_pool;
  24. 75696 rows created.
  26. SQL> select count(1) from t_direct_test; ==>没有报错，即表明没有用到直接路径写入
  28. COUNT(1)
  29. ———-
  30. 378480
  32. SQL> commit;
  34. Commit complete.
  36. SQL> alter session enable parallel dml;
  38. Session altered.
  40. SQL> insert into t_direct_test select * from t_data_pool;
  42. 75696 rows created.
  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
  51. SQL>

.

.

我的同类文章

Oracle 体系结构_（7）_

_•_如何正确Move Table，利用PLSQL可以批量处理2014-05-23_阅读_975_•_Oracle 11g 新特性– API和预编译改进2014-05-19_阅读_865_•_【视图学习】user_tab_modifications 视图的作用2013-12-05_阅读_980_•_彻底理解move 操作2013-03-21_阅读_2187_•_ORACLE 11G 新特性–可用性增强2014-05-19_阅读_516_•_【脚本分享】表空间每天的使用情况2013-12-05_阅读_1062_•_剖析shrink 操作原理2013-03-21_阅读_2002