@@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