shparam

col TIMEO for a15
col db_name for a15
WITH shparam as (
select snap_id, instance_number inst, value/(1048576) shpsize
from dba_hist_parameter
where parameter_name ='shared_pool_size' and snap_id between &&begin_snap_id and &&end_snap_id )
select sys_context('userenv','DB_NAME') ||decode( 0,1,'-'||a.inst) db_name , a.snap_id, to_char(end_interval_time,'DD-MON-YY HH24:MI') Timeo, max(shpsize) SHPPARAM, sum(Mbytes) SHPUSED ,
---        max(decode(shcomponent,0,MBytes)) FreeM,
round(max(decode(shcomponent,0,MBytes)) /sum(Mbytes) * 100) "Free%", max(decode(shcomponent,1,MBytes)) SQLA, max(decode(shcomponent,2,MBytes)) KGLH0, max(decode(shcomponent,3,MBytes)) KGLHD, max(decode(shcomponent,4,MBytes)) KGLDA, max(decode(shcomponent,5,MBytes)) KKSSP, max(decode(shcomponent,6,MBytes)) "GC Rsrc",
--        max(decode(shcomponent,7,MBytes)) "PLDIA",
--max(decode(shcomponent,8,MBytes)) "PLMCD",
max(decode(shcomponent,9,MBytes)) "dbHashBucket", max(decode(shcomponent,99,MBytes)) Others
from (
select snap_id, instance_number inst, round(sum(bytes)/(1048576)) MBytes, decode(name,'free memory',0,'SQLA',1,'sql area',1,'KGLH0',2,'KGLHD',3,'KGLDA',4,'KKSSP',5,decode(substr(name,1,1),'g',name),6, 'PLDIA',7,'PLMCD',8,'db_block_hash_buckets',9,99) shcomponent
from
--sys.wrh$_sgastat
dba_hist_sgastat
where snap_id between &&begin_snap_id and &&end_snap_id and pool ='shared pool'
--and bytes > ( 100 * 1048576)
group by instance_number,snap_id, decode(name,'free memory',0,'SQLA',1,'sql area',1,'KGLH0',2,'KGLHD',3,'KGLDA',4,'KKSSP',5,decode(substr(name,1,1),'g',name),6, 'PLDIA',7,'PLMCD',8,'db_block_hash_buckets',9,99) order by instance_number,2 ) a, dba_hist_snapshot b, shparam c
where a.snap_id = b.snap_id and a.snap_id = c.snap_id and a.inst = b.instance_number and a. inst = c.inst group by a.snap_id, a.inst,to_char(end_interval_time,'DD-MON-YY HH24:MI') order by a.inst, snap_id