187 lines
7.3 KiB
SQL
187 lines
7.3 KiB
SQL
@@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
|