Features of direct path reads in Oracle 11g

 

1. directpath reads description

In versions earlier than Oracle 11g, if a full table scan is performed on a large table, the wait event is: DB file scattered read; in 11g, if a full table scan is performed on a large table, wait event is: direct path read; that is, in 11 GB, full table scan of large tables directly reads data blocks into the PGA region of the session. (For more information, see the following example ).

In 11g, data blocks are directly inserted into the PGA without passing through the SGA during full table scan of a large table. This results in a large physical read volume for each full table scan, while in 10g, because the data block for full table scan already exists in SGA, the physical reading of the data block for full table scan is 0.

However, this is mainly about Oracle's improvements in optimization strategies. That is to say, if large tables are frequently scanned in full tables, there will not be much in the production database. By directly reading data into PGA, this reduces the busy exchange degree of cachebuffer and improves the efficiency of cachebuffer.

 

Direct path read has more advantages:

1. reduces the use of plug-ins and avoids possible use of plug-ins

2. the size of physical Io no longer depends on the block in buffer_cache. Imagine that Block 1, 3, and 7 of an eight-block extent are in the cache, while Block 2, 4, and 6 are not cached, when reading this extent, the traditional method will perform four dB file sequential read operations on blocks 2, 4, and 6, which is a terrible situation, the efficiency is often much lower than reading all eight blocks in this interval at a time, although Oracle tries its best not to cache large table blocks (always put at the coldest end of the queue after reading) to avoid this situation, direct path read can completely avoid such problems, read as many physical blocks as possible at a time.

 

Direct path read also introduces some disadvantages:

1. Perform a segment-level checkpoint (A segmentcheckpoint) on the object before reading a segment in the direct path ).

2. Duplicate delayed block clearing operations may occur.

 

This Part references Maclean's blog:

Impact of direct path reading on delayed block clearing

Http://www.oracledatabase12g.com/archives/direct-read-impact-on-delayed-block-read.html

 

 

Description on MOS: High 'direct path read' waits in 11g [ID 793845.1]

There have beenchanges in 11g in the heuristics to choose between direct path reads or readsthrough buffer cache for serial table scans.

In 10g, serialtable scans for "large" tables used to go through cache (by default) which is not the case anymore. in 11g, this demo-to read viadirect path or through cache is based on the size of the table, buffer cachesize and varous other stats.

Direct path reads are faster than scattered reads andhave less impact on other processes because they avoid latches.

 

 

Ii. Wait Event Description

 

33 common waiting events in Oracle

Http://blog.csdn.net/tianlesoftware/article/details/5807800

 

2.1 view the waiting event category:

/* Formatted on 2010/8/11 16:08:55 (qp5v5.115.810.9015 )*/

Select wait_class #,

Wait_class_id,

Wait_class,

Count (*) as "count"

From v $ event_name

Group by wait_class #, wait_class_id, wait_class

Order by wait_class #;

 

Wait_class # wait_class_idwait_class count

------------------------------------------------------

0 1893977003 other 717

1 4217450380 application 17

2 3290255840 configuration 24

3 4166625743 administrative 54

4 3875070507 concurrency 32

5 3386400367 commit 2

6 2723168908 idle 94

7 2000153315 network 35

8 1740759767 useri/o 45

9 4108307767 micron EMI/O 30

10 2396326234 scheduler 7

11 3871361733 cluster 50

12 644977587 queueing 9

 

2.2 dB file scattered read wait event

This wait event is often seen in the actual production database. It is a waiting event caused by user operations. When the user sends an SQL operation such as reading multiple data blocks each time I/O is sent, this wait event is generated. The two most common scenarios are full table scan (FTS: full table scan) and quick index scan (IFFs: Index fast full scan ).

 

Scattered (divergence) in this name may lead to many people think that it is used to read data blocks in scattered mode. In fact, when such a wait event occurs, SQL operations are performed to read data blocks sequentially, such as the FTS or IFFs method (if the data block to be read already exists in memory ).

 

Scattered refers to the storage of read data blocks in the memory. After being read to the memory, the data blocks are stored in the memory in a distributed manner, rather than in a continuous manner.

 

This wait event has three parameters:

File #: the file number of the data file where the data block to be read is located.

Block #: the starting data block number to be read.

Blocks: the number of data blocks to be read.

 

2.3 direct path read wait event

