sql_id/plan_object

set lin 450
col OWNER for a15
col object for a30
col LAST_ANALYZED for a20
col DEGREE for a15

select 'IDX' TYPE, index_name OBJECT ,owner, num_rows , to_char(last_analyzed,'DD-MON-YYYY hh24:mi:ss') Last_Analyzed, clustering_factor "CF/Blks", ini_trans , ltrim(rtrim(degree )) degree, blevel, (
select round(bytes/1024/1024)
from dba_segments b
where segment_type='INDEX' and segment_name = a.index_name and b.owner=a.owner ) size_MB , 0.0 frag, PCT_DIRECT_ACCESS IOT_PCT
from dba_indexes a
where (index_name,owner) in (
select object_name, object_owner
from gv$sql_plan_statistics_all
where sql_id = '&&sql_id' and plan_hash_value ='&&plan_hash_value'
--and child_number =        151
--and inst_id = ' 1'
and object# is not null and substr(object_type,1,5) ='INDEX' ) union ALL
select 'TBL' TYPE, table_name OBJECT ,owner, num_rows , to_char(last_analyzed,'DD-MON-YYYY hh24:mi:ss') Last_Analyzed , blocks, ini_trans, decode(degree,'DEFAULT','1',ltrim(rtrim(degree))) degree,0, (
select round(sum(bytes)/1024/1024)
from dba_segments b
where segment_name = a.table_name and b.owner=a.owner ) size_MB , 0.0, 0
from dba_tables a
where (table_name,owner) in (
select object_name, object_owner
from gv$sql_plan_statistics_all
where sql_id = '&&sql_id' and plan_hash_value ='&&plan_hash_value' and object_type in ( 'TABLE' ,'TABLE PARTITION')
-- and child_Number =' 151' and inst_id = ' 1'
)