@@header

/*
*
*  Author     : Vishal Gupta
*  Purpose    : Display Parallel Session Statisitcs Summary
*  Version    : ???
*  Parameters : NONE
*               
*
*  Revision History:
*  ===================
*  Date       Author        Description
*  ---------  ------------  -----------------------------------------
*  06-Sep-12  Vishal Gupta  Created
*
*/


PROMPT
PROMPT ****************************************
PROMPT *  PARALLEL SESSIONS STATISTIC SUMMARY 
PROMPT ****************************************
REM PROMPT -                               <------------------------ Last Query ------------------------------->     <-------------------------------------  Total --------------------------------> 
REM    Statistic                 Instance1 Instance2 Instance3 Instance4 Instance5 Instance6 Instance7 Instance8 Instance1 Instance2 Instance3 Instance4 Instance5 Instance6 Instance7 Instance8

COLUMN statistic                  HEADING "Statistic"                FORMAT a25
COLUMN inst1_last_query           HEADING "Last|Query|Instance1"     FORMAT 999,999
COLUMN inst2_last_query           HEADING "Last|Query|Instance2"     FORMAT 999,999
COLUMN inst3_last_query           HEADING "Last|Query|Instance3"     FORMAT 999,999
COLUMN inst4_last_query           HEADING "Last|Query|Instance4"     FORMAT 999,999
COLUMN inst5_last_query           HEADING "Last|Query|Instance5"     FORMAT 999,999
COLUMN inst6_last_query           HEADING "Last|Query|Instance6"     FORMAT 999,999
COLUMN inst7_last_query           HEADING "Last|Query|Instance7"     FORMAT 999,999
COLUMN inst8_last_query           HEADING "Last|Query|Instance8"     FORMAT 999,999
COLUMN inst1_session_total        HEADING "Total|Instance1"          FORMAT 999,999
COLUMN inst2_session_total        HEADING "Total|Instance2"          FORMAT 999,999
COLUMN inst3_session_total        HEADING "Total|Instance3"          FORMAT 999,999
COLUMN inst4_session_total        HEADING "Total|Instance4"          FORMAT 999,999
COLUMN inst5_session_total        HEADING "Total|Instance5"          FORMAT 999,999
COLUMN inst6_session_total        HEADING "Total|Instance6"          FORMAT 999,999
COLUMN inst7_session_total        HEADING "Total|Instance7"          FORMAT 999,999
COLUMN inst8_session_total        HEADING "Total|Instance8"          FORMAT 999,999


SELECT TRIM(p.statistic)                               statistic
     , MAX(DECODE(p.inst_id, 1, p.last_query,NULL))    inst1_last_query
     , MAX(DECODE(p.inst_id, 2, p.last_query,NULL))    inst2_last_query
     , MAX(DECODE(p.inst_id, 3, p.last_query,NULL))    inst3_last_query
     , MAX(DECODE(p.inst_id, 4, p.last_query,NULL))    inst4_last_query
     , MAX(DECODE(p.inst_id, 5, p.last_query,NULL))    inst5_last_query
     , MAX(DECODE(p.inst_id, 6, p.last_query,NULL))    inst6_last_query
     , MAX(DECODE(p.inst_id, 7, p.last_query,NULL))    inst7_last_query
     , MAX(DECODE(p.inst_id, 8, p.last_query,NULL))    inst8_last_query
     , MAX(DECODE(p.inst_id, 1, p.session_total,NULL)) inst1_session_total
     , MAX(DECODE(p.inst_id, 2, p.session_total,NULL)) inst2_session_total
     , MAX(DECODE(p.inst_id, 3, p.session_total,NULL)) inst3_session_total
     , MAX(DECODE(p.inst_id, 4, p.session_total,NULL)) inst4_session_total
     , MAX(DECODE(p.inst_id, 5, p.session_total,NULL)) inst5_session_total
     , MAX(DECODE(p.inst_id, 6, p.session_total,NULL)) inst6_session_total
     , MAX(DECODE(p.inst_id, 7, p.session_total,NULL)) inst7_session_total
     , MAX(DECODE(p.inst_id, 8, p.session_total,NULL)) inst8_session_total
  FROM gv$pq_sesstat p
GROUP BY p.statistic
ORDER BY DECODE(TRIM(p.statistic)
               ,'Queries Parallelized'  ,01
               ,'DML Parallelized'      ,02
               ,'DDL Parallelized'      ,03
               ,'DFO Trees'             ,04
               ,'Server Threads'        ,05
               ,'Allocation Height'     ,06
               ,'Allocation Width'      ,07
               ,'Local Msgs Sent'       ,08
               ,'Local Msgs Recv''d'    ,09
               ,'Distr Msgs Sent'       ,10
               ,'Distr Msgs Recv''d'    ,11
               ,99
               )
/

@@footer