Oracle Performance and Engineering

[Am finally back after a looong 5 day weekend :)
Snowboarding, movies, lots of sleep and catching up on the reading were on agenda. ]

Often, I saw APPEND being hinted in an INSERT statement to speed it up. Oracle manual says "In direct-path INSERT, data is appended to the end of the table, rather than using existing space currently allocated to the table. As a result, direct-path INSERT can be considerably faster than conventional INSERT."

Thus an APPEND would do much less work, generate much less Redo and expected to be faster. Since APPEND does not have to do recursive check to figure out where it shall put the new record(s) in, an APPEND call also generates lower number of recursive calls. True. But there are two issues here.

ONE.
For no redo log to be generated, the table must be in "NOLOGGING" mode or the database should be in "NOARCHIVE" mode.

Plus, if the table has indexes -- redo will be unavoidable.

TWO.
"Insert /*+ APPEND */" grabs a lock on the entire table in EXCLUSIVE mode. Thus any other DML from the application will be waiting. For longer transactions this could indeed be causing waiting locks.

A small test --

-- in SESSION 1

SQL> create table objtab as select * from user_objects where 1 = 2 ;
-- empty table

SQL> insert /*+ APPEND */ into objtab select * from user_objects;

SQL> select object_id from dba_objects where object_name like 'OBJTAB'

OBJECT_ID
------------
361912

SQL> select * from v$lock where sid = (select distinct(sid) from v$mystat);
OR,
SQL> select * from v$lock where sid = userenv('sid');

will show something like -

ADDR KADDR SID TYPE ID1 ID2 LMODE REQUEST CTIME BLOCK
A69B4CF4 A69B4E08 292 TX 196652 739623 6 0 163 0
A6921934 A692194C 292 TM 361912 0 6 0 163 0

Where --

LMODE = lock mode held: 1 (null), 2 (row share), 3 (row exclusive), 4 (share), 5 (share row exclusive), 6 (exclusive) 
Thus we see a direct-mode insert (APPEND) grabs "EXCLUSIVE" "TM" lock on the table it 
is inserting into. The other table with ID 196652 is LINK$ -- the base table on which
USER_OBJECTS view is written on.

Now, from another session if you try to do a DML (INSERT, UPDATE or DELETE) against
OBJTAB -- it will wait since the main session has not committed or rolled back the direct insert
against OBJTAB.

-- in SESSION 2
SQL> insert into objtab select * from user_objects;
...wait...
-- in SESSION 1
SQL> select SID,TYPE,ID1,ID2,LMODE,REQUEST,CTIME,BLOCK  from v$lock
          where id1 = 361912
SID TYPE ID1   ID2 LMODE REQUEST CTIME BLOCK
271 TM 361912 0 0  3  120  0
292 TM 361912 0 6  0  1458        1
So, as we see the original session (SID - 292) doing INSERT /* APPEND */ and thus holding an
EXCLUSIVE "TM" lock  is blocking the new session (271).

Note that session 2 (SID - 271) "requests" a "row-exclusive" lock and not an "exclusive" lock on
the whole table like a direct insert does.
If we repeat the experiment with SESSION 1 doing a conventional INSERT -- we will see INSERTS
are not blocking other INSERTS. i.e., locks are acquired in "row-exclusive" mode.
Thus, using direct-path insert could indeed be a case where insert blocks other inserts.
P.S. TM locks are issued during DML to make sure the table is not dropped or altered
(DDLed against). The total number of such locks can be controlled by setting the initialization
parameter DML_LOCKS. Thus to make a database read only, just set DML_LOCKS = 0 ;)
Well, it sometimes has to be done in a distributed environment (hint!).