In this Document
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 SYMPTOMSIn 11.1 , using the /*+ APPEND */ hint to do direct path insert , TM Enqueue Contention occurs. CHANGESAfter having upgraded to 11.1.0.7 CAUSEChange in the behaviour of the APPEND hint (Direct Path Insert) SOLUTION
During direct-path INSERT, the database obtains exclusive locks on the table (or on all partitions of a partitioned table). As a result, users cannot perform any concurrent insert, update, or delete operations on the table, and concurrent index creation and build operations are not permitted. Concurrent queries, however, are supported, but the query will return only the information before the insert operation.
To summarize, there are now 2 different hints for direct-path load:
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 --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
--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 --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 --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 REFERENCESNOTE: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 |
|
Direct Path Insert - APPEND HINT and TM Enqueue LOCK Behaviour (文档 ID 1317447.1)
最新推荐文章于 2024-06-05 18:00:22 发布