@@header

/*
*
*  Author     : Vishal Gupta
*  Purpose    : Display top session by statname
*  Usage      : @<filename> <statname> [<toprecordcount>]
*  Parameters : 1 - Instance Number (Use % as wildcard)
*               2 - Number of top record counts 
*               3 - Statname (Use % as wildcard, double quotes for spaces.
*               4 - Where clause
*                               
*
*  Revision History:
*  ===================
*  Date       Author        Description
*  ---------  ------------  -----------------------------------------
*  11-Sep-07  Vishal Gupta  First cut
*  27-Mar-12  Vishal Gupta  Modified to take instance_id as input parameter to 
*                           give ability to give topsession at instance level.
*  15-Jun-12  Vishal Gupta  Added last_call_et to output
*  22-Jun-12  Vishal Gupta  Change parameter order to be in line with other scripts.
*  
*/

set lines 3000

/************************************
*  INPUT PARAMETERS
************************************/

DEFINE inst_id="&&1"
DEFINE toprecordcount="&&2"
DEFINE statname="&&3"
DEFINE whereclause="&&4"



COLUMN  _inst_id            NEW_VALUE  inst_id              NOPRINT
COLUMN  _toprecordcount     NEW_VALUE  toprecordcount       NOPRINT
COLUMN  _statname           NEW_VALUE  statname             NOPRINT
COLUMN  _whereclause        NEW_VALUE  whereclause          NOPRINT

set term off
SELECT DECODE('&&inst_id','','%','&&inst_id')   "_inst_id"
     , DECODE('&&toprecordcount','','50','&&toprecordcount')   "_toprecordcount"
     , DECODE('&&whereclause','','AND s.type <> ''BACKGROUND'' ','&&whereclause')   "_whereclause"
FROM DUAL;
set term on



/************************************
*  CONFIGURATION PARAMETERS
************************************/

--DEFINE SHOW_BACKGROUND_PROCESSES='Y'
DEFINE SHOW_BACKGROUND_PROCESSES='N'

DEFINE COUNT_FORMAT=999,999,999
--DEFINE COUNT_DIVIDER="1"
--DEFINE COUNT_HEADING="#"
DEFINE COUNT_DIVIDER="1000"
DEFINE COUNT_HEADING="#1000"

DEFINE BYTES_FORMAT="999,999"
--DEFINE BYTES_DIVIDER="1024"
--DEFINE BYTES_HEADING="KB"
DEFINE BYTES_DIVIDER="1024/1024"
DEFINE BYTES_HEADING="MB"
--DEFINE BYTES_DIVIDER="1024/1024/1024"
--DEFINE BYTES_HEADING="GB"

DEFINE TIME_FORMAT=9,999
DEFINE TIME_DIVIDER="60"
DEFINE TIME_HEADING="min"


PROMPT
PROMPT **********************************************************************************
PROMPT * Top &toprecordcount Session by Statistic - "&&statname" 
PROMPT * 
PROMPT * Input Parameters
PROMPT *   - Instance Number = '&&INST_ID'
PROMPT *   - Top row count   = '&&toprecordcount'
PROMPT *   - Statistic Name  = '&&statname'
PROMPT *   - Where Clause  = "&&whereclause"
PROMPT **********************************************************************************

COLUMN name             HEADING "Statistic Name"     FORMAT a30 TRUNCATE
COLUMN value            HEADING "Value"              FORMAT 99,999,999,999,999
COLUMN "%age"           HEADING "%age"               FORMAT 999.00
COLUMN inst_id          HEADING "I#"                 FORMAT 99
COLUMN sid              HEADING "SID"                FORMAT 9999
COLUMN sess             HEADING "Session"            FORMAT a15
COLUMN spid             HEADING "SPID"               FORMAT a6
COLUMN last_call_et     HEADING "LastCall"           FORMAT a12  JUSTIFY RIGHT
COLUMN logon_time       HEADING "Logon Time"         FORMAT a20
COLUMN username         HEADING "UserName"           FORMAT a20
COLUMN service_name     HEADING "ServiceName"        FORMAT a25
COLUMN osuser           HEADING "OS User"            FORMAT a20  TRUNCATE
COLUMN process          HEADING "Client|Process"     FORMAT a15  
COLUMN program          HEADING "Program"            FORMAT a20  TRUNCATE
COLUMN machine          HEADING "Machine"            FORMAT a20  TRUNCATE

SELECT *
FROM
  (SELECT a.name
        , a.VALUE
        , ROUND((a.VALUE / b.total_value) *100,    2) "%age"
        , a.sid
        , a.inst_id
      --, a.sid || ',' || a.serial# || ',@' || a.inst_id sess
              , a.spid
        , a.status
        , a.last_call_et
        , a.logon_time
        , a.username
        , a.service_name
        , a.osuser
        , a.process
        , a.PROGRAM
            --, a.machine
   FROM (SELECT sn.name
              , ss.VALUE
              , s.inst_id
              , s.sid
                       , s.serial#
                       , p.spid
              , s.status
              , s.osuser
              , s.username
              , s.service_name
              , LPAD(DECODE(FLOOR(s.last_call_et / 3600),0, '', FLOOR(s.last_call_et / 3600) || 'h ' )
                     || LPAD(FLOOR(MOD(s.last_call_et , 3600 ) / 60),2) || 'm ' 
                     || LPAD(MOD(s.last_call_et, 60 ) ,2) || 's' 
                     , 12) last_call_et
              , to_char(s.logon_time,    'dd-MON-yyyy hh24:mi:ss') logon_time
              , s.process
              , s.PROGRAM
                       , s.machine
         FROM gv$session s
            , gv$sesstat ss
            , v$statname sn
                     , gv$process p
         WHERE s.sid = ss.sid
         AND s.inst_id = ss.inst_id
               AND s.paddr = p.addr (+)
         AND s.inst_id = p.inst_id (+)
         AND ss.statistic# = sn.statistic#
         AND s.inst_id LIKE '&&inst_id'
         AND LOWER(sn.name) LIKE LOWER('&&statname')
         &&whereclause
       ) a
      , (SELECT DECODE('&&inst_id', '%' ,'%', s.inst_id) inst_id
              , sn.name
              , SUM(VALUE) total_value
         FROM gv$session s
            , gv$sesstat ss
            , v$statname sn
         WHERE s.sid = ss.sid
         AND s.inst_id = ss.inst_id
         AND ss.statistic# = sn.statistic#
         AND s.inst_id LIKE '&&inst_id'
         AND LOWER(sn.name) LIKE LOWER( '&&statname' )
         &&whereclause
         GROUP BY DECODE('&&inst_id', '%' ,'%', s.inst_id)
                , sn.name
         having sum(value) > 0
        ) b
  WHERE a.name = b.name
    AND a.inst_id LIKE '&&inst_id'
  ORDER BY a.VALUE DESC
)
WHERE rownum <= &&toprecordcount
;

UNDEFINE inst_id
UNDEFINE toprecordcount
UNDEFINE statname
UNDEFINE whereclause

@@footer