Oracle direct path read相關隱含引數

Oracle 10G，預設時，即使對錶全表掃描也會先快取到buffer cache中，parallel方式除外

In 11g or higher, there has a been a change in the rules that choose between using 'direct path reads' and reads through the buffer cache for serial (i.e. non-parallel) table scans. This decision is based on the size of the table, buffer cache size, and various other statistics. Since Direct path reads are faster than scattered reads and have less impact on other processes because they avoid latches, it is likely that they will be chosen for such reads in 11g and above.

Oracle 11G及之後版本，發生了變化，全表掃描可透過直接路徑讀（Direct Path Read）繞開buffer cache方式來執行，是否Direct Path Read取決於table大小，buffer cache大小，其它統計資訊。

由於Direct Path Read比scattered reads快，並且由於避免latch而對其他程式的影響較小，因此11G之後很可能使用Direct Path Read

Oracle direct path read相關引數

_serial_direct_read 

是否啟用11G direct path read限制，其預設值為AUTO啟用，設定為NEVER時禁用自動direct path read的特性。該引數可以動態在例項或會話級別修改，而無需重啟例項。

select nam.ksppinm NAME,val.KSPPSTVL VALUE from x$ksppi nam, x$ksppsv val 
where nam.indx = val.indx and nam.ksppinm = '_serial_direct_read';
alter system set "_serial_direct_read"=auto;
alter system set "_serial_direct_read"=never;

_small_table_threshold 

預設值為buffer cache的2%, 單位:塊

就是說 table的blocks數大於_small_table_threshold這個值Oracle就認為是大表，就會走 direct path read

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

_direct_read_decision_statistics_driven

11.2.0.2之後出現，預設值為TRUE

select nam.ksppinm NAME,val.KSPPSTVL VALUE from x$ksppi nam, x$ksppsv val 
where nam.indx = val.indx and nam.ksppinm = '_direct_read_decision_statistics_driven';

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.

TRUE: 代表走direct path read的判定，基於table的統計資訊。

比如：

(1) 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.

(2) 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);

統計資訊查詢TABLE有100個block小於_small_table_threshold 480話，就不會走direct path read。如果統計資訊block設定超過480話就會走direct path read

FLASE: 代表走direct path read的判定，基於table segment header實際的block數

10949 事件

透過設定10949事件遮蔽direct path read特性，返回到Oracle 10G及之前的模式：
alter session set events '10949 trace name context forever, level 1';

還有一個引數 _very_large_object_threshold 用於設定（MB單位）使用DPR( direct path read)方式的上限，這個引數需要結合10949事件共同發揮作用。
10949 事件設定任何一個級別都將禁用DPR的方式，但是僅限於小於 5 倍 BUFFER Cache的資料表，同時，如果一個表的大小大於 0.8 倍的 _very_large_object_threshold  設定，也會執行DPR。

這些限定的目標在於：
對於大表的全表掃描，必須透過Direct Path Read方式執行，以減少對於Buffer Cache的衝擊和效能影響。
但是我們可以透過引數調整來決定執行DPR的上限和下限。

Event 10949 可以線上設定，但對現有session可能不會生效，新登入的會話會執行新的設定：

在例項級別修改引數設定：

ALTER SYSTEM SET EVENTS '10949 TRACE NAME CONTEXT FOREVER';

增加引數到SPFILE中：

alter system set event='10949 TRACE NAME CONTEXT FOREVER' scope=spfile;

對當前會話設定：

ALTER SESSION SET EVENTS '10949 TRACE NAME CONTEXT FOREVER';

參考：

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