Search Oracle Related Sites

Monday, August 31, 2015

db file scattered read, db file sequential read & direct read wait events explanation

As Oracle DBA's we come across these wait events in most environments. We will discuss their definitions and see what we can do to reduce them.

db file scattered read

This event signifies that the user process is reading buffers into the SGA buffer cache and is waiting for a physical I/O call to return. A db file scattered read issues a scattered read to read the data into multiple discontinuous memory locations of buffer cache.A scattered read is usually a multi block read. It can occur for a fast full scan (of an index) in addition to a full table scan.

The db file scattered read wait event identifies that a full scan is occurring. When performing a full scan into the buffer cache, the blocks read are read into memory locations that are not physically adjacent to each other. Such reads are called scattered read calls, because the blocks are scattered throughout memory. This is why the corresponding wait event is called 'db file scattered read'. multiblock (up to DB_FILE_MULTIBLOCK_READ_COUNT blocks) reads due to full scans into the buffer cache show up as waits for 'db file scattered read'

db file sequential read

This event signifies that the user process is reading a buffer into the SGA buffer cache and is waiting for a physical I/O call to return. A sequential read is a single-block read.

Single block I/Os are usually the result of using indexes. Rarely, full table scan calls could get truncated to a single block call due to extent boundaries, or buffers already present in the buffer cache. These waits would also show up as 'db file sequential read'.

direct path read

The session is waiting for a direct read to complete. A direct read is a physical I/O from a data file that bypasses the buffer cache and reads the data block directly into process-private memory.

If asynchronous I/O is supported (and in use), then Oracle can submit I/O requests and continue processing. Oracle can then pick up the results of the I/O request later and wait on "direct path read" until the required I/O completes.

If asynchronous I/O is not being used, then the I/O requests block until completed but these do not show as waits at the time the I/O is issued. The session returns later to pick up the completed I/O data but can then show a wait on "direct path read" even though this wait will return immediately. Hence this wait event is very misleading because: • The total number of waits does not reflect the number of I/O requests • The total time spent in "direct path read" does not always reflect the true wait time.

 All three wait events are graphically represented below.                                                                                                    
 

For all the three wait events Check the following V$SESSION_WAIT parameter columns:
·         P1 - The absolute file number
·         P2 - The block being read

·         P3 - The number of blocks (should be greater than 1)



Solution:

Reducing Waits / Wait times:

Block reads are fairly inevitable so the aim should be to minimize un-necessary IO. This is best achieved by good application design and efficient execution plans. Changes to execution plans can yield orders of magnitude changes in performance. Tweaking at system level usually only achieves percentage gains. The following points may help:
  • Check for SQL using unselective index scans

  • A larger buffer cache can help - test this by actually increasing << Parameter: DB_CACHE_SIZE>> (or <<Parameter:DB_BLOCK_BUFFERS>> if still using that). Never increase the SGA size if it may induce additional paging or swapping on the system.
  •  A less obvious issue which can affect the IO rates is how well data is clustered physically. Eg: Assume that you frequently fetch rows from a table where a column is between two values via an index scan. If there are 100 rows in each index block then the two extremes are:     
    1. Each of the table rows is in a different physical block (100 blocks need to be read for each index block)
    2. The table rows are all located in the few adjacent blocks (a handful of blocks need to be read for each index block)

Pre-sorting or re-organizing data can help to tackle this in severe situations.

  • See if partitioning can be used to reduce the amount of data you need to look at.  

  • It can help to place files which incur frequent index scans on disks which have are buffered by a cache of some form. eg: flash cache or hardware disk cache. For non-ASM based databases put such datafiles on a filesystem with an O/S file system cache. This can allow some of Oracles read requests to be satisfied from the cache rather than from a real disk IO.


Response Times will vary from system to system. As an example, the following could be considered an acceptable average:
10 ms for MultiBlock Synchronous Reads
  5 ms for SingleBlock Synchronous Reads
  3 ms for 'log file parallel write'
This is based on the premise that multi block IO may require more IO subsystem work than a single block IO and that, if recommendations are followed, redo logs are likely to be on the fastest disks with no other concurrent activity
IO Wait Outliers (Intermittent Short IO Delays)
Even though the average IO wait time may be well in the accepted range, it is possible that "hiccups" in performance may be due to a few IO wait outliers. In 12c the following views contain entries corresponding to I/Os that have taken a long time (more than 500 ms) In 11g, the information in the Wait Event Histogram sections of the AWR report may be useful in determining whether there are some IOs that are taking longer than average Log write waits over 500ms are also written to the LGWR trace file

For more information on outliers in 12C see:
Oracle Database Online Documentation 12c Release 1 (12.1)Database Administration
Database Reference
V$IO_OUTLIER
V$LGWRIO_OUTLIER
V$KERNEL_IO_OUTLIER  (only populated on Solaris)
Identifying  IO Response Time
Oracle records the response time of IO operations as the "Elapsed Time" indicated in  specific wait events and statistics."Response time" and "elapsed time" are synonymous and interchangeable terms in this context.
Below is a list of some of the more popular wait events and their typical acceptable wait times (not an exhaustive list)
Wait Event
R/W
Synchronous
/Asynchronous
Singleblock/
Multiblock
Elapsed Time
(with 1000+ waits per hour)
control file parallel write
Write
Asynchronous
Multi
< 15ms
control file sequential read
Read
Synchronous
Single
< 20 ms
db file parallel read
Read
Asynchronous
Multi
< 20 ms
db file scattered read
Read
Synchronous
Multi
< 20 ms
db file sequential read
Read
Synchronous
Single
< 20 ms
direct path read
Read
Asynchronous
Multi
< 20 ms
direct path read temp
Read
Asynchronous
Multi
< 20 ms
direct path write
Write
Asynchronous
Multi
< 15 ms
direct path write temp
Write
Asynchronous
Multi
< 15 ms
log file parallel write
Write
Asynchronous
Multi
< 15 ms
Exadata Related
cell smart table scan
Read
Asynchronous
Multi
< 1 ms
cell single block physical read
Read
Synchronous
Single
< 1 ms
cell multiblock physical read
Read
Synchronous
Multi
< 6 ms
 references: asktom, oracle documentation.

 


