Oracle Database 12.2 -- Direct Path Reads Not Used from a Job

Problem

The same SQL statement (SQL_ID) with exactly the same execution plan (PLAN_HASH_VALUE), when executed from a client application (sqlplus, SQL Developer, etc) uses direct path read, while running from a dbms_scheduler job uses conventional read (db file scattered read wait event)

Solution

Having enabled the ‘direct path read’ trace for the job session, it is seen that direct path read is disabled for recursive sql:

NSMTIO: kcbism: islarge 1 next 0 nblks 267424906 type 2, bpid 3, kcbisdbfc 0 kcbnhl 8388608 kcbstt 1549287 keep_nb 6277500kcbnbh 77401575 kcbnwp 4
NSMTIO: kcbimd: nblks 267424906 kcbstt 1549287 kcbpnb 7746435 kcbisdbfc 3 is_medium 0
NSMTIO: qertbFetch:NoDirectRead:[]:Obect's size: 267424906 (blocks), Threshold: STT(1549287 blocks),
_object_statistics: enabled, Sage: enabled,
Direct Read for serial qry: disabled(::recursive_call:pgapls::::), Ascending SCN table scan: FALSE
flashback_table_scan: FALSE, Row Versions Query: FALSE
SqlId: 4zz9f1uqudahb, plan_hash_value: 3230269578, Object#: 4226634, Parition#: 75 DW_scan: disabled

The only way to force direct path read from a recursive sql (dbms_scheduler, dbms_refresh etc):

1) Enable parallelism at table level by ALTER TABLE ... PARALLEL <N>

or

2) Use /*+ parallel(<table> <N>) */ hint to enable parallelism at statement level

Reference:

• Oracle Database 12c -- Enable Trace for Direct Path Read