【TUNE_ORACLE】列出走了笛卡爾積的SQL參考

實驗環境

搭建平臺：VMware Workstation

OS：RHEL 6.10

Grid&DB：Oracle 11.2.0.4

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 = 'TEST'

   and a.sql_id = b.sql_id

   and a.child_number = b.child_number

   and a.operation like 'MERGE JOIN'    --一般情況下出現 MERGE JOIN  CARTESIAN（笛卡爾積連線）要麼是SQL程式有問題，要麼是執行計劃不準，大多數是執行計劃不準導致

   and a.options = 'CARTESIAN'

    and a.id = b.parent_id

   and b.object_owner = d.owner

   and b.object_name = d.segment_name

 order by 4 desc;