150 lines
4.8 KiB
MySQL
150 lines
4.8 KiB
MySQL
@@header
|
|
|
|
/*
|
|
*
|
|
* Author : Vishal Gupta
|
|
* Purpose : Display Session Hierarchy
|
|
* Parameters : 1 - INST_ID (Use % as wildcard)
|
|
* 2 - WHERE CLAUSE
|
|
*
|
|
* Revision History:
|
|
* ===================
|
|
* Date Author Description
|
|
* --------- ------------ -----------------------------------------
|
|
* 20-Apr-12 Vishal Gupta First Draft
|
|
*
|
|
*/
|
|
|
|
|
|
|
|
/************************************
|
|
* INPUT PARAMETERS
|
|
************************************/
|
|
DEFINE INST_ID="&&1"
|
|
DEFINE WHERE_CLAUSE="&2"
|
|
|
|
COLUMN _INST_ID NEW_VALUE INST_ID NOPRINT
|
|
COLUMN _TOP_ROWCOUNT NEW_VALUE TOP_ROWCOUNT NOPRINT
|
|
COLUMN _WHERE_CLAUSE NEW_VALUE WHERE_CLAUSE NOPRINT
|
|
|
|
SELECT DECODE('&&INST_ID','','%','&&INST_ID') "_INST_ID"
|
|
-- , DECODE('&&WHERE_CLAUSE','','','&&WHERE_CLAUSE') "_WHERE_CLAUSE"
|
|
FROM DUAL
|
|
;
|
|
|
|
|
|
|
|
PROMPT *************************************************************************************
|
|
PROMPT * D A T A B A S E S E S S I O N S ( A C T I V E ) T R E E
|
|
PROMPT *
|
|
PROMPT * Input Parameters
|
|
PROMPT * - Instance# = "&&INST_ID"
|
|
PROMPT * - Where Clause = "&&WHERE_CLAUSE"
|
|
PROMPT *************************************************************************************
|
|
|
|
|
|
set pages 50
|
|
|
|
COLUMN INST_ID HEADING "I#" FORMAT 99
|
|
COLUMN SID HEADING "SID(Hierarchy)" FORMAT a11
|
|
COLUMN serial# HEADING "Serial#" FORMAT 99999
|
|
COLUMN QCINST_ID HEADING "Parent|I#" FORMAT 99
|
|
COLUMN QCSID HEADING "Parent|SID" FORMAT 9999
|
|
COLUMN qcserial# HEADING "Parent|Serial#" FORMAT 99999
|
|
COLUMN degree HEADING "Deg" FORMAT 99
|
|
COLUMN req_degree HEADING "Req|Deg" FORMAT 99
|
|
COLUMN state HEADING "State" FORMAT a12 TRUNCATED
|
|
COLUMN SPID FORMAT a6
|
|
COLUMN username FORMAT a20
|
|
COLUMN last_call_et HEADING "LastCall|(sec)" FORMAT 99,999
|
|
COLUMN logon_time FORMAT a15
|
|
COLUMN service_name FORMAT a21
|
|
COLUMN process FORMAT a15
|
|
COLUMN osuser FORMAT a10 TRUNCATE
|
|
COLUMN machine FORMAT a30
|
|
COLUMN program FORMAT a15 TRUNCATE
|
|
COLUMN sql_text FORMAT a60 TRUNCATE
|
|
COLUMN event FORMAT a30 TRUNCATE
|
|
|
|
--BREAK ON QCSID ON QCSID SKIP 1
|
|
/*
|
|
WITH parallel_sessions AS
|
|
(
|
|
SELECT pxs.inst_id
|
|
, pxs.sid
|
|
, pxs.serial#
|
|
, pxs.qcinst_id
|
|
, pxs.qcsid
|
|
, pxs.qcserial#
|
|
, degree
|
|
, req_degree
|
|
FROM gv$px_session pxs
|
|
ORDER BY pxs.qcinst_id NULLS FIRST
|
|
, pxs.qcsid
|
|
, pxs.qcserial#
|
|
, pxs.inst_id
|
|
, pxs.sid
|
|
, pxs.serial#
|
|
)
|
|
*/
|
|
SELECT /*+ RULE */
|
|
--, level
|
|
DECODE(level,1, '' , ' ')
|
|
|| LPAD('> ',(level-1)*5,'|--')
|
|
|| s.sid sid
|
|
, s.inst_id
|
|
-- , pxs.serial#
|
|
-- , pxs.qcinst_id
|
|
-- , pxs.qcsid
|
|
-- , pxs.qcserial#
|
|
, p.spid
|
|
-- , pxs.req_degree
|
|
, pxs.degree
|
|
-- , s.status
|
|
, s.state
|
|
, s.last_call_et
|
|
, TO_CHAR(s.LOGON_TIME,'DD-MON-YY HH24:MI') logon_time
|
|
, s.sql_id
|
|
, s.username
|
|
, s.service_name
|
|
, s.osuser
|
|
, s.program
|
|
, sql.sql_text
|
|
FROM gv$session s
|
|
-- , parallel_sessions pxs
|
|
, gv$px_session pxs
|
|
, gv$process p
|
|
, gv$sqltext sql
|
|
, gv$session s2
|
|
WHERE p.inst_id = s.inst_id
|
|
AND p.addr = s.paddr
|
|
AND s.inst_id = pxs.inst_id (+)
|
|
AND s.sid = pxs.sid (+)
|
|
AND s.serial# = pxs.serial# (+)
|
|
AND s.inst_id = sql.inst_id (+)
|
|
AND s.sql_id = sql.sql_id (+)
|
|
AND pxs.qcinst_id = s2.inst_id (+)
|
|
AND pxs.qcsid = s2.sid (+)
|
|
AND sql.PIECE (+) = 0
|
|
AND s.status <> 'INACTIVE'
|
|
AND s.type <> 'BACKGROUND'
|
|
AND s.inst_id LIKE '&&INST_ID'
|
|
&&WHERE_CLAUSE
|
|
CONNECT BY 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(s2.last_call_et,s.last_call_et) DESC
|
|
-- , NVL(pxs.qcinst_id,pxs.inst_id)
|
|
-- , pxs.qcsid
|
|
, s.inst_id
|
|
, s.sid
|
|
*/
|
|
;
|
|
|
|
UNDEFINE INST_ID
|
|
UNDEFINE WHERE_CLAUSE
|
|
|
|
@@footer
|