Oracle direct path read相關(guān)隱含參數(shù)

Oracle 10G，默認(rèn)時(shí)，即使對(duì)表全表掃描也會(huì)先緩存到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及之后版本，發(fā)生了變化，全表掃描可通過直接路徑讀（Direct Path Read）繞開buffer cache方式來執(zhí)行，是否Direct Path Read取決于table大小，buffer cache大小，其它統(tǒng)計(jì)信息。

由于Direct Path Read比scattered reads快，并且由于避免latch而對(duì)其他進(jìn)程的影響較小，因此11G之后很可能使用Direct Path Read

Oracle direct path read相關(guān)參數(shù)

_serial_direct_read 

是否啟用11G direct path read限制，其默認(rèn)值為AUTO啟用，設(shè)置為NEVER時(shí)禁用自動(dòng)direct path read的特性。該參數(shù)可以動(dòng)態(tài)在實(shí)例或會(huì)話級(jí)別修改，而無需重啟實(shí)例。

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 

默認(rèn)值為buffer cache的2%, 單位:塊

就是說 table的blocks數(shù)大于_small_table_threshold這個(gè)值Oracle就認(rèn)為是大表，就會(huì)走 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之后出現(xiàn)，默認(rèn)值為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的統(tǒng)計(jì)信息。

比如：

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

統(tǒng)計(jì)信息查詢TABLE有100個(gè)block小于_small_table_threshold 480話，就不會(huì)走direct path read。如果統(tǒng)計(jì)信息block設(shè)置超過480話就會(huì)走direct path read

FLASE: 代表走direct path read的判定，基于table segment header實(shí)際的block數(shù)

10949 事件

通過設(shè)置10949事件屏蔽direct path read特性，返回到Oracle 10G及之前的模式：
alter session set events '10949 trace name context forever, level 1';

還有一個(gè)參數(shù) _very_large_object_threshold 用于設(shè)定（MB單位）使用DPR( direct path read)方式的上限，這個(gè)參數(shù)需要結(jié)合10949事件共同發(fā)揮作用。
10949 事件設(shè)置任何一個(gè)級(jí)別都將禁用DPR的方式，但是僅限于小于 5 倍 BUFFER Cache的數(shù)據(jù)表，同時(shí)，如果一個(gè)表的大小大于 0.8 倍的 _very_large_object_threshold  設(shè)置，也會(huì)執(zhí)行DPR。

這些限定的目標(biāo)在于：
對(duì)于大表的全表掃描，必須通過Direct Path Read方式執(zhí)行，以減少對(duì)于Buffer Cache的沖擊和性能影響。
但是我們可以通過參數(shù)調(diào)整來決定執(zhí)行DPR的上限和下限。

Event 10949 可以在線設(shè)置，但對(duì)現(xiàn)有session可能不會(huì)生效，新登錄的會(huì)話會(huì)執(zhí)行新的設(shè)置：

在實(shí)例級(jí)別修改參數(shù)設(shè)置：

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

增加參數(shù)到SPFILE中：

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

對(duì)當(dāng)前會(huì)話設(shè)置：

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)