This wait event occurs when the session directly reads data blocks to the PGA, instead of the SGA. The read data is usually private data of the session, therefore, you do not need to put it in SGA as shared data, because this does not make sense. The data is usually from data in a temporary segment, such as SQL sorting data in a session, data generated during parallel execution, and sorting data generated by hash join and merge join, because the data only makes sense to the SQL operations of the current session, it does not need to be placed in SGA.

 

When a direct path read wait event occurs, a large amount of temporary data is generated on the disk, such as sorting and parallel execution. Or the space in the PGA is insufficient.

 

This wait event has three parameters:

Descriptoraddress: A pointer pointing to a direct read I/O waiting for the current session.

First DBA: the oldest I/O block address in descriptor address.

Block CNT: Number of valid buffers involved in the descriptor address context.

 

2.4 direct path write wait event

This wait event is the opposite of direct path read. The session directly writes some data from the PGA to the disk file without passing through SGA.

 

This usually happens in:

Use temporary tablespace sorting (insufficient memory)

Direct data loading (using append to load data)

Parallel DML operations.

 

This wait event has three parameters:

Descriptoraddress: A pointer pointing to a direct I/O.

Firstdba: the oldest I/O data block address in descriptor address.

Blockcnt: Number of valid buffer involved in the descriptor address context.

 

 

For more information, see:

DB file sequential read

Http://sharklove.itpub.net/post/660/279770

 

DB file scattered read

Http://sharklove.itpub.net/post/660/279771

 

Direct path write (user I/0 class)

Http://sharklove.itpub.net/post/660/279774

 

Direct path read (user I/0 class)

Http://sharklove.itpub.net/post/660/279772

 

 

Iii. Example

 

Description of the script for viewing session statistics in Oracle

Http://blog.csdn.net/tianlesoftware/article/details/7689450

 

Explain the Oracle execution plan

Http://blog.csdn.net/tianlesoftware/article/details/5827245

 

3.1 Oracle 11gr2 example

 

SQL> show user

User is "Anqing"

 

SQL> Create Table Dave as select rownumrn, rpad ('A', 600, 'z') RP from dual connect by level <= 300000;

Table created.

 

SQL> set linesize 200 pagesize 1400;

SQL> select count (*) from Dave;

 

Count (*)

----------

300000

 

SQL> Col name for A50

SQL> select VM. Sid, vs. Name, VM. Value

2 from V $ mystat Vm, V $ sysstatvs

3 where VM. Statistic # = vs. Statistic #

4 and vs. Name in

5 ('cleanouts only-consistent read gets ',

6 'session logicalreads ',

7 'physicalreads ',

8 'physical readsdirect ',

9 'redo size ');

 

SID Name Value

----------------------------------------------------------------------

139 session logical reads 83269

139 physical reads 32505

139 physical reads direct 27273

139 redo size 577392

139 cleanouts only-consistent read gets 4

 

 

-- View the execution plan:

SQL> set autotrace on

SQL> select count (*) from Dave;

 

Count (*)

----------

300000

 

 

Execution Plan

----------------------------------------------------------

Plan hash value: 1718719579

 

-------------------------------------------------------------------

| ID | operation | Name | rows | cost (% CPU) | time |

-------------------------------------------------------------------

| 0 | SELECT statement | 1 | 7461 (1) | 00:01:30 |

| 1 | sort aggregate | 1 |

| 2 | table access full | Dave | 254k | 7461 (1) | 00:01:30 |

-------------------------------------------------------------------

 

Note

-----

-Dynamic sampling used for this statement (Level = 2)

 

 

Statistics

----------------------------------------------------------

4 recursive cballs

0 dB block gets

27343 consistent gets

27273 physical reads

0 redo size

527 bytes sent via SQL * Net toclient

520 bytes received via SQL * netfrom Client

2 SQL * Net roundtrips to/fromclient

0 sorts (memory)

0 sorts (Disk)

1 rows processed

 

SQL>

 

 

3.2 Oracle 10gr2 Test

 

SQL> select * from V $ version;

 

Banner

----------------------------------------------------------------

Oracle Database 10g enterprise editionrelease 10.2.0.4.0-Prod

PL/SQL release 10.2.0.4.0-Production

Core 10.2.0.4.0 Production

TNS for Linux: Version 10.2.0.4.0-Production

Nlsrtl version 10.2.0.4.0-Production

 

SQL> Create Table Dave as select rownumrn, rpad ('A', 600, 'z') RP from dual connect by level <= 300000;

 

Table created.

 

SQL> Col name for A50

SQL> select VM. Sid, vs. Name, VM. Value

