@@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 a10
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