111 lines
4.1 KiB
SQL
111 lines
4.1 KiB
SQL
@@header
|
|
|
|
/*
|
|
*
|
|
* Author : Vishal Gupta
|
|
* Purpose : Display PGA statistics
|
|
* Parameter : NONE
|
|
*
|
|
* Revision History:
|
|
* ===================
|
|
* Date Author Description
|
|
* --------- ------------ ------------------------------------------------------------------------
|
|
* 06-Jun-13 Vishal Gupta Modified AllInst calculations, ordered the statistics in logical order
|
|
* 27-Jun-12 Vishal Gupta Created
|
|
*
|
|
*/
|
|
|
|
|
|
/************************************
|
|
* INPUT PARAMETERS
|
|
************************************/
|
|
|
|
/************************************
|
|
* CONFIGURATION PARAMETERS
|
|
************************************/
|
|
|
|
DEFINE COUNT_FORMAT=999,999,999
|
|
--DEFINE COUNT_DIVIDER="1"
|
|
--DEFINE COUNT_HEADING="#"
|
|
DEFINE COUNT_DIVIDER="1000"
|
|
DEFINE COUNT_HEADING="#1000"
|
|
|
|
DEFINE BYTES_FORMAT="999,999"
|
|
--DEFINE BYTES_DIVIDER="1024"
|
|
--DEFINE BYTES_HEADING="KB"
|
|
--DEFINE BYTES_DIVIDER="1024/1024"
|
|
--DEFINE BYTES_HEADING="MB"
|
|
DEFINE BYTES_DIVIDER="1024/1024/1024"
|
|
DEFINE BYTES_HEADING="GB"
|
|
|
|
DEFINE TIME_FORMAT=9,999
|
|
DEFINE TIME_DIVIDER="60"
|
|
DEFINE TIME_HEADING="min"
|
|
|
|
|
|
|
|
|
|
|
|
PROMPT
|
|
PROMPT *************************
|
|
PROMPT * PGA Statistics
|
|
PROMPT *************************
|
|
|
|
|
|
|
|
COLUMN name HEADING "Name" FORMAT a40
|
|
COLUMN unit HEADING "Unit" FORMAT a10
|
|
COLUMN all_inst_value HEADING "AllInst" FORMAT &&COUNT_FORMAT
|
|
COLUMN inst1_value HEADING "Inst1" FORMAT &&COUNT_FORMAT
|
|
COLUMN inst2_value HEADING "Inst2" FORMAT &&COUNT_FORMAT
|
|
COLUMN inst3_value HEADING "Inst3" FORMAT &&COUNT_FORMAT
|
|
COLUMN inst4_value HEADING "Inst4" FORMAT &&COUNT_FORMAT
|
|
COLUMN inst5_value HEADING "Inst5" FORMAT &&COUNT_FORMAT
|
|
COLUMN inst6_value HEADING "Inst6" FORMAT &&COUNT_FORMAT
|
|
COLUMN inst7_value HEADING "Inst7" FORMAT &&COUNT_FORMAT
|
|
COLUMN inst8_value HEADING "Inst8" FORMAT &&COUNT_FORMAT
|
|
|
|
SELECT p.name
|
|
, DECODE(p.unit, 'bytes','&&BYTES_HEADING', p.unit) unit
|
|
, DECODE(p.unit
|
|
,'bytes', SUM(p.value)/&&BYTES_DIVIDER
|
|
,'percent', AVG(p.value)
|
|
,SUM(p.value)
|
|
) all_inst_value
|
|
, SUM(DECODE(p.inst_id, 1, DECODE(p.unit,'bytes',p.value/&&BYTES_DIVIDER,p.value))) inst1_value
|
|
, SUM(DECODE(p.inst_id, 2, DECODE(p.unit,'bytes',p.value/&&BYTES_DIVIDER,p.value))) inst2_value
|
|
, SUM(DECODE(p.inst_id, 3, DECODE(p.unit,'bytes',p.value/&&BYTES_DIVIDER,p.value))) inst3_value
|
|
, SUM(DECODE(p.inst_id, 4, DECODE(p.unit,'bytes',p.value/&&BYTES_DIVIDER,p.value))) inst4_value
|
|
, SUM(DECODE(p.inst_id, 5, DECODE(p.unit,'bytes',p.value/&&BYTES_DIVIDER,p.value))) inst5_value
|
|
, SUM(DECODE(p.inst_id, 6, DECODE(p.unit,'bytes',p.value/&&BYTES_DIVIDER,p.value))) inst6_value
|
|
, SUM(DECODE(p.inst_id, 7, DECODE(p.unit,'bytes',p.value/&&BYTES_DIVIDER,p.value))) inst7_value
|
|
, SUM(DECODE(p.inst_id, 8, DECODE(p.unit,'bytes',p.value/&&BYTES_DIVIDER,p.value))) inst8_value
|
|
FROM gv$pgastat p
|
|
GROUP BY p.name
|
|
, p.unit
|
|
ORDER BY DECODE(p.name
|
|
,'aggregate PGA target parameter',1
|
|
,'aggregate PGA auto target',2
|
|
,'cache hit percentage',3
|
|
,'',4
|
|
,'total PGA allocated',5
|
|
,'total PGA inuse',6
|
|
,'total freeable PGA memory',7
|
|
,'total PGA used for auto workareas',8
|
|
,'total PGA used for manual workareas',9
|
|
,'',10
|
|
,'max processes count',11
|
|
,'process count',12
|
|
,'maximum PGA allocated',13
|
|
,'maximum PGA used for auto workareas',14
|
|
,'maximum PGA used for manual workareas',15
|
|
,'over allocation count',16
|
|
,'bytes processed',17
|
|
,'extra bytes read/written',18
|
|
,'PGA memory freed back to OS',19
|
|
,99)
|
|
;
|
|
|
|
|
|
@@footer
|