cache

 select inst_id, namespace, status, name, count(*) noofresults, round(avg(scan_count)) avg_scan_cnt, round(max(scan_count)) max_scan_cnt, round(sum(scan_count)) tot_blk_cnt
from gv$result_cache_objects
where type ='Result' group by inst_id,namespace, name, status order by tot_blk_cnt

 

 

select name cacheName, count(*), status
from V$RESULT_CACHE_OBJECTS group by name, status order by 2