118 lines
4.0 KiB
SQL
118 lines
4.0 KiB
SQL
@@header
|
|
|
|
/*
|
|
*
|
|
* Author : Vishal Gupta
|
|
* Purpose : Display top activity from ASH
|
|
* Parameters : 1 - Number of MINUTES (Default 5min)
|
|
* 2 - Top Row Count (Default 5 rows)
|
|
* 3 - INST_ID (Default %)
|
|
* 3 - TO_MINUTES (Default 0min)
|
|
*
|
|
* Revision History:
|
|
* ===================
|
|
* Date Author Description
|
|
* --------- ------------ -----------------------------------------
|
|
* 02-Apr-13 Vishal Gupta Created
|
|
*
|
|
*/
|
|
|
|
|
|
/************************************
|
|
* INPUT PARAMETERS
|
|
************************************/
|
|
UNDEFINE FROM_MINUTES
|
|
UNDEFINE TOPROWCOUNT
|
|
UNDEFINE INST_ID
|
|
UNDEFINE TO_MINUTES
|
|
|
|
DEFINE FROM_MINUTES="&&1"
|
|
DEFINE TOPROWCOUNT="&&2"
|
|
DEFINE INST_ID='&&3'
|
|
DEFINE TO_MINUTES="&&4"
|
|
|
|
|
|
set term off
|
|
COLUMN _FROM_MINUTES NEW_VALUE FROM_MINUTES NOPRINT
|
|
COLUMN _TO_MINUTES NEW_VALUE TO_MINUTES NOPRINT
|
|
COLUMN _TOPROWCOUNT NEW_VALUE TOPROWCOUNT NOPRINT
|
|
COLUMN _INST_ID NEW_VALUE INST_ID NOPRINT
|
|
|
|
SELECT DECODE('&&FROM_MINUTES','','5','&&FROM_MINUTES') "_FROM_MINUTES"
|
|
, DECODE('&&TO_MINUTES','','0','&&TO_MINUTES') "_TO_MINUTES"
|
|
, DECODE('&&TOPROWCOUNT','','5','&&TOPROWCOUNT') "_TOPROWCOUNT"
|
|
, DECODE('&&INST_ID','','%','&&INST_ID') "_INST_ID"
|
|
from dual;
|
|
|
|
set term on
|
|
|
|
PROMPT *****************************************************************
|
|
PROMPT * ASH - Top &&TOPROWCOUNT Active Services (Over Last &&FROM_MINUTES min)
|
|
PROMPT *
|
|
PROMPT * Input Parameters
|
|
PROMPT * - Minutes = '&&FROM_MINUTES'
|
|
PROMPT * - Top Row Count = '&&TOPROWCOUNT'
|
|
PROMPT * - Instance ID = '&&INST_ID'
|
|
PROMPT * - To Minutes = '&&TO_MINUTES'
|
|
PROMPT *****************************************************************
|
|
|
|
|
|
COLUMN service_name HEADING "ServiceName" FORMAT a30
|
|
COLUMN percent HEADING "Activity| (%age)" FORMAT 999.99
|
|
COLUMN session_count HEADING "Active|Session|Count" FORMAT 999,999
|
|
|
|
WITH ash_session_summary AS
|
|
(
|
|
select TRUNC(ash.sample_time,'MI') sample_time
|
|
, s.name service_name
|
|
, count(1) session_count
|
|
from gv$active_session_history ash
|
|
JOIN dba_services s ON s.name_hash = ash.service_hash
|
|
where 1=1
|
|
AND s.name NOT IN ('SYS$BACKGROUND')
|
|
--and s.name = 'pr01pimi_sas_con'
|
|
and ash.sample_time > sysdate - (1/24)
|
|
group by TRUNC(ash.sample_time,'MI'), s.name
|
|
)
|
|
, ash_rank AS
|
|
(
|
|
SELECT service_name
|
|
, RANK() OVER (ORDER BY SUM(session_count) desc) rank
|
|
FROM ash_session_summary
|
|
GROUP BY service_name
|
|
)
|
|
, graph_unit AS
|
|
(
|
|
SELECT GREATEST(CEIL(MAX(SUM(session_count))/100),1) divider
|
|
FROM ash_session_summary
|
|
GROUP BY sample_time
|
|
)
|
|
SELECT a.sample_time
|
|
, MAX(DECODE(r.rank,1,a.service_name,' ')) rank1_service
|
|
, MAX(DECODE(r.rank,2,a.service_name,' ')) rank2_service
|
|
, MAX(DECODE(r.rank,3,a.service_name,' ')) rank3_service
|
|
, MAX(DECODE(r.rank,4,a.service_name,' ')) rank4_service
|
|
, SUM(DECODE(r.rank,1,a.session_count,0)) rank1
|
|
, SUM(DECODE(r.rank,2,a.session_count,0)) rank2
|
|
, SUM(DECODE(r.rank,3,a.session_count,0)) rank3
|
|
, SUM(DECODE(r.rank,4,a.session_count,0)) rank4
|
|
, SUM(CASE WHEN r.rank >= 5 THEN a.session_count ELSE 0 END) rank_others
|
|
, TRIM(RPAD(' ',ROUND(SUM(DECODE(r.rank,1,a.session_count,0))/g.divider),'#'))
|
|
|| TRIM(RPAD(' ',ROUND(SUM(DECODE(r.rank,2,a.session_count,0))/g.divider),'%'))
|
|
|| TRIM(RPAD(' ',ROUND(SUM(DECODE(r.rank,3,a.session_count,0))/g.divider),'&'))
|
|
|| TRIM(RPAD(' ',ROUND(SUM(DECODE(r.rank,4,a.session_count,0))/g.divider),'^'))
|
|
|| TRIM(RPAD(' ',ROUND(SUM(CASE WHEN r.rank >= 5 THEN a.session_count ELSE 0 END)/g.divider),'*'))
|
|
Graph
|
|
FROM ash_session_summary a
|
|
JOIN ash_rank r ON r.service_name = a.service_name
|
|
JOIN graph_unit g on g.divider >= 1
|
|
WHERE 1=1
|
|
GROUP BY a.sample_time, g.divider
|
|
ORDER BY a.sample_Time
|
|
;
|
|
|
|
|
|
|
|
|
|
@@footer
|