ITPub博客
首页 博客 专栏 学院 社区 IT新媒体评选
发博

首页 > 数据库 > Oracle > Direct Path Insert与APPEND, PARALLEL的梳理与小结

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

原创 Oracle 作者：hd_system 时间：2017-02-16 13:26:31 0 删除 编辑

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博客 ” ，链接：https://blog.itpub.net/29209863/viewspace-2133642/ ，如需转载，请注明出处，否则将追究法律责任。

0
0
上一篇： 【RAC】Oracle RAC环境的日志体系
下一篇： 如何正确删除ORACLE归档日志文件
请登录后发表评论 登录
全部评论
hd_system

注册时间：2016-10-12

  • 博文量
    149
  • 访问量
    99043

最新文章

支持我们 作者招募 用户协议 FAQ Contact Us

北京盛拓优讯信息技术有限公司. 版权所有  京ICP备16024965号-8 北京市公安局海淀分局网监中心备案编号：11010802021510 niuxiaotong@pcpop.com 17352615567 未成年人举报专区

广播电视节目制作经营许可证(京) 字第1234号 中国互联网协会会员

违法和不良信息举报电话:18101376593