本文介绍了Oracle数据库中的两种数据写入方式：传统方式和直接路径写入。直接路径写入避免了缓冲区缓存，提高了效率，但在某些条件下可能不产生Undo或Redo日志。此外，文章还探讨了直接路径写入的限制，如不适用于集群表、对象类型字段等，并讨论了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> select log_mode, force_logging from v$database;
LOG_MODE FORCE_
------------------------ ------
ARCHIVELOG NO
SQL> select * from v$version;
BANNER
-------------------------------------------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE 11.2.0.3.0 Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production
===scott测试用户，构造测试数据源和数据表
SQL> conn scott/tiger;
Connected.
SQL> create table t_data_pool as select * from dba_objects;
Table created.
SQL> create table t_direct_test as select * from t_data_pool where 1=2;
Table created.
SQL>
1、测试REDO生成的量，以及直接路径阻塞传统写入的场景
==场景1，Redo的量
SQL> set autot on;
SQL> insert into t_direct_test select * from t_data_pool;
75696 rows created.
Execution Plan
----------------------------------------------------------
Plan hash value: 2724272089
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | 76299 | 15M| 216 (1)| 00:00:03 |
| 1 | LOAD TABLE CONVENTIONAL | T_DIRECT_TEST | | | | |
| 2 | TABLE ACCESS FULL | T_DATA_POOL | 76299 | 15M| 216 (1)| 00:00:03 |
------------------------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
266 recursive calls
10388 db block gets
3228 consistent gets
784 physical reads
8845208 redo size ==> redo 量
837 bytes sent via SQL*Net to client
807 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
75696 rows processed
SQL> commit;
Commit complete.
==使用append， logging时，redo量测试如下：
SQL> insert /*+append*/ into t_direct_test select * from t_data_pool;
75696 rows created.
Execution Plan
----------------------------------------------------------
ERROR:
ORA-12838: cannot read/modify an object after modifying it in parallel
SP2-0612: Error generating AUTOTRACE EXPLAIN report
Statistics
----------------------------------------------------------
94 recursive calls
1397 db block gets
1204 consistent gets
0 physical reads
8890936 redo size ==>redo 量
823 bytes sent via SQL*Net to client
819 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
75696 rows processed
SQL> commit;
Commit complete
==nologging append量，明显很少了
SQL> alter table t_direct_test nologging;
Table altered.
SQL> insert /*+append*/ into t_direct_test select * from t_data_pool;
75696 rows created.
Execution Plan
----------------------------------------------------------
ERROR:
ORA-12838: cannot read/modify an object after modifying it in parallel
SP2-0612: Error generating AUTOTRACE EXPLAIN report
Statistics
----------------------------------------------------------
139 recursive calls
1361 db block gets
1229 consistent gets
0 physical reads
20904 redo size ==>Redo 量
824 bytes sent via SQL*Net to client
819 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
7 sorts (memory)
0 sorts (disk)
75696 rows processed
SQL> select count(1) from t_direct_test;
select count(1) from t_direct_test
*
ERROR at line 1:
ORA-12838: cannot read/modify an object after modifying it in parallel
==此时不提交，当前事务是无法访问到该对象的，其他session可以。
新开会话B，
oracle@ora11gr2 ~]$sqlplus scott/tiger
SQL*Plus: Release 11.2.0.3.0 Production on Wed Jun 11 12:30:31 2014
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> select count(1) from t_direct_test;
COUNT(1)
----------
151392
SQL> insert into t_direct_test select * from t_data_pool; ==会被阻塞
新开会话C，查看锁的情况：
当前会话申请0号TM锁被直接路径写入会话阻塞，直接路径写入会话持有Exclusive(6) TM锁。
col resource format a20
col sid format 9999
col request for a15
Select Type || '-' || Id1 || '-' || Id2 "resource",
Sid,
Decode(Lmode,
0,
'None',
1,
'Null',
2,
'Row share',
3,
'Row Exclusive',
4,
'Share',
5,
'Share Row Exclusive',
6,
'Exlusive') Lock_Type,
Decode(Request,
0,
'None',
1,
'Null',
2,
'Row share',
3,
'Row Exclusive',
4,
'Share',
5,
'Share Row Exclusive',
6,
'Exlusive') Request,
Ctime,
Block
From V$lock
Where Type In ('TX', 'TM')
37 Order By "resource", Ctime Desc;
resource SID LOCK_TYPE REQUEST CTIME BLOCK
-------------------- ----- -------------------------------------- --------------- ---------- ----------
TM-5124-0 44 Row Exclusive None 905 0
TM-78585-0 29 Row Exclusive None 2912 0
TM-78587-0 44 Exlusive None 906 1 ====> TM上持有6号锁
TM-78587-0 58 None Row Exclusive 380 0 ====> TM上被阻塞
TX-262166-8704 44 Exlusive None 906 0
TX-458782-8781 29 Exlusive None 2912 0
6 rows selected.
SQL>
Sid为44的正在执行直接路径插入，当时还没有提交，而58号 会话在执行一个传统路径写入
2、测试append，parallel的默认插入方式
上面的测试已经表名，在表默认的并行度为1的情况下，直接append是会选择直接路径写入方式。下面现将表t_direct_test的并行度设置为4.
SQL> conn scott/tiger
Connected.
SQL> col owner for a10
SQL> col degree for 999
SQL> col table_name for a20
SQL> select owner, table_name, Degree from dba_tables Where table_name = Upper('t_direct_test');
OWNER TABLE_NAME DEGREE
---------- -------------------- --------------------------------------------------------------------------------
SCOTT T_DIRECT_TEST 1
SQL> alter table t_direct_test parallel 4;
Table altered.
SQL> select owner, table_name, Degree from dba_tables Where table_name = Upper('t_direct_test');
OWNER TABLE_NAME DEGREE
---------- -------------------- --------------------------------------------------------------------------------
SCOTT T_DIRECT_TEST 4
SQL> insert into t_direct_test select * from t_data_pool;
75696 rows created.
SQL> select count(1) from t_direct_test; ==>没有报错，即表明没有用到直接路径写入
COUNT(1)
----------
378480
SQL> commit;
Commit complete.
SQL> alter session enable parallel dml;
Session altered.
SQL> insert into t_direct_test select * from t_data_pool;
75696 rows created.
SQL> select count(1) from t_direct_test;
==报错，说明在会话开启允许并行dml的情况下，会直接使用直接路径写入方式。
select count(1) from t_direct_test
*
ERROR at line 1:
ORA-12838: cannot read/modify an object after modifying it in parallel
SQL>
扫一扫
945
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
