Direct Path Insert - APPEND HINT and TM Enqueue LOCK Behaviour (文档 ID 1317447.1)
文档 1317447.1
In this Document
Symptoms |
Changes |
Cause |
Solution |
References |
Applies to:
Oracle Database - Enterprise Edition - Version 9.2.0.1 to 11.1.0.8 [Release 9.2 to 11.1]Oracle Database - Personal Edition - Version 9.2.0.1 to 11.1.0.8 [Release 9.2 to 11.1]
Oracle Database - Standard Edition - Version 9.2.0.1 to 11.1.0.8 [Release 9.2 to 11.1]
Information in this document applies to any platform.
Issue is seen in 11.1 not on other Versions
Symptoms
Changes
After having upgraded to 11.1.0.7
Cause
Change in the behaviour of the APPEND hint (Direct Path Insert)
Solution
- In Oracle 10g and previous releases the APPEND hint is ignored when specifying INSERT..VALUES. Thus even though stated, the insert will be done in conventional mode and not use direct-path insert into the buffer cache. When using INSERT..SELECT the hint will be honoured
- On 11g Release 1, the APPEND hint will be honoured for an INSERT with VALUES and the row will be inserted directly into datafiles, bypassing the buffer cache.
- In 11g release 2, a new hint has been added, /*+ APPEND_VALUES */ that will load the data directly into the datafiles.
To summarize, there are now 2 different hints for direct-path load:
- APPEND: Instructs Oracle to use direct-path INSERT with the subquery syntax of the INSERT statement.
- APPEND_VALUES: Instructs Oracle to use direct-path INSERT with the VALUES clause.
The following examples clarify the behaviour explained above:
INSERT .. VALUES and APPEND HINT
Version: 10.2.0.4
--SID=159: SQL> create table test_insert (id number, name varchar2(25)); SQL> insert /*+ APPEND */ into test_insert values (1,'Test1'); 1 row created. select sid,type,id1,id2,lmode from v$lock where sid=159; SQL> SID TY ID1 ID2 LMODE ---------- -- ---------- ---------- ---------- 159 TM 61245 0 3 159 TX 262153 3571 6
APPEND hint ignored and lock mode=3 Row Exclusive
Version: 11.1.0.7
--SID=131: SQL> create table test_insert (id number, name varchar2(25)); SQL> insert /*+ APPEND */ into test_insert values (1,'Test1'); 1 row created. select sid,type,id1,id2,lmode from v$lock where sid=131; SQL> SID TY ID1 ID2 LMODE ---------- -- ---------- ---------- ---------- 131 TM 84239 0 6 131 TX 65548 4981 6
APPEND hint honoured and lock mode=6 Exclusive
Version: 11.2.0.3
--SID=59 SQL> create table test_insert (id number, name varchar2(25)); SQL> insert /*+ APPEND */ into test_insert values (1,'Test1'); 1 row created. select sid,type,id1,id2,lmode from v$lock where sid=59; SID TY ID1 ID2 LMODE ---------- -- ---------- ---------- ---------- 69 TM 96843 0 3 69 TX 196640 3301 6
APPEND hint ignored and lock mode=3 Row Exclusive
INSERT .. VALUES and APPEND_VALUES HINT
Version: 11.2.0.3
--SID=134 SQL> create table test_insert (id number, name varchar2(25)); SQL> insert /*+ APPEND_VALUES */ into test_insert values (1,'Test1'); 1 row created. SQL> select sid,type,id1,id2,lmode from v$lock where sid=134; SID TY ID1 ID2 LMODE ---------- -- ---------- ---------- ---------- 134 TM 96843 0 6 134 TX 65565 7849 6
New APPEND_VALUES hint honoured and lock mode=6 Exclusive
INSERT .. SELECT and APPEND HINT
All Versions
--SID=77 SQL> insert /*+ APPEND */ into test_insert select object_id,object_name from user_objects; 59 rows created. SQL> select sid,type,id1,id2,lmode from v$lock where sid=77; SID TY ID1 ID2 LMODE ---------- -- ---------- ---------- ---------- 77 TM 96843 0 6 77 TX 589827 8029 6
APPEND hint honoured and lock mode=6 Exclusive
References
NOTE:1019527.6 - Script to Check for Foreign Key Locking Issues for a Specific UserNOTE:842374.1 - APPEND Hint (Direct-Path) Insert with Values Causes Excessive Space Usage on 11G