Oracle direct path read相关隐含参数

刺骨的言语ヽ痛彻心扉 2022-10-15 12:54 175阅读 0赞

In 10g, serial table scans for “large” tables go through the buffer cache (by default).  
[Oracle 10G，默认时，即使对表全表扫描也会先缓存到buffer cache中，parallel方式除外][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大小，其它统计信息。][Oracle 10G_buffer cache_parallel]

[由于Direct Path Read比scattered reads快，并且由于避免latch而对其他进程的影响较小，因此11G之后很可能使用Direct Path Read][Oracle 10G_buffer cache_parallel]

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM5MjIxNDM2_size_16_color_FFFFFF_t_70]

Description of Figure 10-1 follows

Oracle direct path read相关参数

\_serial\_direct\_read

[是否启用11G direct path read限制，其默认值为AUTO启用，][Oracle 10G_buffer cache_parallel]设置为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][Oracle 10G_buffer cache_parallel]

FLASE: 代表走direct path read的判定，基于table segment header实际的block数

10949 事件

[通过设置10949事件屏蔽direct path read特性，返回到Oracle 10G及之前的模式：][Oracle 10G_buffer cache_parallel]  
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。][Oracle 10G_buffer cache_parallel]

这些限定的目标在于：  
对于大表的全表扫描，必须通过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)

完整实例：[http://github.crmeb.net/u/defu][Oracle 10G_buffer cache_parallel]

来自 “开源世界 ” ，链接：[http://ym.baisou.ltd/post/609.html][http_ym.baisou.ltd_post_609.html]，如需转载，请注明出处，否则将追究法律责任

[Oracle 10G_buffer cache_parallel]: http://github.crmeb.net/u/defu
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM5MjIxNDM2_size_16_color_FFFFFF_t_70]: /images/20221014/d41004e791ad4661aeb7dd02f210b7cc.png
[http_ym.baisou.ltd_post_609.html]: http://ym.baisou.ltd/post/609.html