2 from V $ mystat Vm, V $ sysstatvs

3 where VM. Statistic # = vs. Statistic #

4 and vs. Name in

5 ('cleanouts only-consistent read gets ',

6 'session logicalreads ',

7 'physicalreads ',

8 'physical readsdirect ',

9 'redo size ');

 

SID Name Value

----------------------------------------------------------------------

167 session logical reads 30345

167 physical reads 13

167 physical readsdirect 0

167 redo size 247320

167 cleanouts only-consistent read gets 0

-- Note that physicalreads direct here is 0

 

SQL> set autotrace on;

Sesql

SQL> select count (*) from Dave;

 

Count (*)

----------

300000

 

 

Execution Plan

----------------------------------------------------------

Plan hash value: 1718719579

 

-------------------------------------------------------------------

| ID | operation | Name | rows | cost (% CPU) | time |

-------------------------------------------------------------------

| 0 | SELECT statement | 1 | 7421 (1) | 00:01:30 |

| 1 | sort aggregate | 1 |

| 2 | table access full | Dave | 280k | 7421 (1) | 00:01:30 |

-------------------------------------------------------------------

 

Note

-----

-Dynamic sampling used for this statement

 

 

Statistics

----------------------------------------------------------

28 recursive cballs

0 dB block gets

27359 consistent gets

27304 physical reads

0 redo size

411 bytes sent via SQL * Net to client

400 bytes received via SQL * netfrom Client

2 SQL * Net roundtrips to/fromclient

0 sorts (memory)

0 sorts (Disk)

1 rows processed

 

SQL>

 

 

4. Related bugs

 

Using direct path read in Oracle 11g can directly read data into PGA, which reduces the busy exchange degree of cachebuffer and improves the efficiency of cachebuffer. however, the SGA buffer cache cannot be used because it avoids the SGA process.

 

There is a direct read-related bug in MOS, MOS as follows: Keep buffer pool does not work for largeobjects on 11g [ID 1081553.1]

 

4.1 symptoms

While doing fulltable scan, buffer cache keep pool is not being used to read the blocks of A (keep) table which size is> 10% of db_keep_cache_size.

-- In Oracle 11gr1, when we perform a full table scan, if the block to be read is greater than 10% of db_keep_cache_size, The buffercache keep pool will not be used.

 

Theseblocks are always being read through direct read, which can causeperformance problem due to non-caching/re-reading of keep object blocks.

-- The scanned block uses direct read, which causes performance problems.

 

4.2 cause

The problem is caused by bug8897574-keep buffer pool does not work

 

The issue is fixed in 12.1 and the fix isincluded in Patch Set 11.2.0.2 and higher.

-- The cause is that bug 8897574 has been fixed in Oracle 12.1 and 11.2.0.2.

 

Due to this bug, tables with size> 10% of cache size, were being treated as 'largetables 'for their reads and this resulted in execution of a newserial_direct_read path in 11g.

 

 

4.3 Solution

Download and apply patch 8897574 for 11.1.0.7 or 11.2.0.1 for your actualpsu level.

Or

Upgrade to 11.2.0.2 or higher

With the bug fix applied, any object in thekeep buffer pool, whose size is less than db_keep_cache_size, is considered ASA small or medium sized object. this will cache the read blocks and avoidsubsequent Direct Read for these objects.

 

@ As per Development Update in the bugreport, to workaround this issue we can set event1_49 at level 1.

 

 

 

 

 

Bytes -------------------------------------------------------------------------------------------------------

All rights reserved. reprinted articles are allowed, but source addresses must be indicated by links. Otherwise, the documents will be held legally responsible!

Skype: tianlesoftware

QQ: tianlesoftware@gmail.com

Email: tianlesoftware@gmail.com

Blog: http://www.tianlesoftware.com

WEAVER: http://weibo.com/tianlesoftware

Twitter: http://twitter.com/tianlesoftware

Facebook: http://www.facebook.com/tianlesoftware

LinkedIn: http://cn.linkedin.com/in/tianlesoftware

 

 

------- Add a group to describe the relationship between Oracle tablespace and data files in the remarks section. Otherwise, reject the application ----

Dba1 group: 62697716 (full); dba2 group: 62697977 (full) dba3 group: 62697850 (full)

Super DBA group: 63306533 (full); dba4 group: 83829929 dba5 group: 142216823

Dba6 group: 158654907 dba7 group: 172855474 DBA group: 104207940