168 lines
6.7 KiB
MySQL
168 lines
6.7 KiB
MySQL
@@header
|
|
|
|
/*
|
|
*
|
|
* Author : Vishal Gupta
|
|
* Purpose : Display Session Hierarchy
|
|
* Parameters : 1 - SID (Use % as wildcard)
|
|
* : 2 - INST_ID (Use % as wildcard)
|
|
*
|
|
* Revision History:
|
|
* ===================
|
|
* Date Author Description
|
|
* --------- ------------ -----------------------------------------
|
|
* 20-Apr-12 Vishal Gupta Created
|
|
*
|
|
*/
|
|
|
|
|
|
/************************************
|
|
* INPUT PARAMETERS
|
|
************************************/
|
|
UNDEFINE SID
|
|
UNDEFINE INST_ID
|
|
UNDEFINE WHERE_CLAUSE
|
|
|
|
DEFINE SID="&&1"
|
|
DEFINE INST_ID="&&2"
|
|
DEFINE WHERE_CLAUSE="&3"
|
|
|
|
|
|
COLUMN _SID NEW_VALUE SID NOPRINT
|
|
COLUMN _INST_ID NEW_VALUE INST_ID NOPRINT
|
|
|
|
set term off
|
|
SELECT DECODE('&&SID','','%','&&SID') "_SID"
|
|
, DECODE('&&INST_ID','','%','&&INST_ID') "_INST_ID"
|
|
FROM DUAL;
|
|
set term on
|
|
|
|
PROMPT **************************************************************
|
|
PROMPT * P A R A L L E L S E S S I O N S H I E R A R C H Y
|
|
PROMPT *
|
|
PROMPT * Input Parameters
|
|
PROMPT * - SID = "&&SID"
|
|
PROMPT * - Instance# = "&&INST_ID"
|
|
PROMPT * - Where Clause = "&&WHERE_CLAUSE"
|
|
PROMPT **************************************************************
|
|
|
|
set pages 50000
|
|
|
|
COLUMN INST_ID HEADING "I#" FORMAT 99
|
|
COLUMN SID HEADING "SID|(Hierarchy)" FORMAT a15
|
|
COLUMN serial# HEADING "Serial#" FORMAT 99999
|
|
COLUMN SPID FORMAT a6
|
|
COLUMN QCINST_ID HEADING "Parent|I#" FORMAT 99
|
|
COLUMN QCSID HEADING "Parent|SID" FORMAT 9999
|
|
COLUMN qcserial# HEADING "Parent|Serial#" FORMAT 99999
|
|
COLUMN server_hierarchy HEADING "Parallel|ServerSet|Hierarchy" FORMAT a10
|
|
COLUMN server_group HEADING "Server|Group" FORMAT 99
|
|
COLUMN server_set HEADING "Server|Set" FORMAT 99
|
|
COLUMN server# HEADING "Server#" FORMAT 99
|
|
COLUMN degree HEADING "Deg" FORMAT 999
|
|
COLUMN req_degree HEADING "Req|Deg" FORMAT 999
|
|
COLUMN slave_status HEADING "Slave|Status" FORMAT a10
|
|
COLUMN status HEADING "Session|Status" FORMAT a8
|
|
COLUMN service_name FORMAT a22
|
|
COLUMN username FORMAT a20
|
|
COLUMN last_call_et HEADING "LastCall" FORMAT a12 JUSTIFY RIGHT
|
|
COLUMN logon_time FORMAT a15
|
|
COLUMN process FORMAT a15
|
|
COLUMN osuser FORMAT a10 TRUNCATE
|
|
COLUMN machine FORMAT a30
|
|
COLUMN program FORMAT a15 TRUNCATE
|
|
COLUMN server_name FORMAT a15 TRUNCATE
|
|
COLUMN sql_text FORMAT a150
|
|
COLUMN sql_child_number HEADING "SQL|C#" FORMAT 99
|
|
COLUMN event FORMAT a30 TRUNCATE
|
|
|
|
BREAK ON server_group ON server_set ON server#
|
|
|
|
WITH parallel_sessions AS
|
|
(
|
|
SELECT pxs.inst_id
|
|
, pxs.sid
|
|
, pxs.serial#
|
|
, pxs.qcinst_id
|
|
, pxs.qcsid
|
|
, pxs.qcserial#
|
|
, pxs.degree
|
|
, pxs.req_degree
|
|
, pxp.server_name
|
|
, pxs.server_group
|
|
, pxs.server_set
|
|
, pxs.server#
|
|
, pxp.status
|
|
FROM gv$px_session pxs
|
|
, gv$px_process pxp
|
|
WHERE pxs.inst_id = pxp.inst_id (+)
|
|
AND pxs.sid = pxp.sid (+)
|
|
AND pxs.serial# = pxp.serial# (+)
|
|
ORDER BY pxs.qcinst_id NULLS FIRST
|
|
, pxs.qcsid
|
|
, pxs.qcserial#
|
|
, pxs.inst_id
|
|
, pxs.sid
|
|
, pxs.serial#
|
|
, pxs.server_group
|
|
, pxs.server_set
|
|
, pxs.server#
|
|
)
|
|
SELECT DECODE(level,1, '' , ' ')
|
|
|| LPAD('> ',(level-1)*5,'|--')
|
|
|| pxs.sid sid
|
|
, pxs.inst_id
|
|
, p.spid
|
|
--, NVL(pxs.qcinst_id,pxs.inst_id) qcinst_id
|
|
--, pxs.qcsid
|
|
--, pxs.qcserial#
|
|
, pxs.req_degree
|
|
, pxs.degree
|
|
, LPAD(DECODE(pxs.server#, 1, TO_CHAR(pxs.server_group), ' '),2)
|
|
|| ' '
|
|
|| LPAD(DECODE(pxs.server#, 1, TO_CHAR(pxs.server_set), ' '),2)
|
|
|| ' '
|
|
|| LPAD(pxs.server#,2) server_hierarchy
|
|
--, pxs.server_group
|
|
--, pxs.server_set
|
|
--, pxs.server#
|
|
, pxs.status slave_status
|
|
, s.status
|
|
, 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-YY HH24:MI') logon_time
|
|
, s.service_name
|
|
, s.username
|
|
, s.osuser
|
|
, DECODE(level,1, '' ,LPAD('> ',((level-1)*3)+2,'|--') )
|
|
|| NVL2(pxs.qcinst_id,pxs.server_name || ' I#' || pxs.inst_id , s.program)
|
|
program
|
|
, s.sql_id
|
|
, s.sql_child_number
|
|
, s.event
|
|
FROM parallel_sessions pxs
|
|
LEFT OUTER JOIN gv$session s ON s.inst_id = pxs.inst_id
|
|
AND s.sid = pxs.sid
|
|
AND s.serial# = pxs.serial#
|
|
LEFT OUTER JOIN gv$process p ON p.inst_id = s.inst_id
|
|
AND p.addr = s.paddr
|
|
WHERE NVL(pxs.qcinst_id,pxs.inst_id) LIKE '&&INST_ID'
|
|
AND pxs.qcsid LIKE '&&SID'
|
|
&&WHERE_CLAUSE
|
|
CONNECT BY NOCYCLE PRIOR pxs.inst_id = NVL(pxs.qcinst_id,pxs.inst_id)
|
|
AND PRIOR pxs.sid = pxs.qcsid
|
|
AND PRIOR pxs.serial# = pxs.qcserial#
|
|
START WITH pxs.qcinst_id IS NULL
|
|
ORDER BY NVL(pxs.qcinst_id,pxs.inst_id)
|
|
, pxs.qcsid NULLS FIRST
|
|
, pxs.qcserial# NULLS FIRST
|
|
, pxs.server_group NULLS FIRST
|
|
, pxs.server_set NULLS FIRST
|
|
, pxs.server# NULLS FIRST
|
|
;
|
|
|
|
@@footer
|