“DB File Sequential Read” Wait Event
I shall start with the wait event that is most associated with slow storage causing end-user delays, the Db file sequential read. This event represents a wait for a physical read of a single Oracle block from the disk. It is usually caused by reading an index block or accessing a table via a rowid (after it was obtained from an index block). Although the name may suggest that sequential I/O access path is being used, actually this event indicates random I/O access pattern. The following SQL statement illustrates a table that is being accessed via an index resulting in reads of a single block (one to access the index and another one to access the table):
The following diagram illustrates an Oracle shadow process that reads one block from the disk and places it in the buffer cache in the SGA (System Global Area):
Db file sequential read is almost always associated with index access path that reads a single block into the buffer cache. If a query performs multiple reads of single blocks (which results in several Db file sequential read waits) , the blocks most likely will not be adjacent on disk, resulting in Random I/Os.
Db file sequential read is the common wait event associated with disk I/O.
A performance of application that suffers from high percentage of Db file sequential read will almost always improve its performance with low latency storage.
“DB File Scattered Read” Wait Event
n this post I will describe the second most common event that I see when system suffer from I/O waits: db file scattered read.This important information is taken from the Oracle performance white paper written by Bartal Vindzberg and myself.
The db file scattered read wait event represents a wait for a physical read of multiple Oracle blocks from the disk (unlike db file sequential read which represents reading a single block). It is usually caused by scanning the entire or a sub-range of a table, index, table partition, or index partition. The following SQL statement illustrates accessing a table via full table scan:
The following diagram illustrates an Oracle shadow process that scans multiple blocks from the disk and places them in the buffer cache:
Adjacent blocks are read from disk allowing Oracle to read many blocks by performing large I/Os. Thus, from the storage prospective, db file scattered read is a large sequential read with block size that can get up to 1 MB.
db file scattered read wait event is common for applications with a high amount of large reads (such as full or range scans). This is true for BI, DWH and DSS workload environments.
In order to improve a db file scattered read wait event, a storage system needs to provide both low latency and high throughput.
Direct Path Read
In this post I will describe another common wait event that in many cases is caused by a weak storage performance.
Direct path read is an access path in which multiple Oracle blocks are read directly to the Oracle process memory without being read into the buffer cache in the Shared Global Area (SGA). This event is usually caused by scanning an entire table, index, table partition, or index partition during Parallel Query execution (although 11g support “direct path read” on serial scans). The following SQL statement illustrates a parallel query scanning a table:
| Sample SQL Query:Select /*+ Parallel(emp 4) */ * from Employee emp;
Execution Plan:
SELECT STATEMENT
PX COORDINATOR
PX RECEIVE
PX SEND RANGE
PX BLOCK ITERATOR
TABLE ACCESS FULL EMPLOYEE
|
The following diagram illustrates Oracle shadow processes that read multiple blocks in parallel from the disk and place them in the Oracle shadow processes memory bypassing the buffer cache in the SGA:
Below is an example of an AWR report illustrating where a “direct path read” is one of the top 5 wait events:
Top 5 Timed Events
Event
|
Waits
|
Time(s)
|
Avg Wait(ms)
|
% Total Call Time
|
Wait Class
|
db file scattered read
|
288,146
|
10,909
|
38
|
33.8
|
User I/O
|
direct path read
|
148,245
|
8,965
|
60
|
27.8
|
User I/O
|
CPU time
|
4,680
|
14.5
| |||
PX Deq Credit: send blkd
|
51,263
|
4,246
|
83
|
13.2
|
Other
|
db file sequential read
|
337,674
|
3,406
|
10
|
10.6
|
User I/O
|
The AWR report shows the number of waits the Oracle sessions encountered for this event. Note however, that many I/Os will be completed before the session actually needs to process the blocks (read ahead or pre-fetch operations). So, a low number of waits does not necessarily mean a low number of IOPS. This is also one of the reasons why parallel queries significantly increase the load on the storage, as I once wrote in the post https://kaminario.com/blog/storage-performance-and-parallelism/ that “direct path read” is common for applications with a high amount of large reads (such as full or range scans) using parallel query executions. This is true for BI, DWH and DSS workload environments.
Direct Path Read Temp
When a session reads buffers from disk directly into the PGA, the wait is on direct path read temp. This is closely related to the direct path read wait. If the I/O subsystem doesn’t support asynchronous I/Os, then each wait corresponds to a physical read request. If the I/O subsystem supports asynchronous I/O, then the process overlaps read requests with processing the blocks already in the PGA. When the process attempts to access a block in the PGA that has not yet been read from disk, it issues a wait call and updates the statistics for this event. So, the number of waits is not always the same as the number of read requests.
Similarly, the direct path write temp wait event and direct path write wait event can occur when a process is writing buffers directly from PGA. The process will wait on this event for the write call to complete. Operations that could perform direct path writes include when a sort goes to disk, parallel DML operations, direct-path INSERTs, parallel create table as select, and some LOB operations. Like direct path reads, the number of waits is not the same as number of write calls issued if the I/O subsystem supports asynchronous writes. The session will wait if it has processed the buffers in the PGA and can’t continue work until an I/O request completes.
Diagnosing the direct path read temp wait event
When a session is waiting on this event, an entry will be seen in the v$session_wait system view giving more information on the blocks being waited for:
SELECT p1 “file#”, p2 “block#”, p3 “class#” FROM v$session_wait WHERE event = ‘direct path read temp’;
In this case p1 represents the file_ID for the read call; p2 represents the start block_ID for the read call; while p3 is the number of blocks in the read call. Check the file_ID to see if it is for temp.
SELECT relative_fno, owner, segment_name, segment_type FROM dba_extents WHERE file_id = &file AND &block BETWEEN block_id AND block_id + &blocks – 1;
Causes for the direct path read temp wait event To reduce the direct path read wait event and direct path read temp wait event:
- High disk sorts – If the sorts are too large to fit in memory and get sent to disk, this wait can occur.
- Parallel slaves – Parallel slaves are used for scanning data or parallel DML may be used to create and populate objects. These may lead to direct path read wait and direct path write wait respectively.
- Direct path loads – The direct path API is used to pass data to the load engine in the server and can cause the related direct path write wait.
- Server process ahead of I/O – The server process is processing buffers faster than the I/O system can return the buffers. This can indicate an overloaded I/O system
- Data Warehouse – Sorts in a data warehouse environment may always go to disk leading to high waits on direct path read temp and/or direct path write temp.
Hash area size – For query plans that call for a hash join, excessive I/O could result from having HASH_AREA_SIZE too small.
Tune away the direct path read temp
High disk sorts
The disk sort distribution can be checked:
select a.instance_number,to_char(a.snap_time,’dd/mon/yyyy hh24:mi’) meas_date, b.value from stats$snapshot a, stats$sysstat b, v$statname c where c.name=’sorts (disk)’ and a.snap_time>sysdate-7 and c.statistic#=b.statistic# and b.snap_id=a.snap_id order by a.instance_number,a.snap_time
It is recommended to use pga_aggregate_target. This area is used both for sorts and the hash join area. If possible the PGA can be sized larger to fit more in-memory sorts reducing the disk sorts. It is important to remember that there are limitations of pga_aggregate_target: The total work area cannot exceed 200 megabytes of RAM because of the default setting for the hidden parameter_pga_max_size. No RAM sort may use more than 5% of pga_aggregate_target or _pga_max_size, whichever is smaller. This means that no task may use more than 200 megabytes for sorting or hash joins. Oracle does not recommend changing the hidden parameters, but for certain environments, the result can be beneficial.
To view the PGA and its distribution.
Select * from v$pgastat; NAME VALUE —————————————- ———- aggregate PGA target parameter 419430400 aggregate PGA auto target 343517184 global memory bound 20971520 total PGA inuse 37789696 total PGA allocated 42677248 maximum PGA allocated 53294080 total freeable PGA memory 0 PGA memory freed back to OS 0 total PGA used for auto workareas 0 maximum PGA used for auto workareas 16384 total PGA used for manual workareas 0 maximum PGA used for manual workareas 0 over allocation count 0 bytes processed 26643456 extra bytes read/written 0 cache hit percentage 100
Tune the sorts
Find the session experiencing the waits and look at the SQL statement currently being run to see the cause of the sorts. Query V$TEMPSEG_USAGE to find the SQL statement generating the sort. Also query the statistics from V$SESSTAT for the session to determine the size of the sort. Determine whether it is possible to reduce the sorting by tuning the SQL statement.
Full table scans
If tables are defined with a high degree of parallelism, the optimizer leans towards using full table scans with parallel slaves. For a direct path read, locate the object being loaded. Consider using disk striping or Automatic Storage Management (ASM) which can stripe for you.
Parallel DML
Check the I/O distribution across the disks and make sure your I/O is configured for the parallelism being used. Verify that the parameter DISK_ASYNCH_IO is set to true.
Conclusion
The direct path read temp wait event is most often encountered when the PGA is not able to support the size of the sorts. The closely related wait events of direct path read, direct path write temp, and direct path write can occur due to parallel operations, direct path inserts and overloaded I/O. But tuning the PGA, the I/O placement, and SQL tuning can reduce or eliminate this wait.
No comments:
Post a Comment