Oracle Wait event-direct path Read/write

Ext.://http://blog.chinaunix.net/uid-23177306-id-2531235.html

First, direct path read
1. Wait events associated with direct read. When Oracle reads data blocks directly into the PGA (Process Global Zone) of the session, it bypasses the SGA (System global Zone). Data in the PGA is not shared with other sessions. That is, this part of the data read is used on its own, not in the shared SGA.

2, when the sort operation (order By/group by/union/distinct/rollup/Merge Connection), due to the lack of sort_area_size space in the PGA, it is necessary to use temporary table space to save intermediate results. The direct path read wait event is generated when the sort result is read from the temporary tablespace.

3. Use a hash-connected SQL statement to flush the hash partition that is not suitable for in-memory into the temporary tablespace. To pinpoint the rows that match the SQL predicate, the hash partition in the staging table space is read back into memory (to find the row that matches the SQL predicate), and the Oralce session waits on the direct path read wait event.

4. SQL statements using parallel scans can also affect system-wide direct path read wait events. During parallel execution, the direct path read wait event is related to a dependent query, regardless of the parent query, where the session running the parent query basically waits on the PX deq:execute reply, and the subordinate query produces the direct path read wait event.

5. Direct reads may be performed synchronously or asynchronously, depending on the value of the platform and initialization parameters Disk_asynch_io parameters. When using asynchronous I/O, the statistics of system-wide wait events may be inaccurate and can be misleading.

6, the event is generally impossible to show as the main bottleneck, but it is actually the culprit. Because the way Oracle counts wait times can cause inaccurate statistics (such as when a dependent query is not being counted), the event should not be estimated using total_wait or time_waited in the V$session_event view. You should use the number of direct read operations (physical reads direct) in the V$sesstat view to determine:
Select A.name,b.sid,b.value,round ((sysdate-c.logon_time) *) hours_connected from V$statname A, V$sesstat B, v$session C WHERE B.sid = C.sid and a.statistic# = b.statistic# and b.value > 0 and A.name = ' physical reads direct ' ORDER by B. VALUE

7. The reason why the direct path read event occurs, we need to determine what segment the event is reading (such as hash segment, sort segment, general data file), so as to determine the cause of the event, can be queried using the following statement:
SELECT a.event,a.sid,c.sql_hash_value Hash_vale,decode (d.ktssosegt,1, ' SORT ', 2, ' hash ', 3, ' DATA ', 4, ' INDEX ', 5, ' lob_ DATA ', 6, ' Lob_index ', NULL) as Segment_type,b.tablespace_name,b.file_name from V$session_wait A, dba_data_files B, v$ Session C, X$ktsso d WHERE c.saddr = d.ktssoses (+) and c.serial# = D.ktssosno (+) and d.inst_id (+) = Userenv (' instance ') an D A.sid = c.sid and a.p1 = b.file_id and a.event = ' direct path read ' UNION all SELECT a.event,a.sid,d.sql_hash_value Hash _value,decode (e.ktssosegt,1, ' SORT ', 2, ' HASH ', 3, ' DATA ', 4, ' INDEX ', 5, ' Lob_data ', 6, ' Lob_index ', NULL) as Segment_type, B.tablespace_name,b.file_name from v$session_wait a,dba_temp_files b,v$parameter c,v$session d,x$ktsso e WHERE d.saddr = E.ktssoses (+) and d.serial# = E.ktssosno (+) and e.inst_id (+) = Userenv (' instance ') and A.sid = D.sid and b.file_id = A.p1 -C.value and c.name = ' db_files ' and a.event = ' direct path read ';
Note: If you are reading a session of a sort segment from a temporary file, the settings for sort_area_size or pga_aggregate_target are not small. If you are reading a session of a hash segment from a temporary file, it indicates that the setting for Hash_area_size or pag_aggregate_target is too small.

