direct path write temp wait events in oracle

This wait event is similar to direct path write event with only difference that I/O performed are made to temporary files. This wait event signifies sort segment, hashes or bitmap opearation are not fitting in the PGA. This is the case of one pass scan or multi pass scan. This wait event may also occur for creation of global  temporary tables.

So Direct path write temp is an direct access path in which many Oracle blocks are written directly
to the temporary files by the Oracle Server process.  To know which tempfiles are affected query p1 column from v$session.

Note: since temp file numbers are found by adding 200(default value of db_files or whatever the value of db_files has been set, related this P1 information to v$tempfile accordingly.
causes:

• sorting(order by)
• sort megre joi
• hash join
• union
• distinct
• create index
• creating global temporary tables

action:

• use order by on indexes column
• when use hash join make sure hash table can fit into memory(statistics must be gathered accurately else oracle can use bigger table for hashing)
• avoid sort merge join by using hint no_use_merge
• if column data is already sorted then use no sort in creating indexes
• use UNION ALL in place of UNIION

This wait event implies work area size operations are not fitting in PGA so increase PGA_AGGREGATE_TARGET. Other reason for not using the PGA inspite of setting it large is Oracle generally uses 5% to 25% of PGA_AGGREGATE_TARGET value as upper bound in 11g. Other criteria of upper bound is oracle does not use PGA above _PGA_MAX_SIZE  and _shm_max_size hidden parameters which defaults to 200M. So increase _PGA_MAX_SIZE ,_SHM_MAX_SIZE and PGA_AGGREGATE_TARGET.


I increased both PGA parameters in my dataware house databases in 10gR2 and processing time reduced significantltly

direct path write wait events in Oracle

This wait event belongs to "User I/O" wait class and signifies that Oracle Server Process is writing buffers ino datafiles bypassing the SGA. This occurs when direct path append insert operation is performed. This also occirs for CTAS done in parallel and parallel DML. The number of waits is not the same as number of write calls issued if the I/O subsystem supports asynchronous writes.

 Cases: It occurs in the following situations:

• Parallel DML are issued to create objects (CREATE TABLE hr.admin_emp_dept
  PARALLEL COMPRESS
  AS SELECT * FROM hr.employees
  WHERE department_id = 10;)
• Parallel DMLS is issued to populate objejcts
• Direct path loads  ( Insert /*+ append / into table2 select * from table1)

To see what object is undergoing above wait event query V$SESSION or V$SESSION_WAIT parameter columns:

Wait event parameters are explained as below: 
P1: File_id for the write call
P2: Start block_id for the write call
P3: Number of blocks in the write call

select name,parameter1,parameter2,parameter3,wait_class from v$event_name where name like 'direct path write';
NAME                 PARAMETER1           PARAMETER2           PARAMETER3           WAIT_CLASS
-------------------- -------------------- -------------------- -------------------- -------------
direct path write    file number          first dba            block cnt            User I/O
value of P1 and p2 can be passed to DBA_EXTENTS to find the object undergoing direct path write event.

Action Plan: Make sure I/O distribution is balanced and I/O subsystem is adequate to meet parallel I/O demand on data files, Ideally parallel degree should not exceed the actual parallelization data files on differnt devices.


 

direct path read temp wait events

Direct read temp wait event is similar to direct path read wait event except difference
that data which is read from disk into the PGA bypassing the SGA comes from the temp data
files instead of permanent data files. So sort segments, hashes and bit maps etc from
temp files are read into PGA

SELECT p1 “file#”, p2 “block#”, p3 “class#”
 FROM v$session
 WHERE event = ‘direct path read temp’;

Note: since temp file numbers are found by adding 200(default value of db_files or whatever the value of db_files has been set, related this P1 information to v$tempfile accordingly.


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;

ASH Query to find direct path read temp waits:

SELECT dba_objects.object_name,
dba_objects.object_type,
ash.event,
p1,p2,p3,
round(SUM(active_session_history.wait_time + active_session_history.time_waited) / 1000000, 2) ttl_wait_time
FROM v$active_session_history ash, dba_objects
WHERE sample_time BETWEEN sysdate-60/1440 and sysdate
AND active_session_history.current_obj# = dba_objects.object_id
AND event LIKE 'direct path read temp%'
GROUP BY dba_objects.object_name, dba_objects.object_type, ash.event,p1,p2,p3
ORDER BY 7 DESC;

How to tune:

1. Individual sessions are not alloted PGA greater than least (5% of PGA_AGGREGATE_TARGET, _PGA_MAX_SIZE)  So set  _pga_max_size to value higher than 200M to large value and increase
PGA_AGGREGATE_PARAMETER

If using shared servers then increase sort_area_size and hash_area_size

2. Query v$PGA_TARGET_ADVICE and see PGA_CACHE_HIT_PERCENTAGE should be 100% also PGA_OVERALLOCATION must be 0.

If you query V$PGASTAT you should PGA should not be overallocated than target.

3. use indexes for sorting

4. Aoid sort operations

5. try use UNION ALL instead UNION

6. try avoid use sort merge join by using hint NO_USE_MERGE
 

Buffer Busy Waits wait events

Buffer busy waits occur when another session is reading the block into the buffer OR Another session holds the buffer in an incompatible mode to our request. This wait event was known as buffer busy wait event before oracle 10.

These waits indicate read/read, read/write, or write/write contention. The Oracle session is waiting to pin a buffer. A buffer must be pinned before it can be read or modified. Only one process can pin a buffer at any one time.

This wait can be intensified by a large block size as more rows can be contained within
the block This wait happens when a session wants to access a database block in the buffer cache
but it cannot as the buffer is "busy It is also often due to several processes repeatedly reading the same blocks (eg: if lots of people scan the same index or data block)

These waits are common in an I/O bound system. These wait events may indicates presence of hot blocks even in tuned queries and presence of un-selective or right hand indexes.

Queries in 10g to find the segments whose data blocks have read contention:

1. SELECT p1 "file#", p2 "block#", p3 "class#",sql_id
FROM v$session
WHERE event = 'buffer busy waits' and username='&USER_NAME';

Parameters P1 and P2 represents the file# and block# while parameter p3 represents the wait_class id. In 9i Parameter P3 represented reason code


2. Pass above selected file# and block# to below query to find to the affected segment

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;

This block of contention can belong to data block,segement header or undo block.

The main way to reduce buffer busy waits is to reduce the total I/O on the system by tuning the query Depending on the block type, the actions will differ

Data Blocks:

-Eliminate HOT blocks from the application.

-Reduce the number of rows per block( by moving table to tablespace with smaller block size or by below techniques)

-Try rebuilding the object with a higher PCTFREE so that you reduce the number of rows per block.

-Increase INITRANS and MAXTRANS and reduce PCTUSED This will make the table less dense .

-Check for repeatedly scanned /unselective indexes.

-Check for 'right- hand-indexes' (indexes that get inserted into at the same point by many processes).

Segemnt Header:
Use Automate segment management that is bit maps or increase of number of FREELISTs and FREELIST GROUPs

Undo Header:Increase the number of Rollback Segments

block contention wait events are also recorded in specific view V$WAITSTAT and since V$SESSION has all the wait events data integrated with it from 10g and it also have the row wait information, below query can also be used to find the sql statements.

SELECT
s.p1 file_id, s.p2 block_id,o.object_name obj,
o.object_type otype,
s.SQL_ID,
w.CLASS,event
FROM v$session s,
( SELECT ROWNUM CLASS#, CLASS FROM v$waitstat ) w,
all_objects o
WHERE
event IN ('buffer busy waits'')
AND
w.CLASS#(+)=s.p3
AND o.object_id (+)= s.row_wait_OBJ#
ORDER BY 1;

SELECT SQL_FULLTEXT from V$SQL WHERE sql_id=&amp

SQL_FULLTEXT is CLOB column which displays full query

ASH samples the active sessions every one second and so we can query v$active_session_history also to get buffer busy waits or read by other session.

SELECT
p1 file_id , p2 block_id ,o.object_name obj,
o.object_type otype,
ash.SQL_ID,
w.CLASS
FROM v$active_session_history ash,
( SELECT ROWNUM CLASS#, CLASS FROM v$waitstat ) w,
all_objects o
WHERE event='buffer busy waits'
   AND w.CLASS#(+)=ash.p3
AND o.object_id (+)= ash.CURRENT_OBJ#
AND ash.sample_time > SYSDATE - &MIN/(60*24)
ORDER BY 1;

direct path read waits

Direct path read waits are associated with direct read operations. This wait event falls under the User I/O wait class. An Oracle direct read operation reads data directly into the session's PGA , bypassing the SGA. The data in PGA is not shared with other sessions.

Direct reads may be performed in synchronous or asynchronous mode depending on the platform , and the value of the DISK_ASYNC_IO parameter

A significant number of direct path read waits is most likely an application issue

Common causes, Diagnosis and Actions:

Till Oracle 9i this wait events occured for SQL statements with functions that require sorts such as ORDER BY, GROUP BY, UNION,DISTINCT and ROLLUP, HASH PARTITIONS that do not fit into SQL Work Area.

From Oracle 10g this wait event has been divided in two wait events 1. direct path read and 2. direct path read temp

Direct path read waits  in 10g onward occurs for read operations by parallel slaves used in parallel query. While direct path read temp waits occur for sort operations, hashes,index creation etc

This wait event has three parameters: FILE#, FIRST BLOCK#, and BLOCK COUNT.

If level 8 or level 12 10046 trace is enabled for session performing direct path read then number of blocks counts is shown by parameter p3 .

If session performing direct path read is traced with strace , truss, tusc or trace then size of block chunk of direct read can be seen

Note: There is a separate directr read wait event for LOB segments: direct path read(lob). This wait event applies for LOBs that are stored as NOCACHE. When LOBs are stored as CACHE reads and writes go through the SGA and show up as db file sequential reads.

Avoid sorting or hashing large tables else Increase PGA_AGGREGATE_TARGET or MEMORY_MAX_TARGET  after querying V$PGASTAT and V$PGA_TARGET_ADVICE or V$MEMORY_TARGET_ADVICE

read by other session wait events

Read by other session or buffer busy waits occur a when another session is reading the block into the  buffer  OR Another session holds the buffer in an  incompatible mode to our request.   This wait event was known as buffer busy wait event before oracle 10.

These waits indicate read/read, read/write, or write/write contention. The Oracle session is waiting to pin a buffer. A buffer must be pinned before it can be read or modified. Only one process can pin a buffer at any one time.

This wait can be intensified by a large block  size as more rows can be contained within
the block  This wait happens when a session wants to  access a database block in the buffer cache
but it cannot as the buffer is "busy  It is also often due to several processes  repeatedly reading the same blocks (eg: if lots of people scan the same index or data  block)

These waits are common in an I/O bound system.  These wait events may indicates presence of  hot blocks even in tuned queries and presence of un-selective or right hand indexes.

Queries in 10g to find the segments whose data blocks have read contention:

1. SELECT p1 "file#", p2 "block#", p3 "class#",sql_id
FROM v$session
WHERE event = 'read by other session' and username='&USER_NAME';

Parameters P1 and P2 represents the file# and block# while parameter p3 represents the wait_class id. In 9i Parameter P3 represented reason code


2. Pass above selected file# and block# to below query

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;


This block of contention can belong to data block,segement header or undo block.


The main way to reduce buffer busy waits is to reduce the total I/O on the system by tuning the query  Depending on the block type, the actions will differ

Data Blocks:
-Eliminate HOT blocks from the application.

-Reduce the number of rows per block( by moving table to tablespace with smaller block size or by below techniques)

-Try rebuilding the object with a higher PCTFREE so that you reduce the number of rows per block.

-Increase INITRANS and MAXTRANS and reduce PCTUSED This will make the table less dense .

-Check for repeatedly scanned /unselective indexes.

-Check for 'right- hand-indexes' (indexes that get inserted into at the same point by many processes).

Segemnt Header:
Use Automate segment management that is bit maps or  increase of number of FREELISTs and FREELIST GROUPs

Undo Header:Increase the number of Rollback Segments


block contention wait events are also recorded in specific view V$WAITSTAT and since V$SESSION has all the wait events data integrated with it from 10g and it also have the row wait information, below query can also be used to find the sql statements.


SELECT
      s.p1 file_id, s.p2 block_id,o.object_name obj,
       o.object_type otype,
       s.SQL_ID,
       w.CLASS,event
FROM v$session s,
     ( SELECT ROWNUM CLASS#, CLASS FROM v$waitstat ) w,
     all_objects o
WHERE
 event IN ('read by other session')
AND
    w.CLASS#(+)=s.p3
   AND o.object_id (+)= s.row_wait_OBJ#
ORDER BY 1;

SELECT SQL_FULLTEXT from V$SQL WHERE sql_id=&amp

SQL_FULLTEXT is CLOB column which displays full query

ASH samples the active sessions every one second and so we can query v$active_session_history also to get buffer busy waits or read by other session.

SELECT
     p1 file_id ,  p2  block_id ,o.object_name obj,
       o.object_type otype,
       ash.SQL_ID,
       w.CLASS
FROM v$active_session_history ash,
     ( SELECT ROWNUM CLASS#, CLASS FROM v$waitstat ) w,
      all_objects o
WHERE event='read by other session'
   AND w.CLASS#(+)=ash.p3
   AND o.object_id (+)= ash.CURRENT_OBJ#
      AND ash.sample_time > SYSDATE - &MIN/(60*24)
ORDER BY 1;