Common Oracle Wait Event Descriptions (2)-direct path read / write

Jul 20 posted in Oracle (RSS), 624 views.

direct path read

Read directly associated with the wait event. When the ORACLE data block directly read into session PGA (process global area), the same time to bypass the SGA (system global area). PGA of data and other sessions are not shared. Which shows that this part of the data read into the sole use of the session, without letting go of the SGA shared.
In the sorting operation (order by / group by / union / distinct / rollup / merger connection), because PGA of SORT_AREA_SIZE space, resulting in need to use the temporary table space to hold intermediate results, when read from the temporary table space for sorting results produce direct path read wait events.
HASH connection using SQL statements, will not fit in the memory refresh the hash partition to the temporary table space. SQL predicate to identify matching rows, hash temporary table space partition is read back to memory (to match the SQL predicate to identify the line), ORALCE session in the direct path read wait events waiting.
SQL statements using parallel scanning system will also affect the scope of the direct path read wait events. In the parallel implementation of the process, direct path read wait events and subordinate inquiries, but nothing to do with the parent query, the query session is basically running the parent will PX Deq: Execute Reply waiting, dependent query will produce direct path read wait events.
May be read directly in accordance with synchronous or asynchronous manner, depending on the platform and the initialization parameters disk_asynch_io parameter. Use asynchronous I / O, the system-wide wait events statistics may be inaccurate, will cause misleading.
The event generally can not appear as a major bottleneck, but it actually is the culprit might be. ORACLE statistics as a way to wait for the time the amount of time will result in inaccurate statistics (such as: slave query time can not produce statistics), so the incident should not use the v $ session_event view total_wait or time_waited estimate, you should use v $ sesstat view directly the number of read operations (physical reads direct) to determine:

select a.NAME,
b.SID,
b.VALUE,
round ((sysdate - c.LOGON_TIME) * 24) 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

By the direct path read events causes, we need to determine what segment of the event is being read (eg: hash section, sorting section, a general data file), which can produce the event to judge what the reason, you can use The following query 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')
AND 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 the temporary file is read from the session sort segment, then that SORT_AREA_SIZE or PGA_AGGREGATE_TARGET setting is not too small. If the file is read from the temporary session HASH paragraph, then that HASH_AREA_SIZE or PAG_AGGREGATE_TARGET setting is not too small.

When the direct path read wait events is due to the parallel query result (read data files in general, rather than temporary files), the parent and child HASHVALUE SQL statements SQL statement HASHVALUE different SQL queries generated by the following sub-SQL statement parent SQL statement:

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 DESC

Used to set the initialization parameter db_file_direct_io_count directly read and write operations to set the maximum of the IO buffer size, it can affect the direct path read performance, the parameters were hidden in 9i, and changes in bytes and not block-number as a unit.

>> Using 10046 level 8 trace a direct read operation of the ORACLE session, in which P3 parameters indicate that the number of block reads.

>> Also use strace, truss up directly read or write directly to the UNIX process, from the TRACE file generated by the direct IO access to the appropriate size.

>> At the 1st floor 10357 use to track events to start the implementation of the direct IO operations session debugging information.

A large number of direct path read wait events is most likely an application problem.
Parameters:

Event Number: 212

Event Name: direct path read

Parameter 1: Read the data file's absolute file number of file number

Parameter 2: start block number first dba

Parameter 3: the number of blocks to read block cnt

Parameters P1 and P2 by the push to access the data objects:

select s.segment_name, s.partition_name
from dba_extents s
where <P2的值> between s.block_id and (s.block_id + s.blocks -1) and s.file_id = <P1的值>

Note:

>> 1. If the Temp files, then the session is reading it with a direct path write operations previously created temporary segment, to identify what type of use of the temporary section to help understand 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#>

>> 2. If the data file, it may be parallel query slave operation at work, by P1 to determine the value of the data file name:

select s.NAME from v $ datafile s where s.FILE # = <P1的值>
union all
select a.name
from v $ tempfile a, v $ parameter b
where b.NAME = 'db_files'
and a.FILE # + b.VALUE = <P1的值>

Wait time: No timeout

9, direct path write (USER I / 0 type)

Write data files from the PGA, a session can issue multiple write requests and continuous processing.
Direct write can be synchronous or asynchronous manner, depending on the platform and DISK_ASYNC_IO parameter.
Often used in the data load (APPEND prompt, CTAS-CREATE TABLE AS SELECT), the parallel DML operation is written to the temporary segment.
When using asynchronous IO, direct path write wait event produced inaccurate, so the view through the v $ sesstat to get the number of direct writes to evaluate the impact of the incident the situation:

SELECT a.NAME,
b.sid,
b.VALUE,
round ((SYSDATE - c.logon_time) * 24) 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'

Parameters:

Event Number: 213

Event Name: direct path write

Parameter 1: the absolute file number to be written to file number, the operation carried out can be found in nature (such as: sorting / parallel operation)

Parameter 2: start block number first dba

Parameter 3: blocks block cnt, can be found in the size of direct write IO

Parameters P1 and P2 by the push to access the data objects:

select s.segment_name, s.partition_name
from dba_extents s
where <P2的值> between s.block_id and (s.block_id + s.blocks -1) and s.file_id = <P1的值>

Note:

>> 1. If the Temp files, said that the session is written to temporary table space, use of temporary segment to identify the type of help to understand conversations do.

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#>

>> 2. If the data file, you may be implementing a direct path load operation, the value determined by P1 data file name:

select s.NAME from v $ datafile s where s.FILE # = <P1的值>
union all
select a.name
from v $ tempfile a, v $ parameter b
where b.NAME = 'db_files'
and a.FILE # + b.VALUE = <P1的值>