How To Force Direct Path Read for SQL Statements (Doc ID 2426051.1)

APPLIES TO:

Oracle Database Cloud Schema Service - Version N/A and later
Oracle Database Exadata Cloud Machine - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Oracle Database Cloud Exadata Service - Version N/A and later
Oracle Database Backup Service - Version N/A and later
Information in this document applies to any platform.

GOAL

There are ways to disable direct path read for SQL statements as follows:

1. event 10949 level 1
2. _serial_direct_read = NEVER
However, there are no direct methods to force the direct path read operations which are faster for some SQL statements.

Sometimes, the same SQL that used to run in direct path read suddenly changed to conventional cache reads causing slow performance.
Goal of this document is to provide a method to force direct path read for such SQL statements.

SOLUTION

There are 2 methods to force direct path reads for SQL statements.

1. Use PARALLEL hint to the SQL statements like, /*+ parallel(4) */ so that parallelism uses direct path read.
2. Setting the statistics of the tables involved in the SQL such that the no.of blocks of tables > _small_table_threshold to enable the serial direct path read.

(i) Check the value of _small_table_threshold parameter in your DB.
SQL> select nam.ksppinm NAME,val.KSPPSTVL VALUE from x$ksppi nam, x$ksppsv val where nam.indx = val.indx and nam.ksppinm = '_small_table_threshold';

(ii) Check the no.of blocks statistics for the table.
SQL> SELECT blocks FROM user_tables WHERE table_name = 'TABLE_NAME';
Example:
If the blocks from user_tables for the object show 100 and _small_table_threshold is set to 480 then set the blocks statistics manually to 1000 so that it would go for direct path read.

(iii) Set the no.of blocks statistics for the tables involved in the SQL manually greater than the "_small_table_threshold" value.
SQL> EXEC DBMS_STATS.SET_TABLE_STATS('username','tabname',numblks=>n);
Example:
SQL> EXEC DBMS_STATS.SET_TABLE_STATS(user,'TEST',numblks=>1000);

There is a parameter _direct_read_decision_statistics_driven that controls this:

NAME                                               VALUE    DESCRIPTION
-------------------------------------------------- -------- ----------------------------------------------------------
_direct_read_decision_statistics_driven            TRUE     enable direct read decision based on optimizer statistics

When the above parameter is FALSE, the direct path read decision is done based on the actual block count of segment header.
When the above parameter is TRUE (default from 11.2.0.2), the direct path read decision is done based on the optimizer statistics.

NOTE: Though the block counts are taken from the optimizer statistics, it is not the optimizer alone that does the direct path read decision, as there are other factors like buffer cache size, cached block counts, PX execution etc affecting the direct path reads.