--输入SQL_ID, 获取该SQL相关的表及索引、栏位等信息
COL TABLE_NAME ON for a28
COL index_name ON for a28
COL column_name ON for a16
COL POS FOR 99
COL tBlk for 9EEEE
COL rRow for 9EEEE
COL iBlk for 9EEEE
COL iKey for 9EEEE
COL cKey for 9EEEE
COL cnull for 9EEEE
break on table_name
with table_list as
(select  /*+ materialize */ OBJECT_OWNER table_owner,OBJECT_NAME table_name
 from dba_hist_sql_plan
where  sql_id ='&sql_id' and object_type like 'TABLE%'
union
select  TABLE_OWNER,TABLE_NAME table_name
 from dba_hist_sql_plan sql,dba_indexes i
where  sql_id ='&sql_id' and object_type like 'INDEX%'
and i.owner = sql.object_owner and i.index_name = sql.object_name
),t as (select /*+ materialize */ owner,t.table_name,num_rows,last_analyzed ,blocks
from dba_tables t, table_list p
where t.owner = p.table_owner  and t.table_name = p.table_name
) ,
ic as (select /*+ materialize */ ic.table_owner,ic.table_name,column_name,index_owner,index_name,column_position  
from dba_ind_columns ic, table_list p
where ic.table_owner = p.table_owner  and ic.table_name = p.table_name
) ,
tc as (select  /*+ materialize */table_owner,tc.table_name,column_name,num_distinct,num_nulls
from dba_tab_columns tc, table_list p
where tc.owner = p.table_owner  and tc.table_name = p.table_name
union all
select table_owner,table_name,column_name,null,null
from ic
where column_name like 'SYS_NC%'
) ,
i as (select  /*+ materialize */i.table_owner,i.table_name,index_name,owner,distinct_keys,leaf_blocks
from dba_indexes i, table_list p
where i.table_owner = p.table_owner  and i.table_name = p.table_name
)
select /*+ordered use_hash(t tc)*/
owner||'.'||table_name   table_name   ,
blocks   tBlk       ,
num_rows  rRow      ,
index_name      ,
leaf_blocks iBlk    ,
distinct_keys iKey   ,
column_position pos ,
column_name     ,
num_distinct cKey   ,
num_nulls    cnull   ,
last_analyzed   
from
(
select  /*+ordered use_hash(t tc)*/ 
t.owner,
t.table_name ,
t.num_rows,
t.blocks,
i.index_name ,
i.distinct_keys ,
i.leaf_blocks,
ic.column_position,
tc.column_name,
tc.num_distinct ,
tc.num_nulls ,
t.last_analyzed
 from   t ,  tc ,  ic,  i
 where tc.table_owner = t.owner and tc.table_name = t.table_name
 and ic.table_owner(+) = tc.table_owner and ic.table_name(+) = tc.table_name and ic.column_name(+)=tc.column_name
 and i.table_owner(+) = ic.table_owner and i.table_name(+) = ic.table_name and i.index_name(+)=ic.index_name
 order by t.owner,t.table_name,i.index_name,column_position,num_distinct desc
)
/