【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;