找出導致db file scattered read等待事件發生的SQL及其執行計劃

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';

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/25964700/viewspace-705785/，如需轉載，請註明出處，否則將追究法律責任。