106 lines
3.2 KiB
MySQL
106 lines
3.2 KiB
MySQL
@@header
|
|
|
|
/*
|
|
*
|
|
* Author : Vishal Gupta
|
|
* Purpose : Display top user from ASH
|
|
* Parameters : 1 - Number of MINUTES (Default 5min))
|
|
* 2 - Top Row Count Default 10)
|
|
*
|
|
* Revision History:
|
|
* ===================
|
|
* Date Author Description
|
|
* --------- ------------ -----------------------------------------
|
|
* 02-Apr-13 Vishal Gupta Created
|
|
*
|
|
*/
|
|
|
|
|
|
/************************************
|
|
* INPUT PARAMETERS
|
|
************************************/
|
|
UNDEFINE MINUTES
|
|
UNDEFINE TOPROWCOUNT
|
|
UNDEFINE INST_ID
|
|
|
|
DEFINE MINUTES="&&1"
|
|
DEFINE TOPROWCOUNT="&&2"
|
|
DEFINE INST_ID='&&3'
|
|
|
|
|
|
set term off
|
|
COLUMN _MINUTES NEW_VALUE MINUTES NOPRINT
|
|
COLUMN _TOPROWCOUNT NEW_VALUE TOPROWCOUNT NOPRINT
|
|
COLUMN _INST_ID NEW_VALUE INST_ID NOPRINT
|
|
|
|
SELECT DECODE('&&MINUTES','','5','&&MINUTES') "_MINUTES"
|
|
, DECODE('&&TOPROWCOUNT','','5','&&TOPROWCOUNT') "_TOPROWCOUNT"
|
|
, DECODE('&&INST_ID','','%','&&INST_ID') "_INST_ID"
|
|
from dual;
|
|
|
|
set term on
|
|
|
|
|
|
PROMPT *****************************************************************
|
|
PROMPT * ASH - Top &&TOPROWCOUNT Active Sesssions (Over Last &&MINUTES min)
|
|
PROMPT *
|
|
PROMPT * Input Parameters
|
|
PROMPT * - Minutes = '&&MINUTES'
|
|
PROMPT * - Top Row Count = '&&TOPROWCOUNT'
|
|
PROMPT * - Instance ID = '&&INST_ID'
|
|
PROMPT *****************************************************************
|
|
|
|
|
|
COLUMN inst_id HEADING "Instance|Number" FORMAT 9999999
|
|
COLUMN sid HEADING "SID" FORMAT a6
|
|
COLUMN session_serial# HEADING "Serial#" FORMAT 9999999
|
|
COLUMN username HEADING "UserName" FORMAT a30
|
|
COLUMN program HEADING "Program" FORMAT a15 TRUNC
|
|
COLUMN machine HEADING "Machine" FORMAT a20 TRUNC
|
|
COLUMN percent HEADING "Activity| (%age)" FORMAT 999.99
|
|
COLUMN session_count HEADING "Active|Session|Count" FORMAT 999,999
|
|
|
|
WITH ash as
|
|
(SELECT /*+ NO_MERGE */
|
|
ash.inst_id
|
|
, ash.session_id
|
|
, ash.session_serial#
|
|
, u.username
|
|
, ash.program
|
|
, ash.machine
|
|
, count(1) session_count
|
|
FROM gv$active_session_history ash
|
|
, dba_users u
|
|
WHERE ash.user_id = u.user_id
|
|
AND ash.session_type <> 'BACKGROUND'
|
|
AND ash.inst_id LIKE '&&INST_ID'
|
|
AND ash.sample_time > systimestamp - (&&MINUTES/(24*60))
|
|
GROUP BY ash.inst_id
|
|
, ash.session_id
|
|
, ash.session_serial#
|
|
, u.username
|
|
, ash.program
|
|
, ash.machine
|
|
)
|
|
, ash_total as
|
|
(SELECT /*+ NO_MERGE */ SUM(session_count) session_total
|
|
FROM ash
|
|
)
|
|
SELECT * FROM (
|
|
SELECT inst_id
|
|
, session_id
|
|
, session_serial#
|
|
, username
|
|
, program
|
|
, machine
|
|
, 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
|