1.这个问题主要与sort有关。

2.确定对象的sql：

SELECT segment_name, partition_name, p1, p2, wait1.sql_hash_value
FROM dba_extents, wait1
WHERE wait1.p2 BETWEEN block_id AND (block_id + blocks - 1)
AND file_id = wait1.p1
AND wait1.event = 'direct path read'
ORDER BY segment_name

3.根据回话读取的数据库文件类型了解回话所做的事情。如果是临时文件，则回话正在读取先前通过direct path write操作的临时段。如果是数据文件，可能是并行查询从属操作在工作。

SELECT NAME
FROM v$datafile
WHERE 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

4.如果回话读取临时段，要查明是什么类型的段：

SELECT DISTINCT DECODE (ktssosegt,
1, 'SORT',
2, 'HASH',
3, 'DATA',
4, 'INDEX',
5, 'LOB_DATA',
6, 'LOB_INDEX',
'UNDEFINED'
)
FROM SYS.x$ktsso
WHERE inst_id = USERENV ('instance')
AND ktssoses = :cursor_session_address
AND ktssosno = :cursor_serial# ；

5.获取sql语句：

SELECT hash_value, address, piece, sql_text
FROM v$sqltext
WHERE hash_value = :1
ORDER BY hash_value, piece;

1.根据收集的等待事件，分析是那些对象以及对应的sql。

2.确定是那些对象，执行如下：

SELECT segment_name, partition_name, p1, p2
FROM dba_extents, wait1
WHERE wait1.p2 BETWEEN block_id AND (block_id + blocks - 1)
AND file_id = wait1.p1
ORDER BY segment_name

3.确定执行的sql语句，执行如下：

SELECT hash_value, address, piece, sql_text
FROM v$sqltext
WHERE hash_value IN (SELECT DISTINCT sql_hash_value
FROM wait1)
ORDER BY hash_value, piece;

SELECT hash_value, address, piece, sql_text
FROM v$sqltext
WHERE hash_value ＝ :1
ORDER BY hash_value, piece;

注意这个有可能一些已经不在shared pool。

4.另外通过这个脚本也可以确定对象，缺点这个块一定要读入sga。

SELECT DISTINCT a.object_name, a.subobject_name
FROM dba_objects a, SYS.x_$bh b
WHERE (a.object_id = b.obj OR a.data_object_id = b.obj)
AND b.file# = :p1
AND b.dbablk = :p2 ；

1。首先建立表

create table wait1 as
SELECT a.SID, a.seq#, a.event, a.p1text, a.p1, a.p1raw, a.p2text, a.p2,
a.p2raw, a.p3text, a.p3, a.p3raw, a.wait_time, a.seconds_in_wait,
a.state, b.serial#, b.username, b.osuser, b.paddr, b.logon_time,
b.process, b.sql_hash_value, b.saddr, b.module, b.row_wait_obj#,
b.row_wait_file#, b.row_wait_block#, b.row_wait_row#
FROM v$session_wait a, v$session b
WHERE a.SID = b.SID
AND b.username IS NOT NULL
AND b.TYPE <> 'BACKGROUND'
AND a.event IN
('db file sequential read',
'db file scattered read',
'latch free',
'direct path read',
'direct path write',
'enqueue',
'library cache pin',
'library cache load lock',
'buffer busy waits',
'free buffer waits'
);

2。建立这个脚本，适当修改收集间隔以及次数，这种缺点就是可能会遗漏，不过大部分应该的问题应该收集到，可能对性能有影响，特别是已经很慢的时候。

begin
dbms_lock.sleep(1);
for i in 1 .. 30000
loop
INSERT INTO wait1
SELECT a.SID, a.seq#, a.event, a.p1text, a.p1, a.p1raw, a.p2text, a.p2, a.p2raw, a.p3text, a.p3,
a.p3raw, a.wait_time, a.seconds_in_wait, a.state, b.serial#, b.username, b.osuser, b.paddr,
b.logon_time, b.process, b.sql_hash_value, b.saddr, b.module, b.row_wait_obj#, b.row_wait_file#,
b.row_wait_block#, b.row_wait_row#
FROM v$session_wait a, v$session b
WHERE a.SID = b.SID
AND b.username IS NOT NULL
AND b.TYPE <> 'BACKGROUND'
AND a.event
IN
('db file sequential read',
'db file scattered read',
'latch free',
'direct path read',
'direct path write',
'enqueue',
'library cache pin',
'library cache load lock',
'buffer busy waits',
'free buffer waits'
);
commit ;
dbms_lock.sleep(3);
end loop;
end;
/