获取执行计划对象信息

先用explain解释sql执行计划，然后执行下面的脚本获取执行计划里相关对象的信息

----File name : execplan_stat.sql

----Purpose : view table stats in a plan_table

col object_owner for a10

col  object_owner  for  a10

col object_name for a30

col object_type for a20

col DEGREE for a10

col ind_tab for a30

select distinct a.object_owner,

                a.object_name,

                a.object_type,

                sum(b.bytes) / 1024 / 1024 "size(mb)",

                case a.object_type when 'TABLE' then null, when 'INDEX%' then d.table_name end ind_tab,

                case a.object_type when 'TABLE' then c.partitioned, when 'INDEX%' then d.partitioned end parted,

                case a.object_type when 'TABLE' then c.degree, when 'INDEX'% then d.degree end degree,

                case a.object_type when 'TABLE' then c.num_rows, when 'INDEX%' then d.num_rows end rows,

  from plan_table a, dba_segments b, dba_tables c, dba_indexes d

where a.object_name = b.segment_name

and a.object_owner=b.owner

   and ((a.object_name = c.table_name(+)

   and a.object_owner = c.OWNER)or (a.object_name = d.index_name(+)

   and a.object_owner = c.OWNER)) group by a.object_owner,

                a.object_name,

                a.object_type,c.partitioned,

                c.degree,

                c.num_rows;