Friday, January 3, 2014

Top 5 Timed Foreground Events - Streams miscellaneous event

Streams miscellaneous event

The Streams miscellaneous event will be renamed to "Waiting for additional work from the logfile" to better describe the activity from Oracle release 11.2.0.2.x See detail in BugDB 12341046 for more information

In AWR (Automatic Workload Repository) you may see a Streams miscellaneous event as a top resource consumers in the wait class OTHER , when running Oracle Goldengate (OGG) EXTRACT with the parameter TRANLOGOPTIONS DBLOGREADER set.





As of OGG 11.1.1.0.0 onwards the extract process can use the OCIPOGG logreader module if the TRANLOGOPTIONS DBLOGREADER parameter is set in the extract parameter file.

NOTE :  to use the logreader functionality your Oracle release must also be 10.2.0.5+ or 11.2.0.2+

CAUSE

The cause of the issue has been identified in unpublished Bug 12341046. It is caused by incorrectly associating the wait for a redo log with the "Streams miscellaneous event" rather than the IDLE wait event "Streams capture: waiting for archive log"

Solution

 To confirm that this is the case you can do the following

1. Get the operating system pocess ids of the processes which are waiting on the  'Streams miscellaneous event'
connect / as sysdba  
set markup html on
spool processes.html 
select s.sid, s.serial#, s.process, p.spid, p.pid, p.program from v$session s, v$session_wait sw, v$process p where s.sid=sw.sid and sw.event = 'Streams miscellaneous event' and s.paddr=p.addr;

spool off

2.  Attach to the processes and generate sql trace 

oradebug setospid <spid>; 

oradebug TRACEFILE_NAME 

oradebug event 10046 trace name context forever, level 12 

--Trace for 5 minutes 

oradebug event 10046 trace name context off

In the tracefile for processes associated with the GG Extract process you should see entries of the form 
WAIT #0: nam='log file sequential read' ela= 2823 log#=0 block#=1 blocks=1 obj#=-1 tim=8507422526613 <<< READING the redo logs
WAIT #0: nam='log file sequential read' ela= 221 log#=0 block#=235 blocks=2  obj#=-1 tim=8507422526918<<< READING the redo logs 
WAIT #0: nam='log file sequential read' ela= 233 log#=0 block#=235 blocks=2 obj#=-1 tim=8507422527161<<< READING the redo logs 
WAIT #0: nam='Streams miscellaneous event' ela= 488302 TYPE=16 p2=0 p3=0 obj#=-1 tim=8507423015480 
TYPE=16 confirms that we are waiting for redo and indicates that the fix for unpublished Bug 12341046 will resolve the issue

Bug 12341046 is fixed in Oracle RDBMS 12.1 onwards

Tuesday, December 31, 2013

Exadata Wait Event - Cell Statistics Gather


cell statistics gather

This wait event appears when a select is done on the V$CELL_STATE , V$CELL_THREAD_HISTORY , or V$CELL_REQUEST_TOTALS tables. During the select, data from the cells and any wait events are shown in this wait event.


cell statistics gather ==> appears when a select is done on the V$CELL_STATE, V$CELL_THREAD_HISTORY, or V$CELL_REQUEST_TOTALS tables. During the select, data from the cells and any wait events are shown in this wait event.

Exadata Wait Event - Cell Smart Table Scan


cell smart table scan

This wait event appears when the database is waiting for table scans to complete on a cell. The cell hash number in the P1 column in the V$SESSION_WAIT view for this event should help identify a slow cell when compared to the rest of the cells.


cell smart table scan ==> appears when the database is waiting for table scans to complete on a cell

Exadata Wait Event - Cell Smart Restore from Backup


cell smart restore from backup

This wait event appears when the database is waiting for the completion of a file initialization for restore from backup on a cell. The cell hash number in the P1 column in the V$SESSION_WAIT view for this event should help identify a slow cell when compared to the rest of the cells.
 
cell smart restore from backup ==> Appears when the database is waiting for the completion of a file initialization for restore from backup on a cell

Exadata Wait Event - Cell Smart Index Scan


cell smart index scan

This wait event appears when the database is waiting for index or index-organized table (IOT) fast full scans. The cell hash number in the P1 column in the V$SESSION_WAIT view for this event should help identify a slow cell when compared to the rest of the cells.


cell smart index scan ==> Appears when the database is waiting for index or index-organized table (IOT) fast full scans

Exadata Wait Event - Cell Smart Incremental Backup


cell smart incremental backup

This wait event appears when the database is waiting for the completion of an incremental backup on a cell. The cell hash number in the P1 column in the V$SESSION_WAIT view for this event should help identify a slow cell when compared to the rest of the cells.

Exadata Wait Event - Cell Smart File Creation


cell smart file creation
This wait event appears when the database is waiting for the completion of a file creation on a cell. The cell hash number in the P1 column in the V$SESSION_WAIT view for this event should help identify a slow cell compared to the rest of the cells.

cell smart file creation ==> Appears when the database is waiting for the completion of a file creation on a cell