137 lines
5.6 KiB
SQL
137 lines
5.6 KiB
SQL
@@header
|
|
|
|
/*
|
|
*
|
|
* Author : Vishal Gupta
|
|
* Purpose : Display PGA statistics
|
|
* Parameter : 1 - InstanceNumber (Use % for all instances)
|
|
* 2 - From Timestamp (In YYYY-MM-DD HH24:MI:SS format)
|
|
* 3 - To Timestamp (In YYYY-MM-DD HH24:MI:SS format)
|
|
* 4 - PGA Statistics Name (For exact name, use @pga_stats script to find exact name)
|
|
*
|
|
* Revision History:
|
|
* ===================
|
|
* Date Author Description
|
|
* --------- ------------ -----------------------------------------
|
|
* 27-Jun-12 Vishal Gupta Created
|
|
*
|
|
*/
|
|
|
|
|
|
/************************************
|
|
* INPUT PARAMETERS
|
|
************************************/
|
|
DEFINE INST_ID="&&1"
|
|
DEFINE FROM_TIMESTAMP="&2"
|
|
DEFINE TO_TIMESTAMP="&3"
|
|
DEFINE STAT_NAME="&4"
|
|
|
|
|
|
|
|
/************************************
|
|
* CONFIGURATION PARAMETERS
|
|
************************************/
|
|
|
|
set pages 50000
|
|
|
|
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"
|
|
|
|
|
|
|
|
|
|
|
|
|
|
PROMPT
|
|
PROMPT ***************************************************
|
|
PROMPT * P G A S T A T I S T I C S
|
|
PROMPT *
|
|
PROMPT * From AWR Repository (Delta Figures)
|
|
PROMPT * - For Instance ID - &INST_ID
|
|
PROMPT * - Between &&FROM_TIMESTAMP and &&TO_TIMESTAMP (YYYY-MM-DD HH24:MI:SS)
|
|
PROMPT * - Statistic Name - "&&STAT_NAME"
|
|
PROMPT ***************************************************
|
|
|
|
|
|
|
|
COLUMN end_interval_time HEADING "SnapTime" FORMAT a18
|
|
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
|
|
|
|
WITH snap
|
|
AS
|
|
(SELECT s.dbid
|
|
, s.instance_number
|
|
, s.snap_id
|
|
, LEAD (s.snap_id) OVER (ORDER BY s.dbid, s.instance_number, s.snap_id) next_snap_id
|
|
, s.end_interval_time
|
|
FROM dba_hist_snapshot s
|
|
, v$database d
|
|
WHERE s.dbid = d.dbid
|
|
AND s.instance_number LIKE '&&INST_ID'
|
|
AND s.end_interval_time BETWEEN TO_TIMESTAMP('&&FROM_TIMESTAMP','YYYY-MM-DD HH24:MI:SS')
|
|
AND TO_TIMESTAMP('&&TO_TIMESTAMP' ,'YYYY-MM-DD HH24:MI:SS')
|
|
)
|
|
SELECT TO_CHAR(s.end_interval_time,'DD-MON-YY HH24:MI') end_interval_time
|
|
, p.name
|
|
, DECODE(pn.unit
|
|
, 'bytes','&&BYTES_HEADING'
|
|
, pn.unit) unit
|
|
, SUM(DECODE(pn.unit,'bytes',(p.value - LEAST(p_prev.value,p.value))/&&BYTES_DIVIDER,(p.value - LEAST(p_prev.value,p.value)))) all_inst_value
|
|
, SUM(DECODE(p.instance_number, 1, DECODE(pn.unit,'bytes',(p.value - LEAST(p_prev.value,p.value))/&&BYTES_DIVIDER,(p.value - LEAST(p_prev.value,p.value))))) inst1_value
|
|
, SUM(DECODE(p.instance_number, 2, DECODE(pn.unit,'bytes',(p.value - LEAST(p_prev.value,p.value))/&&BYTES_DIVIDER,(p.value - LEAST(p_prev.value,p.value))))) inst2_value
|
|
, SUM(DECODE(p.instance_number, 3, DECODE(pn.unit,'bytes',(p.value - LEAST(p_prev.value,p.value))/&&BYTES_DIVIDER,(p.value - LEAST(p_prev.value,p.value))))) inst3_value
|
|
, SUM(DECODE(p.instance_number, 4, DECODE(pn.unit,'bytes',(p.value - LEAST(p_prev.value,p.value))/&&BYTES_DIVIDER,(p.value - LEAST(p_prev.value,p.value))))) inst4_value
|
|
, SUM(DECODE(p.instance_number, 5, DECODE(pn.unit,'bytes',(p.value - LEAST(p_prev.value,p.value))/&&BYTES_DIVIDER,(p.value - LEAST(p_prev.value,p.value))))) inst5_value
|
|
, SUM(DECODE(p.instance_number, 6, DECODE(pn.unit,'bytes',(p.value - LEAST(p_prev.value,p.value))/&&BYTES_DIVIDER,(p.value - LEAST(p_prev.value,p.value))))) inst6_value
|
|
, SUM(DECODE(p.instance_number, 7, DECODE(pn.unit,'bytes',(p.value - LEAST(p_prev.value,p.value))/&&BYTES_DIVIDER,(p.value - LEAST(p_prev.value,p.value))))) inst7_value
|
|
, SUM(DECODE(p.instance_number, 8, DECODE(pn.unit,'bytes',(p.value - LEAST(p_prev.value,p.value))/&&BYTES_DIVIDER,(p.value - LEAST(p_prev.value,p.value))))) inst8_value
|
|
FROM snap s
|
|
, dba_hist_pgastat p
|
|
, dba_hist_pgastat p_prev
|
|
, v$pgastat pn
|
|
WHERE p.dbid = s.dbid
|
|
AND p.instance_number = s.instance_number
|
|
AND p.snap_id = s.next_snap_id
|
|
AND p.name = pn.name
|
|
AND p_prev.dbid = s.dbid
|
|
AND p_prev.instance_number = s.instance_number
|
|
AND p_prev.snap_id = s.snap_id
|
|
AND p_prev.name = pn.name
|
|
AND p_prev.name = p.name
|
|
AND p.name = '&&STAT_NAME'
|
|
GROUP BY TO_CHAR(s.end_interval_time,'DD-MON-YY HH24:MI')
|
|
, p.name
|
|
, pn.unit
|
|
ORDER BY TO_DATE(TO_CHAR(s.end_interval_time,'DD-MON-YY HH24:MI'),'DD-MON-YY HH24:MI')
|
|
, p.name
|
|
, pn.unit
|
|
;
|
|
|
|
UNDEFINE INST_ID
|
|
UNDEFINE FROM_TIMESTAMP
|
|
UNDEFINE TO_TIMESTAMP
|
|
UNDEFINE STAT_NAME
|
|
|
|
@@footer
|