实验环境

搭建平台：VMware Workstation

OS：RHEL 6.10

Grid&DB：Oracle 11.2.0.4

SQL参考

select case

when access_predicates is not null or filter_predicates is not null then

'*' || id

else

' ' || id

end as "Id",

lpad(' ', level) || operation || ' ' || options "Operation",

object_name "Name",

cardinality "Rows",

b.size_mb "Mb",

case

when object_type like '%TABLE%' then

REGEXP_COUNT(a.projection, ']') || '/' || c.column_cnt

end as "Column",

access_predicates "Access",

filter_predicates "Filter",

case

when object_type like '%TABLE%' then

projection

end as "Projection"

from plan_table a,

(select owner, segment_name, sum(bytes / 1024 / 1024) size_mb

from dba_segments

group by owner, segment_name) b,

(select owner, table_name, count(*) column_cnt

from dba_tab_cols

group by owner, table_name) c

where a.object_owner = b.owner(+)

and a.object_name = b.segment_name(+)

and a.object_owner = c.owner(+)

and a.object_name = c.table_name(+)

start with id = 0

connect by prior id = parent_id;

ITPub博客

【TUNE_ORACLE】定制化执行计划SQL参考