8. When the direct path read wait event is caused by a parallel query (reading a generic data file rather than a temporary file), the hashvalue of the parent SQL statement differs from the hashvalue of the child SQL statement. You can generate a parent SQL statement for a child SQL statement by using the following SQL query:
SELECT decode (a.qcserial#, NULL, ' PARENT ', ' child ') stmt_level,a.sid,a.serial#,b.username,b.osuser,b.sql_hash_value , B.sql_address,a.degree,a.req_degree from V$px_session A, v$session b WHERE a.sid = B.sid ORDER by A.qcsid, Stmt_level DES C

9. Initialization parameter db_file_direct_io_count is used to set the maximum IO buffer size for direct read-out and write operations, so it can affect the performance of direct path read, which is hidden in 9i and changed in bytes instead of blocks.
>> uses the 10046 8th layer to track Oracle sessions for direct read operations, where the P3 parameter indicates the number of blocks read.
>> can also use Strace,truss to track UNIX processes for direct read or direct write operations, and the corresponding direct IO size can be obtained from the generated trace file.
>> Use trace event 10357 on level 1th to start debugging information for a session that performs a direct IO operation.

10. A large number of direct path read wait events are most likely an application issue.

11. Parameter Description:
Event Number: 212
Event Name: Direct path Read
Parameter one: Read the absolute file number of the data file
Parameter two: Starting block number first DBA
Parameter three: number of blocks to read block CNT
Data objects that are accessed by the parameter P1 and P2:
Select S.segment_name, s.partition_name from dba_extents s where <p2 value > between s.block_id and (s.block_id + s.block S-1) and s.file_id = value of <P1 >
Note:
>> 1. If it is a temp file, it means that the session is reading the temporary segment that it created earlier with the direct path write operation, identifying what type of temporary segment is being used, and helping to understand what the session is doing.
SELECT DISTINCT decode (t.ktssosegt,1, ' SORT ', 2, ' HASH ', 3, ' DATA ', 4, ' INDEX ', 5, ' Lob_data ', 6, ' Lob_index ', ' UNDEFINED ') From Sys.x$ktsso t WHERE t.inst_id = userenv (' instance ') and t.kssoses = < current session address > and T.ktssosno = <serial#& Gt
>> 2. If it is a data file, it is possible that the parallel query subordinate operation is working, and the data file name is determined by the P1 value:
Select S.name from V$datafile s where s.file# = <P1 values > union ALL Select A.name from V$tempfile A, V$parameter b wher E b.name = ' db_files ' and a.file# + b.value = <p1 value >

12. Wait Time: No timeout

Second, direct path write (USER i/0 Class)
1. Writing data files from the PGA, a session can publish multiple write requests and continuous processing.
2. Direct writes can be performed synchronously or asynchronously, depending on the values of the platform and disk_async_io parameters.
3. Typically used to write to a temporary segment when data is loaded (append hint, ctas-create TABLE as SELECT), parallel DML operation.
4. When using asynchronous Io, the wait time generated by the direct path write event is inaccurate, so the V$sesstat view is used to obtain a direct write count to evaluate the event's impact:
SELECT A.name,b.sid,b.value,round ((sysdate-c.logon_time) *) hours_connected from V$statname A, V$sesstat B, v$session C WHERE a.statistic# = b.statistic# and B.sid = C.sid and b.value > 0 and A.name = ' physical writes direct '
5. Parameter Description:
Event Number: 213
Event name: Direct Path Write
Parameter one: the absolute file number to be written, the filename, to discover the nature of the operation performed (e.g., sort/parallel operation)
Parameter two: Starting block number first DBA
Parameter three: block number of blocks CNT, can find the size of the direct write IO
Data objects that are accessed by the parameter P1 and P2:
Select S.segment_name, s.partition_name from dba_extents s where <p2 value > between s.block_id and (s.block_id + s.block S-1) and s.file_id = value of &LT;P1 >
Note:
>> 1. If it is a temp file, it means that the session is writing to a temporary tablespace and finding out what type of temporary segment is used helps to understand what the session is doing.
SELECT DISTINCT decode (t.ktssosegt,1, ' SORT ', 2, ' HASH ', 3, ' DATA ', 4, ' INDEX ', 5, ' Lob_data ', 6, ' Lob_index ', ' UNDEFINED ') From Sys.x$ktsso t WHERE t.inst_id = userenv (' instance ') and t.kssoses = < current session address > and T.ktssosno = <serial#& Gt
>> 2. If it is a data file, you may be performing a direct path load operation that determines the name of the data file through the P1 value:
Select S.name from V$datafile s where s.file# = &LT;P1 values > union ALL Select A.name from V$tempfile A, V$parameter b wher E b.name = ' db_files ' and a.file# + b.value = <p1 value >
6, wait time: no super

Oracle Wait event-direct path Read/write