@@header

/*
*
*  Author     : Vishal Gupta
*  Purpose    : Display session breakdown by inst_id, username, osuser, program, machine
*  Parameters : None
*
*  Revision History:
*  ===================
*  Date       Author        Description
*  ---------  ------------  -----------------------------------------
*  02-Dec-15  Vishal Gupta  Add session count by Active Program
*  04-Apr-12  Vishal Gupta  Bug fixes
*/

set pages 20000

COLUMN separator              HEADING "!|!|!"                     FORMAT a1
COLUMN status                 HEADING "Status"                    FORMAT a8
COLUMN status_count           HEADING "Count By|Status"           FORMAT 999,999
COLUMN inst_id                HEADING "Inst#"                     FORMAT 999 
COLUMN inst_id_count          HEADING "Count By|Instance"         FORMAT 999,999
COLUMN status_instance        HEADING "Status Inst#"              FORMAT a20
COLUMN status_instance_count  HEADING "Count By|Status Instance"  FORMAT 999,999
COLUMN service_name           HEADING "ServiceName"               FORMAT a25
COLUMN service_name_count     HEADING "Count By|Service|Name"     FORMAT 999,999
COLUMN event                  HEADING "Event"                     FORMAT a30 TRUNCATE
COLUMN event_count            HEADING "Count By|Event"            FORMAT 999,999

COLUMN active_service_count       HEADING "Count By|Active|Service"          FORMAT 999,999
COLUMN active_user_count          HEADING "Count By|Active|DBUser"           FORMAT 999,999
COLUMN active_program_count       HEADING "Count By|Active|Program"           FORMAT 999,999
COLUMN active_service_user_count  HEADING "Count By|Active|Service|DBUser"   FORMAT 999,999
COLUMN ServiceUser                HEADING "ServiceUser"            FORMAT a45


COLUMN username                HEADING "UserName"                       FORMAT a20
COLUMN username_count          HEADING "Count By|DBUser|Name"           FORMAT 999,999
COLUMN status_username         HEADING "StatusUserName"                 FORMAT a30
COLUMN status_username_count   HEADING "Count By|Status|DBUser|Name"    FORMAT 999,999
COLUMN osuser                  HEADING "OSUser"                         FORMAT a20       TRUNCATE
COLUMN osuser_count            HEADING "Count By|OSUser"                FORMAT 999,999
COLUMN program                 HEADING "Program"                        FORMAT a40       TRUNCATE
COLUMN program_count           HEADING "Count By|Program"               FORMAT 999,999
COLUMN machine                 HEADING "Client|Machine"                 FORMAT a40       TRUNCATE
COLUMN machine_count           HEADING "Count By|Machine"               FORMAT 999,999

BREAK ON REPORT    

COMPUTE SUM LABEL 'Total' OF inst_id_count         FORMAT 99,999,999 ON REPORT 
--COMPUTE SUM LABEL 'Total' OF service_name_count    FORMAT 99,999,999 ON REPORT 
--COMPUTE SUM LABEL 'Total' OF username_count        FORMAT 99,999,999 ON REPORT 
--COMPUTE SUM LABEL 'Total' OF osuser_count          FORMAT 99,999,999 ON REPORT 
--COMPUTE SUM LABEL 'Total' OF program_count         FORMAT 99,999,999 ON REPORT 
--COMPUTE SUM LABEL 'Total' OF machine_count         FORMAT 99,999,999 ON REPORT 

PROMPT  ##############################################################
PROMPT  #    S E S S I O N S      C O U N T      B R E A K D O W N 
PROMPT  #   
PROMPT  #  - By Instance
PROMPT  #  - By ServiceName
PROMPT  #  - By DatabaseUsername
PROMPT  #  - By OSUsername
PROMPT  #  - By Program
PROMPT  #  - By Client machine name
PROMPT  #  - By Event
PROMPT  #   
PROMPT  #   ( Excluding parallel slaves)
PROMPT  #   
PROMPT  ##############################################################


WITH sess      AS 
  ( SELECT * FROM gv$session s 
     WHERE NOT EXISTS (SELECT 1 
                         FROM gv$px_session px 
                        where px.inst_id = s.inst_id and px.sid = s.sid 
                        /*Take only parallel co-ordinators, parallel slaves have qcinst_id=NULL*/
                        AND px.qcinst_id IS NOT NULL 
                      ) 
     AND TYPE <> 'BACKGROUND'
 ) 
, byinstance    AS ( SELECT rownum r , a.* FROM (select inst_id     , count(1) total_count from sess group by inst_id      order by inst_id asc  ) a )
, bystatus      AS ( SELECT rownum r , a.* FROM (select status      , count(1) total_count from sess group by status       order by status  ) a )
, bystatusInstance      AS ( SELECT rownum r , a.* FROM (select RPAD(status,8) || ' Inst# '|| inst_id  status_instance   , count(1) total_count from sess group by status, inst_id  order by 1,2 desc  ) a )
, byServiceName AS ( SELECT rownum r , a.* FROM (select service_name, count(1) total_count from sess group by service_name order by count(1) desc  ) a )
, byEvent AS ( SELECT rownum r , a.* FROM (select event, count(1) total_count from sess group by event order by count(1) desc  ) a )
SELECT i.total_count  inst_id_count          , i.inst_id
     , '|'  separator
     , sr.total_count service_name_count     , sr.service_name
     , '|'  separator
     , s.total_count  status_count           , s.status
     , '|'  separator
     , si.total_count status_instance_count  , si.status_instance
     , '|'  separator
     , e.total_count  event_count            , e.event
     , '|'  separator
  FROM byservicename sr
       LEFT OUTER JOIN bystatus         s  on s.r = sr.r 
       LEFT OUTER JOIN byinstance       i  on i.r = sr.r 
       LEFT OUTER JOIN bystatusInstance si on si.r = sr.r 
       LEFT OUTER JOIN byEvent          e  ON e.r  = sr.r
