144 lines
7.9 KiB
SQL
144 lines
7.9 KiB
SQL
@@header
|
|
|
|
/*
|
|
*
|
|
* Author : Vishal Gupta
|
|
* Purpose : Display Service Waits from AWR
|
|
* Parameters : 1 - SERVICE_NAME (Use % as wildcard, escape with \)
|
|
* 2 - Instance (Use % for all instances in the RAC database)
|
|
* 3 - From timestamp (YYYY-MM-DD HH24:MI:SS format)
|
|
* 4 - To timstamp (YYYY-MM-DD HH24:MI:SS format)
|
|
*
|
|
* Wait Classes are as follows:-
|
|
* Concurrency
|
|
* User I/O
|
|
* System I/O
|
|
* Administrative
|
|
* Other
|
|
* Configuration
|
|
* Scheduler
|
|
* Cluster
|
|
* Application
|
|
* Queueing
|
|
* Idle
|
|
* Network
|
|
* Commit
|
|
*
|
|
*
|
|
* Revision History:
|
|
* ===================
|
|
* Date Author Description
|
|
* --------- ------------ -----------------------------------------
|
|
* 17-May-12 Vishal Gupta Intial version
|
|
*
|
|
*/
|
|
|
|
|
|
DEFINE service_name="&&1"
|
|
DEFINE instance_number="&2"
|
|
DEFINE from_timestamp="&3"
|
|
DEFINE to_timestamp="&4"
|
|
|
|
DEFINE COUNT_FORMAT=999,999
|
|
--DEFINE COUNT_DIVIDER="1"
|
|
--DEFINE COUNT_HEADING="(#)"
|
|
DEFINE COUNT_DIVIDER="1000"
|
|
DEFINE COUNT_HEADING="#1000"
|
|
|
|
DEFINE TIME_FORMAT=999,999
|
|
DEFINE TIME_DIVIDER="1"
|
|
DEFINE TIME_HEADING="sec"
|
|
--DEFINE TIME_DIVIDER="1000"
|
|
--DEFINE TIME_HEADING="1000s"
|
|
|
|
|
|
DEFINE TOTAL_FORMAT=999,999,999,999
|
|
DEFINE TOTAL_DIVIDER="1000"
|
|
DEFINE TOTAL_HEADING="'1000"
|
|
|
|
COLUMN end_interval_time HEADING "SnapTime" FORMAT a15
|
|
COLUMN service_name HEADING "ServiceName" FORMAT a25
|
|
|
|
COLUMN Concurrency_total_waits HEADING "Concurr|Total|Waits|(&&COUNT_HEADING)" FORMAT &&COUNT_FORMAT
|
|
COLUMN user_io_total_waits HEADING "UserIO|Total|Waits|(&&COUNT_HEADING)" FORMAT &&COUNT_FORMAT
|
|
COLUMN system_io_total_waits HEADING "SystemIO|Total|Waits|(&&COUNT_HEADING)" FORMAT &&COUNT_FORMAT
|
|
COLUMN application_total_waits HEADING "App|Total|Waits|(&&COUNT_HEADING)" FORMAT &&COUNT_FORMAT
|
|
COLUMN network_total_waits HEADING "Network|Total|Waits|(&&COUNT_HEADING)" FORMAT &&COUNT_FORMAT
|
|
COLUMN commit_total_waits HEADING "Commit|Total|Waits|(&&COUNT_HEADING)" FORMAT &&COUNT_FORMAT
|
|
COLUMN idle_total_waits HEADING "Idle|Total|Waits|(&&COUNT_HEADING)" FORMAT &&COUNT_FORMAT
|
|
COLUMN cluster_total_waits HEADING "Cluster|Total|Waits|(&&COUNT_HEADING)" FORMAT &&COUNT_FORMAT
|
|
COLUMN scheduler_total_waits HEADING "Scheduler|Total|Waits|(&&COUNT_HEADING)" FORMAT &&COUNT_FORMAT
|
|
COLUMN Concurrency_time_waited HEADING "Concurr|Timel|Waited|(&&TIME_HEADING)" FORMAT &&TIME_FORMAT
|
|
COLUMN user_io_time_waited HEADING "UserIO|Timel|Waited|(&&TIME_HEADING)" FORMAT &&TIME_FORMAT
|
|
COLUMN system_io_time_waited HEADING "SystemIO|Timel|Waited|(&&TIME_HEADING)" FORMAT &&TIME_FORMAT
|
|
COLUMN application_time_waited HEADING "App|Timel|Waited|(&&TIME_HEADING)" FORMAT &&TIME_FORMAT
|
|
COLUMN network_time_waited HEADING "Network|Timel|Waited|(&&TIME_HEADING)" FORMAT &&TIME_FORMAT
|
|
COLUMN commit_time_waited HEADING "Commit|Timel|Waited|(&&TIME_HEADING)" FORMAT &&TIME_FORMAT
|
|
COLUMN idle_time_waited HEADING "Idle|Timel|Waited|(&&TIME_HEADING)" FORMAT &&TIME_FORMAT
|
|
COLUMN cluster_time_waited HEADING "Cluster|Timel|Waited|(&&TIME_HEADING)" FORMAT &&TIME_FORMAT
|
|
COLUMN scheduler_time_waited HEADING "Scheduler|Timel|Waited|(&&TIME_HEADING)" FORMAT &&TIME_FORMAT
|
|
|
|
|
|
|
|
WITH snap as
|
|
(
|
|
SELECT s.dbid
|
|
, s.instance_number
|
|
, s.snap_id
|
|
, LAG (s.snap_id) OVER(PARTITION BY s.dbid, s.instance_number ORDER BY s.dbid, s.instance_number, s.snap_id) prev_snap_id
|
|
, s.end_interval_time
|
|
FROM dba_hist_snapshot s
|
|
WHERE 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
|
|
, swc.service_name
|
|
-- Total Waits in centisecs
|
|
, SUM(DECODE(swc.wait_class, 'Concurrency', (swc.total_waits-LEAST(swc_prev.total_waits,swc.total_waits)), 0))/&&COUNT_DIVIDER Concurrency_total_waits
|
|
, SUM(DECODE(swc.wait_class, 'User I/O', (swc.total_waits-LEAST(swc_prev.total_waits,swc.total_waits)), 0))/&&COUNT_DIVIDER user_io_total_waits
|
|
, SUM(DECODE(swc.wait_class, 'System I/O', (swc.total_waits-LEAST(swc_prev.total_waits,swc.total_waits)), 0))/&&COUNT_DIVIDER system_io_total_waits
|
|
, SUM(DECODE(swc.wait_class, 'Application', (swc.total_waits-LEAST(swc_prev.total_waits,swc.total_waits)), 0))/&&COUNT_DIVIDER application_total_waits
|
|
, SUM(DECODE(swc.wait_class, 'Network', (swc.total_waits-LEAST(swc_prev.total_waits,swc.total_waits)), 0))/&&COUNT_DIVIDER network_total_waits
|
|
, SUM(DECODE(swc.wait_class, 'Commit', (swc.total_waits-LEAST(swc_prev.total_waits,swc.total_waits)), 0))/&&COUNT_DIVIDER commit_total_waits
|
|
, SUM(DECODE(swc.wait_class, 'Idle', (swc.total_waits-LEAST(swc_prev.total_waits,swc.total_waits)), 0))/&&COUNT_DIVIDER idle_total_waits
|
|
, SUM(DECODE(swc.wait_class, 'Cluster', (swc.total_waits-LEAST(swc_prev.total_waits,swc.total_waits)), 0))/&&COUNT_DIVIDER cluster_total_waits
|
|
, SUM(DECODE(swc.wait_class, 'Scheduler', (swc.total_waits-LEAST(swc_prev.total_waits,swc.total_waits)), 0))/&&COUNT_DIVIDER scheduler_total_waits
|
|
-- Time Waited in centisecs
|
|
, SUM(DECODE(swc.wait_class, 'Concurrency', (swc.time_waited-LEAST(swc_prev.time_waited,swc.time_waited)), 0))/100/&&TIME_DIVIDER Concurrency_time_waited
|
|
, SUM(DECODE(swc.wait_class, 'User I/O', (swc.time_waited-LEAST(swc_prev.time_waited,swc.time_waited)), 0))/100/&&TIME_DIVIDER user_io_time_waited
|
|
, SUM(DECODE(swc.wait_class, 'System I/O', (swc.time_waited-LEAST(swc_prev.time_waited,swc.time_waited)), 0))/100/&&TIME_DIVIDER system_io_time_waited
|
|
, SUM(DECODE(swc.wait_class, 'Application', (swc.time_waited-LEAST(swc_prev.time_waited,swc.time_waited)), 0))/100/&&TIME_DIVIDER application_time_waited
|
|
, SUM(DECODE(swc.wait_class, 'Network', (swc.time_waited-LEAST(swc_prev.time_waited,swc.time_waited)), 0))/100/&&TIME_DIVIDER network_time_waited
|
|
, SUM(DECODE(swc.wait_class, 'Commit', (swc.time_waited-LEAST(swc_prev.time_waited,swc.time_waited)), 0))/100/&&TIME_DIVIDER commit_time_waited
|
|
, SUM(DECODE(swc.wait_class, 'Idle', (swc.time_waited-LEAST(swc_prev.time_waited,swc.time_waited)), 0))/100/&&TIME_DIVIDER idle_time_waited
|
|
, SUM(DECODE(swc.wait_class, 'Cluster', (swc.time_waited-LEAST(swc_prev.time_waited,swc.time_waited)), 0))/100/&&TIME_DIVIDER cluster_time_waited
|
|
, SUM(DECODE(swc.wait_class, 'Scheduler', (swc.time_waited-LEAST(swc_prev.time_waited,swc.time_waited)), 0))/100/&&TIME_DIVIDER scheduler_time_waited
|
|
FROM snap s
|
|
, dba_hist_service_wait_class swc
|
|
, dba_hist_service_wait_class swc_prev
|
|
WHERE s.dbid = swc.dbid
|
|
AND s.instance_number = swc.instance_number
|
|
AND s.snap_id = swc.snap_id
|
|
AND s.dbid = swc_prev.dbid
|
|
AND s.instance_number = swc_prev.instance_number
|
|
AND s.prev_snap_id = swc_prev.snap_id
|
|
AND swc.service_name = swc_prev.service_name
|
|
AND swc.wait_class = swc_prev.wait_class
|
|
AND UPPER(swc.service_name) LIKE UPPER('&&service_name') ESCAPE '\'
|
|
AND swc.instance_number LIKE '&&instance_number'
|
|
GROUP BY TO_CHAR(s.end_interval_time,'DD-MON-YY HH24:MI')
|
|
, swc.service_name
|
|
ORDER BY TO_CHAR(s.end_interval_time,'DD-MON-YY HH24:MI')
|
|
, swc.service_name
|
|
;
|
|
|
|
PROMPT '&&from_timestamp'
|
|
|
|
UNDEFINE service_name
|
|
UNDEFINE instance_number
|
|
UNDEFINE from_timestamp
|
|
UNDEFINE to_timestamp
|
|
|
|
|
|
@@footer
|