全自动SQL审核 - 抓出可以创建组合索引的 SQL（回表只访问少数字段） - 《SQL优化入门》

此脚本不依赖统计信息。

我们在第 1 章中讲到，回表次数太多会严重影响 SQL 性能。当 SQL 走索引回表只访问表中少部分字段，我们可以将这些字段与过滤条件组合起来建立为一个组合索引，这样就能避免回表，从而提升查询性能。下面脚本抓出回表只访问少数字段的 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 = 'BY INDEX ROWID'
   and  REGEXP_COUNT(b.projection, ']')/d.column_cnt<0.25
 order by 5 desc;

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

select object_name from t2 where object_id<1000;

942 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 921640168
-------------------------------------------------------------------------------------
| Id | Operation                   | Name      | Rows  | Bytes | Cost(%CPU)|Time    |
-------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT            |           |   917 | 27510 |    19  (0)|00:00:01|
|  1 |  TABLE ACCESS BY INDEX ROWID| T2        |   917 | 27510 |    19  (0)|00:00:01|
|* 2 |   INDEX RANGE SCAN          | IDX_T2_ID |   917 |       |     4  (0)|00:00:01|
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("OBJECT_ID"<1000)
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        141  consistent gets
          0  physical reads
          0  redo size
      24334  bytes sent via SQL*Net to client
       1102  bytes received via SQL*Net from client
         64  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
        942  rows processed

因为上面 SQL 回表只访问了 1 个字段，我们可以利用脚本将上面 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 = 'BY INDEX ROWID'
     and  REGEXP_COUNT(b.projection, ']')/d.column_cnt<0.25
   order by 5 desc;

SQL_ID        SQL_TEXT                     TABLE_NAME COLUMN_CNT SIZE_MB FILTER
-------------- -------------------------- ---------- ---------- ---------- ------
bzyprvnc41ak8 select object_name from t2   T2         1/15             9
              where object_id<1000