抓出 NL 被驱动表走了全表扫描的 SQL

此脚本不依赖统计信息。

嵌套循环的被驱动表应该走索引，以下脚本抓出嵌套循环被驱动表走了全表扫描的 SQL，同时根据表大小降序显示。

select c.sql_text, a.sql_id, b.object_name, d.mb
  from v$sql_plan a,
       (select *
          from (select sql_id,
                       child_number,
                       object_owner,
                       object_name,
                       parent_id,
                       operation,
                       options,
                       row_number() over(partition by sql_id, child_number, parent_id order by id) rn
                  from v$sql_plan)
         where rn = 2) 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 a.sql_id = b.sql_id
   and a.child_number = b.child_number
   and a.operation like '%NESTED LOOPS%'
   and a.id = b.parent_id
   and b.operation = 'TABLE ACCESS'
   and b.options = 'FULL'
   and b.object_owner = d.owner
   and b.object_name = d.segment_name
 order by 4 desc;

我们在 Scott 账户中运行如下 SQL，强制两表走嵌套循环，强制两表走全表扫描。

select /*+ use_nl(a,b) full(a) full(b) */ *
  from a, b
 where a.object_id = b.object_id;

我们通过以上脚本将其抓出。

select c.sql_text, a.sql_id, b.object_name, d.mb
    from v$sql_plan a,
         (select *
            from (select sql_id,
                         child_number,
                         object_owner,
                         object_name,
                        parent_id,
                         operation,
                         options,
                        row_number() over(partition by sql_id, child_number, parent_id order by id) rn
                    from v$sql_plan)
           where rn = 2) 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 a.sql_id = b.sql_id
     and a.child_number = b.child_number
     and a.operation like '%NESTED LOOPS%'
     and a.id = b.parent_id
     and b.operation = 'TABLE ACCESS'
     and b.options = 'FULL'
     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 /*+ use_nl(a,b) full(a) full(b) */ *   6prgcr0qcj3qr   B                     9
 from a, b  where a.object_id = b.object_id