抓出走了 TABLE ACCESS FULL 的 SQL

此脚本不依赖统计信息。

如果一个大表走了全表扫描，会严重影响 SQL 性能。这时我们可以查看大表与谁进行关联。如果大表与小表（小结果集）关联，我们可以考虑让大表作为嵌套循环被驱动表，大表走连接列索引。如果大表与大表（大结果集）关联，我们可以检查大表过滤条件是否可以走索引，也要检查大表被访问了多少个字段。假设大表有 50 个字段，但是只访问了其中 5 个字段，这时我们可以建立一个组合索引，将 where 过滤字段、表连接字段以及 select 访问的字段组合在一起，这样就可以直接从索引中获取数据，避免大表全表扫描，从而提升性能。下面脚本抓出走了全表扫描的 SQL，同时显示访问了表多少个字段，表一共有多少个字段以及表段大小。

select a.sql_id,
       a.sql_text,
       d.table_name,
       REGEXP_COUNT(b.projection, ']') ||'/'|| d.column_cnt  column_cnt,
       c.size_mb,
       b.FILTER_PREDICATES filter
  from v$sql a,
       v$sql_plan b,
       (select owner, segment_name, sum(bytes / 1024 / 1024) size_mb
          from dba_segments
         group by owner, segment_name) c,
       (select owner, table_name, count(*) column_cnt
          from dba_tab_cols
         group by owner, table_name) d
 where a.sql_id = b.sql_id
   and a.child_number = b.child_number
   and b.object_owner = c.owner
   and b.object_name = c.segment_name
   and b.object_owner = d.owner
   and b.object_name = d.table_name
   and c.owner = 'SCOTT'
   and b.operation = 'TABLE ACCESS'
   and b.options = 'FULL'
 order by 5 desc;

在 Scott 账户中运行如下 SQL。

select owner,object_name from t where object_id>100;

使用脚本将其抓出。

 select a.sql_id,
         a.sql_text,
          d.table_name,
         REGEXP_COUNT(b.projection, ']') || '/' || d.column_cnt column_cnt,
         c.size_mb,
         b.FILTER_PREDICATES filter
    from v$sql a,
         v$sql_plan b,
         (select owner, segment_name, sum(bytes / 1024 / 1024) size_mb
            from dba_segments
            group by owner, segment_name) c,
         (select owner, table_name, count(*) column_cnt
            from dba_tab_cols
           group by owner, table_name) d
   where a.sql_id = b.sql_id
     and a.child_number = b.child_number
     and b.object_owner = c.owner
     and b.object_name = c.segment_name
     and b.object_owner = d.owner
     and b.object_name = d.table_name
     and c.owner = 'SCOTT'
     and b.operation = 'TABLE ACCESS'
    and b.options = 'FULL'
   order by 5 desc;

SQL_ID        SQL_TEXT                        TABLE_NAME COLUMN_CNT    SIZE_MB FILTER
------------- ------------------------------- ---------- ---------- ---------- ------
51mu5j3aydw94 select owner,object_name from t  T          2/15                9

在实际工作中，我们可以对脚本适当修改，比如过滤出大于 1GB 的表、过滤出表总字段数大于 20 的表、过滤出访问了超过 10 个字段的表等。