实验环境

搭建平台：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;

ITPub博客

【TUNE_ORACLE】列出走了笛卡尔积的SQL参考