@@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