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

Problem

You need to enable trace for direct path read. The trace helps to see how decisions are made to use or not to use direct path reads

Solution

To enable direct path read trace:

SQL> alter session set events 'trace[nsmtio] disk medium';

Or

SQL> alter session set events 'trace [NSMTIO] disk=high';

To disable:

SQL> alter session set events 'trace[nsmtio] off'; 

Reference:

• "Direct Path Read" Not Happen Where It Is Expected (Doc ID 2981802.1)
• SRDC - Exadata: Smart Scan Not Working Issues (Doc ID 2310422.1)