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
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