ITPub博客

首页 > 数据库 > Oracle > 找出导致db file scattered read等待事件发生的SQL及其执行计划

找出导致db file scattered read等待事件发生的SQL及其执行计划

原创 Oracle 作者:passion_of_data 时间:2011-08-25 10:55:00 0 删除 编辑

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/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
DBA

注册时间:2011-06-20

  • 博文量
    53
  • 访问量
    308489