184 lines
9.8 KiB
SQL
184 lines
9.8 KiB
SQL
@@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
|