109 lines
4.1 KiB
SQL
109 lines
4.1 KiB
SQL
@@header
|
|
|
|
/*
|
|
*
|
|
* Author : Vishal Gupta
|
|
* Purpose : Display system metric per sec from AWR repository
|
|
* 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 - Statistics Name (Exact Name, use @statname script to find exact name)
|
|
*
|
|
* Revision History:
|
|
* ===================
|
|
* Date Author Description
|
|
* --------- ------------ -----------------------------------------
|
|
* 05-Aug-12 Vishal Gupta Created
|
|
*
|
|
*/
|
|
|
|
|
|
/************************************
|
|
* INPUT PARAMETERS
|
|
************************************/
|
|
DEFINE INST_ID="&&1"
|
|
DEFINE FROM_TIMESTAMP="&2"
|
|
DEFINE TO_TIMESTAMP="&3"
|
|
DEFINE STAT_NAME="&4"
|
|
|
|
/************************************
|
|
* CONFIGURATION PARAMETERS
|
|
************************************/
|
|
|
|
|
|
|
|
DEFINE FORMAT="9999"
|
|
DEFINE TOTAL_FORMAT="999,999"
|
|
|
|
--DEFINE HEADING="#"
|
|
--DEFINE DIVIDER="1"
|
|
--DEFINE HEADING="'1000s"
|
|
--DEFINE DIVIDER="1000"
|
|
|
|
--DEFINE HEADING="min"
|
|
--DEFINE DIVIDER="60/100"
|
|
--DEFINE HEADING="hr"
|
|
--DEFINE DIVIDER="60/60/100"
|
|
|
|
--DEFINE HEADING="KB"
|
|
--DEFINE DIVIDER="1024"
|
|
--DEFINE HEADING="MB"
|
|
--DEFINE DIVIDER="1024/1024"
|
|
DEFINE HEADING="GB"
|
|
DEFINE DIVIDER="1024/1024/1024"
|
|
|
|
DEFINE TIME_FORMAT="DD-MON-YY HH24:MI"
|
|
--DEFINE TIME_FORMAT="DD-MON-YY HH24"
|
|
|
|
PROMPT
|
|
PROMPT ####################################################################################
|
|
PROMPT #
|
|
PROMPT # S Y S T E M S T A T I S T I C S T R E N D
|
|
PROMPT #
|
|
PROMPT # From AWR Repository (Absolute figures , not delta)
|
|
PROMPT # - Statistic Name - "&&STAT_NAME"
|
|
PROMPT # - For Instance ID - &INST_ID
|
|
PROMPT # - Snap Between - &&FROM_TIMESTAMP and &&TO_TIMESTAMP (YYYY-MM-DD HH24:MI:SS)
|
|
PROMPT #
|
|
PROMPT # (All Figures are in &&HEADING)
|
|
PROMPT #
|
|
PROMPT ####################################################################################
|
|
|
|
COLUMN time HEADING "Time|&&TIME_FORMAT" FORMAT a18
|
|
COLUMN instance_number HEADING "I#" FORMAT 99
|
|
COLUMN average_active_session HEADING "Avg|Active|Session" FORMAT 9999999
|
|
COLUMN redo HEADING "Redo|PerSec" FORMAT 9999999
|
|
COLUMN logons HEADING "Logons|PerSec" FORMAT 9999999
|
|
COLUMN user_calls HEADING "UserCalls|PerSec" FORMAT 9999999
|
|
COLUMN executions HEADING "Exec|PerSec" FORMAT 9999999
|
|
COLUMN physical_reads HEADING "PhyReads|PerSec" FORMAT 9999999
|
|
COLUMN physical_writes HEADING "PhyWrites|PerSec" FORMAT 9999999
|
|
COLUMN network_traffic_volume HEADING "Network|Traffic|Bytes|PerSec" FORMAT 9999999
|
|
COLUMN user_transactions HEADING "Transactions|PerSec" FORMAT 9999999
|
|
|
|
SELECT TO_CHAR(m.end_time,'&&TIME_FORMAT') Time
|
|
, m.instance_number
|
|
, AVG(DECODE(m.metric_name,'Average Active Sessions',m.value,0)) average_active_session
|
|
, AVG(DECODE(m.metric_name,'Redo Generated Per Sec',m.value,0)) redo
|
|
, AVG(DECODE(m.metric_name,'Logons Per Sec',m.value,0)) logons
|
|
, AVG(DECODE(m.metric_name,'User Calls Per Sec',m.value,0)) user_calls
|
|
, AVG(DECODE(m.metric_name,'Executions Per Sec',m.value,0)) executions
|
|
, AVG(DECODE(m.metric_name,'Physical Reads Per Sec',m.value,0)) physical_reads
|
|
, AVG(DECODE(m.metric_name,'Physical Writes Per Sec',m.value,0)) physical_writes
|
|
, AVG(DECODE(m.metric_name,'Network Traffic Volume Per Sec',m.value,0)) network_traffic_volume
|
|
, AVG(DECODE(m.metric_name,'User Transaction Per Sec',m.value,0)) user_transactions
|
|
FROM dba_hist_sysmetric_history m
|
|
JOIN v$database d ON d.dbid = m.dbid
|
|
GROUP BY TO_CHAR(m.end_time,'&&TIME_FORMAT')
|
|
, m.instance_number
|
|
ORDER BY TO_DATE(TO_CHAR(m.end_time,'&&TIME_FORMAT'),'&&TIME_FORMAT')
|
|
;
|
|
|
|
UNDEFINE INST_ID
|
|
UNDEFINE FROM_TIMESTAMP
|
|
UNDEFINE FROM_TIMESTAMP
|
|
UNDEFINE STAT_NAME
|
|
|
|
|
|
@@footer
|