ORDER BY sr.r       
;

PROMPT
PROMPT  ##############################################################
PROMPT  #   A C T I V E     S E S S I O N S      B R E A K D O W N 
PROMPT  #   ( Excluding parallel slaves)
PROMPT  ##############################################################

WITH sess      AS 
  ( SELECT * FROM gv$session s 
     WHERE NOT EXISTS (SELECT 1 
                         FROM gv$px_session px 
                        where px.inst_id = s.inst_id and px.sid = s.sid 
                        /*Take only parallel co-ordinators, parallel slaves have qcinst_id=NULL*/
                        AND px.qcinst_id IS NOT NULL 
                      ) 
     AND TYPE <> 'BACKGROUND'
 ) 
, byActiveService  AS ( SELECT rownum r , a.* FROM (select service_name , count(1) total_count from sess where status = 'ACTIVE' group by service_name  order by count(1) desc ) a )
, byActiveUser     AS ( SELECT rownum r , a.* FROM (select UserName     , count(1) total_count from sess where status = 'ACTIVE' group by UserName      order by count(1) desc ) a )
, byActiveProgram  AS ( SELECT rownum r , a.* FROM (select Program      , count(1) total_count from sess where status = 'ACTIVE' group by Program       order by count(1) desc ) a )
, byActiveServiceUser     AS ( SELECT rownum r , a.* FROM (select service_name,UserName     , count(1) total_count from sess where status = 'ACTIVE' group by service_name,UserName     order by count(1) desc ) a )
SELECT asv.total_count active_service_count       , asv.service_name
     , '|'  separator
     , au.total_count active_user_count           , au.username
     , '|'  separator
     , ap.total_count active_program_count        , ap.program
     , '|'  separator
     , asu.total_count active_service_user_count  , asu.service_name || ' - ' || asu.username ServiceUser
     , '|'  separator
  FROM byActiveServiceUser asu
       LEFT OUTER JOIN byActiveUser      au  on au.r  = asu.r 
       LEFT OUTER JOIN byActiveProgram   ap  on ap.r  = asu.r 
       LEFT OUTER JOIN byActiveService   asv on asv.r = asu.r 
ORDER BY asu.r       
;

PROMPT
PROMPT  ##############################################################
PROMPT  #   A L L      S E S S I O N S      B R E A K D O W N 
PROMPT  #   ( Excluding parallel slaves)
PROMPT  ##############################################################

WITH 
  sess          AS ( SELECT * FROM gv$session s WHERE NOT EXISTS (SELECT 1 FROM gv$px_session px where px.inst_id = s.inst_id and px.sid = s.sid 
                                                                       /*Take only parallel co-ordinators, parallel slaves have qcinst_id=NULL*/
                                                                       AND px.qcinst_id IS NOT NULL ) 
                     AND TYPE <> 'BACKGROUND'
                    ) 
, byusername    AS ( SELECT rownum r , a.* FROM (select username    , count(1) total_count from sess group by username     order by 2 desc,1) a )
, byosuser      AS ( SELECT rownum r , a.* FROM (select osuser      , count(1) total_count from sess group by osuser       order by 2 desc,1) a )
, byprogram     AS ( SELECT rownum r , a.* FROM (select SUBSTR(program,1,60) program     , count(1) total_count from sess group by SUBSTR(program,1,60)      order by 2 desc,1) a )
, bymachine     AS ( SELECT rownum r , a.* FROM (select machine     , count(1) total_count from sess group by machine      order by 2 desc,1) a )
, bystatus_username    AS ( SELECT rownum r , a.* FROM (select RPAD(status,8) || ' ' || username  status_username,  count(1) total_count from sess group by status, username  order by 2 desc,1) a )
SELECT u.total_count username_count     , u.username
     , '|'  separator
     , su.total_count status_username_count , su.status_username
     , '|'  separator
     , o.total_count osuser_count       , o.osuser  
     , '|'  separator
     , p.total_count program_count      , p.program 
     , '|'  separator
     , p.total_count machine_count      , m.machine 
     , '|'  separator
  FROM bymachine m
       LEFT OUTER JOIN byusername    u on u.r = m.r 
       LEFT OUTER JOIN byprogram     p on p.r = m.r 
       LEFT OUTER JOIN byosuser      o on o.r = m.r 
       LEFT OUTER JOIN bystatus_username su on su.r = m.r
WHERE m.r <= 30
ORDER BY m.r        
;


  

  

@@footer