88 lines
2.9 KiB
SQL
88 lines
2.9 KiB
SQL
@@header
|
|
|
|
PROMPT
|
|
PROMPT ####################################################################################
|
|
PROMPT NOT COMPLETED YET
|
|
PROMPT ####################################################################################
|
|
|
|
|
|
/*
|
|
*
|
|
* Author : Vishal Gupta
|
|
* Purpose : Display AWR Statistics values
|
|
* 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)
|
|
* 3 - Statistic Name (Use % as wildcard, \ as escape)
|
|
*
|
|
* dbid AND instancenumber are optional.
|
|
* If not passes then it takes current instance's dbid AND instancenumber
|
|
*
|
|
* Revision History:
|
|
* ===================
|
|
* Date Author Description
|
|
* --------- ------------ -----------------------------------------
|
|
* 04-Apr-12 Vishal Gupta Created
|
|
*
|
|
*/
|
|
|
|
|
|
DEFINE INST_ID="&&1"
|
|
DEFINE FROM_TIMESTAMP="&2"
|
|
DEFINE TO_TIMESTAMP="&3"
|
|
DEFINE statname="&4"
|
|
|
|
set pages 100
|
|
|
|
|
|
/************************************
|
|
* CONFIGURATION PARAMETERS
|
|
************************************/
|
|
|
|
DEFINE COUNT_FORMAT=9,999,999
|
|
--DEFINE COUNT_DIVIDER="1"
|
|
--DEFINE COUNT_HEADING="#"
|
|
DEFINE COUNT_DIVIDER="1000"
|
|
DEFINE COUNT_HEADING="#1000"
|
|
|
|
|
|
|
|
PROMPT
|
|
PROMPT ####################################################################################
|
|
PROMPT # S Y S T E M S T A T I S T I C S (From AWR Repository)
|
|
PROMPT #
|
|
PROMPT # - For Instance ID - &INST_ID
|
|
PROMPT # - Between &&FROM_TIMESTAMP AND &&TO_TIMESTAMP (YYYY-MM-DD HH24:MI:SS)
|
|
PROMPT # - Statistic - &&statname
|
|
PROMPT #
|
|
PROMPT ####################################################################################
|
|
|
|
|
|
COLUMN end_interval_time HEADING "Snap Time" FORMAT a18
|
|
COLUMN stat_name HEADING "Statistic Name" FORMAT a30
|
|
COLUMN value HEADING "Statistic Value" FORMAT 999,999,999,999,999,999,999
|
|
|
|
SELECT MAX(TO_CHAR(s.end_interval_time,'DD-MON-YY hh24')) end_interval_time
|
|
, ss.stat_name
|
|
, SUM(ss.value) value
|
|
FROM dba_hist_snapshot s
|
|
, dba_hist_sysstat ss
|
|
, v$database d
|
|
where s.dbid = d.dbid
|
|
AND s.dbid = ss.dbid
|
|
AND s.instance_number = ss.instance_number
|
|
AND s.snap_id = ss.snap_id
|
|
AND upper(ss.stat_name) LIKE UPPER('&&statname') ESCAPE '\'
|
|
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')
|
|
GROUP BY s.dbid, TO_CHAR(s.end_interval_time,'DD-MON-YY hh24'), ss.stat_name
|
|
ORDER BY TO_DATE(MAX(TO_CHAR(s.end_interval_time,'DD-MON-YY hh24')),'DD-Mon-YY hh24') asc
|
|
/
|
|
|
|
UNDEFINE INST_ID
|
|
UNDEFINE FROM_TIMESTAMP
|
|
UNDEFINE FROM_TIMESTAMP
|
|
|
|
|
|
@@footer |