94 lines
2.9 KiB
MySQL
94 lines
2.9 KiB
MySQL
@@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 as
|
|
(SELECT /*+ NO_MERGE */
|
|
sn.name service_name
|
|
, count(1) session_count
|
|
FROM gv$active_session_history ash
|
|
, dba_services sn
|
|
WHERE ash.service_hash = sn.name_hash
|
|
AND ash.session_type <> 'BACKGROUND'
|
|
AND ash.inst_id LIKE '&&INST_ID'
|
|
AND ash.sample_time > systimestamp - (&&FROM_MINUTES/(24*60))
|
|
AND ash.sample_time > systimestamp - (&&TO_MINUTES/(24*60))
|
|
GROUP BY sn.name
|
|
)
|
|
, ash_total as
|
|
(SELECT /*+ NO_MERGE */ SUM(session_count) session_total
|
|
FROM ash
|
|
)
|
|
SELECT * FROM (
|
|
SELECT ash.service_name
|
|
, LPAD(TO_CHAR(ROUND((session_count/ash_total.session_total)* 100,2),'999.99'),6) percent
|
|
, ash.session_count
|
|
FROM ash , ash_total
|
|
ORDER BY ROUND((session_count/ash_total.session_total)* 100,2) desc
|
|
)
|
|
WHERE ROWNUM <= &&TOPROWCOUNT
|
|
;
|
|
|
|
|
|
|
|
@@footer
|