82 lines
4.2 KiB
SQL
82 lines
4.2 KiB
SQL
@@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
|