Shulou(Shulou.com)06/01 Report--

In 10g, serial table scans for "large" tables go through the buffer cache (by default).

Oracle 10G, by default, even a full table scan of the table will be cached in buffer cache first, except in parallel mode

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 and later versions have changed, full table scanning can be performed by direct path reading (Direct Path Read) bypassing buffer cache, whether Direct Path Read depends on table size, buffer cache size, and other statistical information.

Since Direct Path Read is faster than scattered reads and has less impact on other processes by avoiding latch, Direct Path Read is likely to be used after 11G

Oracle direct path read related parameters

_ serial_direct_read

Whether the 11G direct path read limit is enabled, the default value is AUTO enabled, and the automatic direct path read feature is disabled when set to NEVER. This parameter can be dynamically modified at the instance or session level without restarting the instance.

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

The default value is 2% of buffer cache, in blocks

That is to say, if the number of blocks of table is greater than _ small_table_threshold, if the value of Oracle is considered as a large table, it will go to 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

Appears after 11.2.0.2, and the default value is 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: represents the decision of direct path read, based on the statistical information of table.

For example:

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

If the statistics query TABLE has a block less than _ small_table_threshold 480, it will not go to direct path read. If the statistics block is set to more than 480, it will go to direct path read.

FLASE: represents the decision to use direct path read, based on the actual number of block in table segment header

10949 event

Return to Oracle 10G and previous modes by setting the 10949 event mask direct path read feature:

Alter session set events' 10949 trace name context forever, level 1'

There is also a parameter _ very_large_object_threshold to set the upper limit (in MB units) to use DPR (direct path read), which needs to work in conjunction with 10949 events.

The 10949 event setting disables DPR at any level, but only for tables that are less than 5x BUFFER Cache, and DPR is also performed if a table is larger than the _ very_large_object_threshold setting.

The objectives of these limits are:

Full table scans of large tables must be performed in Direct Path Read mode to reduce the impact and performance impact on Buffer Cache.

However, we can adjust the parameters to determine the upper and lower limits of DPR execution.

Event 10949 can be set online, but it may not take effect for existing session, and new settings will be performed for newly logged in sessions:

Modify parameter settings at the instance level:

ALTER SYSTEM SET EVENTS '10949 TRACE NAME CONTEXT FOREVER'

Add parameters to SPFILE:

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

Set for the current session:

ALTER SESSION SET EVENTS '10949 TRACE NAME CONTEXT FOREVER'

Reference:

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)

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.