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

參考：

https://www.eygle.com/archives/2012/05/oracle_11g_direct_path_read.html

http://www.savedba.com/?p=619

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