HOW TO IDENTIFY IF DIRECT PATH LOAD IS USED ORACLE

Consider, there is a critical interview to attend for position of CEO. Consider travelling in Car(Conventional load) or Aero plane.(Direct path load)

If car is used for travel, then high traffic and road congestion can increase the time taken to reach the place. Since road path is used, any time the passenger can get out of the car for a short drink.

If aero plane is preferred, the destination place can be reached faster bypassing the traffic but airport is too far from the destination. Also once the plane is board, no one can get out of plane during the fly.

Similarly both conventional path and direct path has their pros and cons.

Conventional path load is default method used to load data. This method is slower due to SQL processing in SGA.

Direct path load write the blocks above the high water mark bypassing the SGA but uses PGA and temp tablespace to complete data load. This kind of load is faster.

Use hints to force a direct path load during insert and when the insert complete, don’t commit the transaction. Execute a select query or another DML on the same table to confirm if direct path load is used.

Note: Either parallel or append hint force direct path

DIRECT PATH LOAD
kIsH@x3z6zx9<^>insert /*+ append parallel(6) */ into tbl select * from dba_source;

729290 rows created.

Elapsed: 00:00:27.89
kIsH@x3z6zx9<^>select * from tbl where rownum < 5;
select * from tbl where rownum < 5
              *
ERROR at line 1:
ORA-12838: cannot read/modify an object after modifying it in parallel


Elapsed: 00:00:00.00
kIsH@x3z6zx9<^>rollback;

Rollback complete.

Elapsed: 00:00:00.00
kIsH@x3z6zx9<^>insert /*+ append */ into tbl select * from dba_source;

729290 rows created.

Elapsed: 00:00:31.61
kIsH@x3z6zx9<^>select * from tbl where rownum < 5;
select * from tbl where rownum < 5
              *
ERROR at line 1:
ORA-12838: cannot read/modify an object after modifying it in parallel


Elapsed: 00:00:00.00

In traces, direct path write(PGA) or direct path write temp(TEMP tablespace) events can be visible as wait events

WAIT #47109896825792: nam='direct path write' ela= 5866 file number=4 first dba=801920 block cnt=32 obj#=-1 tim=1650041709216543

From SQLPLUS, the wait events appear under v$session_wait

kIsH@x3z6zx9<^>select event,wait_class from v$session_wait where sid=152;

EVENT                                                            WAIT_CLASS
---------------------------------------------------------------- ----------------------------------------------------------------
direct path write                                                User I/O

Elapsed: 00:00:00.00

Another way to identify in a indirect manner is DML locks(TM) and TX(transaction) of table during direct path load.

kIsH@x3z6zx9<^>select * from v$lock where sid=152 and ty <> 'AE';

ADDR             KADDR                   SID TY        ID1        ID2      LMODE    REQUEST      CTIME      BLOCK
---------------- ---------------- ---------- -- ---------- ---------- ---------- ---------- ---------- ----------
00002B7D86123A78 00002B7D86123AD8        152 TM      90057          0          6          0         23          0
00000000E9EC0DF0 00000000E9EC0E68        152 TX      65557      52622          6          0         23          0

Leave a Reply