抓出走了 INDEX FULL SCAN 的 SQL

此脚本不依赖统计信息。

我们在第 4 章中提到，INDEX FULL SCAN 会扫描索引中所有的叶子块，单块读。如果索引很大，执行计划中出现了 INDEX FULL SCAN，这时 SQL 会出现严重的性能问题，因此我们需要抓出走了 INDEX FULL SCAN 的 SQL。以下脚本抓出走了 INDEX FULL SCAN 的 SQL 并且根据索引段大小降序显示。

select c.sql_text, c.sql_id, b.object_name, d.mb
  from v$sql_plan b,
       v$sql c,
       (select owner, segment_name, sum(bytes / 1024 / 1024) mb
          from dba_segments
         group by owner, segment_name) d
 where b.sql_id = c.sql_id
   and b.child_number = c.child_number
   and b.object_owner = 'SCOTT'
   and b.operation = 'INDEX'
   and b.options = 'FULL SCAN'
   and b.object_owner = d.owner
   and b.object_name = d.segment_name
 order by 4 desc;

我们在 Scott 账户中运行如下 SQL。

select * from t where object_id is not null order by object_id;

在 object_id 列创建索引之后，执行上面 SQL 会自动走 INDEX FULL SCAN，使用脚本将其抓出。

 select c.sql_text, c.sql_id, b.object_name, d.mb
    from v$sql_plan b,
         v$sql c,
         (select owner, segment_name, sum(bytes / 1024 / 1024) mb
            from dba_segments
           group by owner, segment_name) d
   where b.sql_id = c.sql_id
     and b.child_number = c.child_number
    and b.object_owner = 'SCOTT'
     and b.operation = 'INDEX'
     and b.options = 'FULL SCAN'
      and b.object_owner = d.owner
     and b.object_name = d.segment_name
   order by 4 desc;

SQL_TEXT                         SQL_ID          OBJECT_NAME             MB
-------------------------------- --------------- --------------- ----------
select * from t where object_id  fkan9h6frsn90   IDX_ID                   2
is not null order by object_id

在实际工作中，我们可以对脚本作适当修改，例如过滤出大于 10GB 的索引。