抓出 SELECT * 的 SQL

此脚本不依赖统计信息。

在开发过程中，我们应该尽量避免编写 SELECT * 这种 SQL。SELECT * 这种 SQL，走索引无法避免回表，走 HASH 连接的时候会将驱动表所有的列放入 PGA 中，浪费 PGA 内存。执行计划中（V$SQL_PLAN/PLAN_TABLE），projection 字段表示访问了哪些字段，如果 projection 字段中字段个数等于表的字段总个数，那么我们就可以判断 SQL 语句使用了 SELECT *。以下脚本抓出 SELECT * 的 SQL。

select a.sql_id, a.sql_text, c.owner, d.table_name, d.column_cnt, c.size_mb
  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 REGEXP_COUNT(b.projection, ']') = d.column_cnt
   and c.owner = 'SCOTT'
 order by 6 desc;

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

select * from t where object_id<1000;

我们使用脚本将其抓出。

select a.sql_id, a.sql_text, c.owner, d.table_name, d.column_cnt, c.size_mb
    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 REGEXP_COUNT(b.projection, ']') = d.column_cnt
     and c.owner = 'SCOTT'
   order by 6 desc;

SQL_ID       SQL_TEXT                           OWNER  TABLE_NAME COLUMN_CNT  SIZE_MB
---------- ----------------------------------- ------ -------- --------- ---------
ga64bhp5fxhtn select * from t where object_id<1000   SCOTT  T           15     9