@@header set term off /* * * Author : Vishal Gupta * Purpose : Display Results Cache Statistics * * Revision History: * =================== * Date Author Description * --------- ------------ ----------------------------------------- * 02-JUL-14 Vishal Gupta Created * */ set term on undefine _IF_INST1_EXISTS undefine _IF_INST2_EXISTS undefine _IF_INST3_EXISTS undefine _IF_INST4_EXISTS undefine _IF_INST5_EXISTS undefine _IF_INST6_EXISTS undefine _IF_INST7_EXISTS undefine _IF_INST8_EXISTS undefine _IF_INST9_EXISTS undefine _IF_INST10_EXISTS undefine _IF_INST11_EXISTS undefine _IF_INST12_EXISTS undefine _IF_INST13_EXISTS undefine _IF_INST14_EXISTS undefine _IF_INST15_EXISTS undefine _IF_INST16_EXISTS define _IF_INST1_EXISTS="--" define _IF_INST2_EXISTS="--" define _IF_INST3_EXISTS="--" define _IF_INST4_EXISTS="--" define _IF_INST5_EXISTS="--" define _IF_INST6_EXISTS="--" define _IF_INST7_EXISTS="--" define _IF_INST8_EXISTS="--" define _IF_INST9_EXISTS="--" define _IF_INST10_EXISTS="--" define _IF_INST11_EXISTS="--" define _IF_INST12_EXISTS="--" define _IF_INST13_EXISTS="--" define _IF_INST14_EXISTS="--" define _IF_INST15_EXISTS="--" define _IF_INST16_EXISTS="--" col INST1_EXISTS new_value _IF_INST1_EXISTS noprint col INST2_EXISTS new_value _IF_INST2_EXISTS noprint col INST3_EXISTS new_value _IF_INST3_EXISTS noprint col INST4_EXISTS new_value _IF_INST4_EXISTS noprint col INST5_EXISTS new_value _IF_INST5_EXISTS noprint col INST6_EXISTS new_value _IF_INST6_EXISTS noprint col INST7_EXISTS new_value _IF_INST7_EXISTS noprint col INST8_EXISTS new_value _IF_INST8_EXISTS noprint col INST9_EXISTS new_value _IF_INST9_EXISTS noprint col INST10_EXISTS new_value _IF_INST10_EXISTS noprint col INST11_EXISTS new_value _IF_INST11_EXISTS noprint col INST12_EXISTS new_value _IF_INST12_EXISTS noprint col INST13_EXISTS new_value _IF_INST13_EXISTS noprint col INST14_EXISTS new_value _IF_INST14_EXISTS noprint col INST15_EXISTS new_value _IF_INST15_EXISTS noprint col INST16_EXISTS new_value _IF_INST16_EXISTS noprint set term off SELECT MIN(DECODE(inst_id,1,' ', '--')) INST1_EXISTS , MIN(DECODE(inst_id,2,' ', '--')) INST2_EXISTS , MIN(DECODE(inst_id,3,' ', '--')) INST3_EXISTS , MIN(DECODE(inst_id,4,' ', '--')) INST4_EXISTS , MIN(DECODE(inst_id,5,' ', '--')) INST5_EXISTS , MIN(DECODE(inst_id,6,' ', '--')) INST6_EXISTS , MIN(DECODE(inst_id,7,' ', '--')) INST7_EXISTS , MIN(DECODE(inst_id,8,' ', '--')) INST8_EXISTS , MIN(DECODE(inst_id,9,' ', '--')) INST9_EXISTS , MIN(DECODE(inst_id,10,' ', '--')) INST10_EXISTS , MIN(DECODE(inst_id,11,' ', '--')) INST11_EXISTS , MIN(DECODE(inst_id,12,' ', '--')) INST12_EXISTS , MIN(DECODE(inst_id,13,' ', '--')) INST13_EXISTS , MIN(DECODE(inst_id,14,' ', '--')) INST14_EXISTS , MIN(DECODE(inst_id,15,' ', '--')) INST15_EXISTS , MIN(DECODE(inst_id,16,' ', '--')) INST16_EXISTS FROM gv$instance GROUP BY version ; set term on PROMPT PROMPT #################################### PROMPT # RESULTS CACHE STATISTICS # PROMPT #################################### PROMPT COLUMN end_column HEADING " " FORMAT a1 COLUMN name HEADING "StatName" FORMAT a28 COLUMN value_inst1 HEADING "Inst1" FORMAT a10 COLUMN value_inst2 HEADING "Inst2" FORMAT a10 COLUMN value_inst3 HEADING "Inst3" FORMAT a10 COLUMN value_inst4 HEADING "Inst4" FORMAT a10 COLUMN value_inst5 HEADING "Inst5" FORMAT a10 COLUMN value_inst6 HEADING "Inst6" FORMAT a10 COLUMN value_inst7 HEADING "Inst7" FORMAT a10 COLUMN value_inst8 HEADING "Inst8" FORMAT a10 COLUMN value_inst9 HEADING "Inst9" FORMAT a10 COLUMN value_inst10 HEADING "Inst10" FORMAT a10 COLUMN value_inst11 HEADING "Inst11" FORMAT a10 COLUMN value_inst12 HEADING "Inst12" FORMAT a10 COLUMN value_inst13 HEADING "Inst13" FORMAT a10 COLUMN value_inst14 HEADING "Inst14" FORMAT a10 COLUMN value_inst15 HEADING "Inst15" FORMAT a10 COLUMN value_inst16 HEADING "Inst16" FORMAT a10 SELECT r.sid , r.inst_id FROM gv$session_client_result_cache r JOIN gv$session s ON s.inst_id = r.inst_id AND s.sid = r.sid AND s.serial# = r.serial# WHERE 1=1 -- AND s.sid is null ; /* SELECT rcs.name &&_IF_INST1_EXISTS , MAX(DECODE(rcs.inst_id,1,rcs.value,NULL)) value_inst1 &&_IF_INST2_EXISTS , MAX(DECODE(rcs.inst_id,2,rcs.value,NULL)) value_inst2 &&_IF_INST3_EXISTS , MAX(DECODE(rcs.inst_id,3,rcs.value,NULL)) value_inst3 &&_IF_INST4_EXISTS , MAX(DECODE(rcs.inst_id,4,rcs.value,NULL)) value_inst4 &&_IF_INST5_EXISTS , MAX(DECODE(rcs.inst_id,5,rcs.value,NULL)) value_inst5 &&_IF_INST6_EXISTS , MAX(DECODE(rcs.inst_id,6,rcs.value,NULL)) value_inst6 &&_IF_INST7_EXISTS , MAX(DECODE(rcs.inst_id,7,rcs.value,NULL)) value_inst7 &&_IF_INST8_EXISTS , MAX(DECODE(rcs.inst_id,8,rcs.value,NULL)) value_inst8 &&_IF_INST9_EXISTS , MAX(DECODE(rcs.inst_id,8,rcs.value,NULL)) value_inst9 &&_IF_INST10_EXISTS , MAX(DECODE(rcs.inst_id,10,rcs.value,NULL)) value_inst10 &&_IF_INST11_EXISTS , MAX(DECODE(rcs.inst_id,11,rcs.value,NULL)) value_inst11 &&_IF_INST12_EXISTS , MAX(DECODE(rcs.inst_id,12,rcs.value,NULL)) value_inst12 &&_IF_INST13_EXISTS , MAX(DECODE(rcs.inst_id,13,rcs.value,NULL)) value_inst13 &&_IF_INST14_EXISTS , MAX(DECODE(rcs.inst_id,14,rcs.value,NULL)) value_inst14 &&_IF_INST15_EXISTS , MAX(DECODE(rcs.inst_id,15,rcs.value,NULL)) value_inst15 &&_IF_INST16_EXISTS , MAX(DECODE(rcs.inst_id,16,rcs.value,NULL)) value_inst16 FROM gv$result_cache_statistics rcs GROUP BY rcs.name ORDER BY DECODE(rcs.name ,'Block Size (Bytes)' ,1 ,'Block Count Current' ,2 ,'Block Count Maximum' ,3 ,'Result Size Maximum (Blocks)' ,4 ,'Find Count' ,5 ,'Find Copy Count' ,6 ,'Global Hit Count' ,7 ,'Global Miss Count' ,8 ,'Create Count Failure' ,9 ,'Invalidation Count' ,10 ,'Delete Count Valid' ,11 ,'Delete Count Invalid' ,12 ,'Hash Chain Length' ,13 ,'Latch (Share)' ,14 ,'Create Count Success' ,15 ,99) ; */ undefine _IF_INST1_EXISTS undefine _IF_INST2_EXISTS undefine _IF_INST3_EXISTS undefine _IF_INST4_EXISTS undefine _IF_INST5_EXISTS undefine _IF_INST6_EXISTS undefine _IF_INST7_EXISTS undefine _IF_INST8_EXISTS undefine _IF_INST9_EXISTS undefine _IF_INST10_EXISTS undefine _IF_INST11_EXISTS undefine _IF_INST12_EXISTS undefine _IF_INST13_EXISTS undefine _IF_INST14_EXISTS undefine _IF_INST15_EXISTS undefine _IF_INST16_EXISTS @@footer