抓出走了笛卡儿积的 SQL

此脚本不依赖统计信息。

我们在第 5 章中提到过笛卡儿积连接。当两表没有关联条件的时候就会走笛卡儿积，当 Rows 被估算为 1 的时候，也可能走笛卡儿积连接。下面脚本抓出走了笛卡儿积的 SQL。

select c.sql_text,
       a.sql_id,
       b.object_name,
       a.filter_predicates filter,
       a.access_predicates predicate,
       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 = 1) 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 = 'MERGE JOIN'
   and a.id = b.parent_id
   and a.options = 'CARTESIAN'
   and b.object_owner = d.owner
   and b.object_name = d.segment_name
 order by 4 desc;

在 Scott 账户中运行如下 SQL。

select * from a,b;

利用脚本将其抓出。

select c.sql_text,
         a.sql_id,
         b.object_name,
         a.filter_predicates filter,
         a.access_predicates predicate,
         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 = 1) 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 = 'MERGE JOIN'
     and a.id = b.parent_id
     and a.options = 'CARTESIAN'
     and b.object_owner = d.owner
     and b.object_name = d.segment_name
   order by 4 desc;

SQL_TEXT             SQL_ID          OBJECT_NAME  FILTER     PREDICATE          MB
-------------------- --------------- ------------ ---------- ---------- ----------
select * from a,b    9kwdjbbs50kcu   A                                           9