1、通过查询v$sql,v$session,v$session_wait找出致等待事件的SQL

select sql_text, sql_fulltext
  from v$sql
 where address = (SELECT a.SQL_ADDRESS
                    FROM v$session a, v$session_wait b
                   WHERE a.SID = b.SID
                     AND b.event = 'db file scattered read);

2、找到对应SQL的执行计划

1）、在知道SID的情况下

SELECT hash_value,
       child_number,
       LPAD(' ', 2 * DEPTH) || operation || ' ' || options ||
       DECODE(ID, 0, SUBSTR(optimizer, 1, 6) || ' Cost=' || TO_CHAR(COST)) operation,
       object_name OBJECT,
       COST,
       ROUND(BYTES / 1024) kbytes
  FROM v$sql_plan
 WHERE hash_value IN
       (SELECT a.sql_hash_value FROM v$session a WHERE a.SID = 159)
 ORDER BY hash_value, child_number, ID;

2）、不知道SID的情况下

SELECT hash_value,
       child_number,
       LPAD(' ', 2 * DEPTH) || operation || ' ' || options ||
       DECODE(ID, 0, SUBSTR(optimizer, 1, 6) || ' Cost=' || TO_CHAR(COST)) operation,
       object_name OBJECT,
       COST,
       ROUND(BYTES / 1024) kbytes
  FROM v$sql_plan
 WHERE hash_value IN (SELECT a.sql_hash_value
                        FROM v$session a, v$session_wait b
                       WHERE a.SID = b.SID
                         AND b.event = 'db file scattered read‘)
 ORDER BY hash_value, child_number, ID;

附：我们可以通过查看v$sql_plan视图得到很多有用的信息哦，以下就是几个示例

1、得到全表扫描的对象及其SQL

select distinct object_name, object_owner
  from v$sql_plan p
 where p.operation = 'TABLE ACCESS'
   and p.options = 'FULL'
   and object_owner = 'SYS';

SELECT p.object_name, p.hash_value, t.piece, t.sql_text
  FROM v$sqltext t, v$sql_plan p
 WHERE t.hash_value = p.hash_value
   AND p.operation = 'TABLE ACCESS'
   and p.options = 'FULL'
   AND p.object_owner = 'SYS'
 ORDER BY p.hash_value, t.piece;

2、得到全索引扫描对象

select distinct object_name, object_owner
  from v$sql_plan p
 where p.operation = 'INDEX'
   and p.options = 'FULL SCAN'
   and p.object_owner = 'SYS';

3、得到索引范围扫描对象

select distinct object_name, object_owner
  from v$sql_plan p
 where p.operation = 'INDEX'
   and p.options = 'RANGE SCAN'
   and p.object_owner = 'SYS';