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

To Bottom

In this Document

Goal

Solution

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