178 lines
7.5 KiB
SQL
178 lines
7.5 KiB
SQL
@@header
|
|
|
|
/*
|
|
*
|
|
* Author : Vishal Gupta
|
|
* Purpose : Display Parallel Servers Statisitcs Summary
|
|
* Version : ???
|
|
* Parameters : NONE
|
|
*
|
|
*
|
|
* Revision History:
|
|
* ===================
|
|
* Date Author Description
|
|
* --------- ------------ -----------------------------------------
|
|
* 06-Sep-12 Vishal Gupta Created
|
|
*
|
|
*/
|
|
|
|
|
|
define _IF_INST1_EXISTS="--"
|
|
define _IF_INST2_EXISTS="--"
|
|
define _IF_INST3_EXISTS="--"
|
|
define _IF_INST4_EXISTS="--"
|
|
define _IF_INST5_EXISTS="--"
|
|
define _IF_INST6_EXISTS="--"
|
|
define _IF_INST7_EXISTS="--"
|
|
define _IF_INST8_EXISTS="--"
|
|
define _IF_INST9_EXISTS="--"
|
|
define _IF_INST10_EXISTS="--"
|
|
define _IF_INST11_EXISTS="--"
|
|
define _IF_INST12_EXISTS="--"
|
|
define _IF_INST13_EXISTS="--"
|
|
define _IF_INST14_EXISTS="--"
|
|
define _IF_INST15_EXISTS="--"
|
|
define _IF_INST16_EXISTS="--"
|
|
|
|
col INST1_EXISTS new_value _IF_INST1_EXISTS noprint
|
|
col INST2_EXISTS new_value _IF_INST2_EXISTS noprint
|
|
col INST3_EXISTS new_value _IF_INST3_EXISTS noprint
|
|
col INST4_EXISTS new_value _IF_INST4_EXISTS noprint
|
|
col INST5_EXISTS new_value _IF_INST5_EXISTS noprint
|
|
col INST6_EXISTS new_value _IF_INST6_EXISTS noprint
|
|
col INST7_EXISTS new_value _IF_INST7_EXISTS noprint
|
|
col INST8_EXISTS new_value _IF_INST8_EXISTS noprint
|
|
col INST9_EXISTS new_value _IF_INST9_EXISTS noprint
|
|
col INST10_EXISTS new_value _IF_INST10_EXISTS noprint
|
|
col INST11_EXISTS new_value _IF_INST11_EXISTS noprint
|
|
col INST12_EXISTS new_value _IF_INST12_EXISTS noprint
|
|
col INST13_EXISTS new_value _IF_INST13_EXISTS noprint
|
|
col INST14_EXISTS new_value _IF_INST14_EXISTS noprint
|
|
col INST15_EXISTS new_value _IF_INST15_EXISTS noprint
|
|
col INST16_EXISTS new_value _IF_INST16_EXISTS noprint
|
|
|
|
set term off
|
|
SELECT MIN(DECODE(inst_id,1,' ', '--')) INST1_EXISTS
|
|
, MIN(DECODE(inst_id,2,' ', '--')) INST2_EXISTS
|
|
, MIN(DECODE(inst_id,3,' ', '--')) INST3_EXISTS
|
|
, MIN(DECODE(inst_id,4,' ', '--')) INST4_EXISTS
|
|
, MIN(DECODE(inst_id,5,' ', '--')) INST5_EXISTS
|
|
, MIN(DECODE(inst_id,6,' ', '--')) INST6_EXISTS
|
|
, MIN(DECODE(inst_id,7,' ', '--')) INST7_EXISTS
|
|
, MIN(DECODE(inst_id,8,' ', '--')) INST8_EXISTS
|
|
, MIN(DECODE(inst_id,9,' ', '--')) INST9_EXISTS
|
|
, MIN(DECODE(inst_id,10,' ', '--')) INST10_EXISTS
|
|
, MIN(DECODE(inst_id,11,' ', '--')) INST11_EXISTS
|
|
, MIN(DECODE(inst_id,12,' ', '--')) INST12_EXISTS
|
|
, MIN(DECODE(inst_id,13,' ', '--')) INST13_EXISTS
|
|
, MIN(DECODE(inst_id,14,' ', '--')) INST14_EXISTS
|
|
, MIN(DECODE(inst_id,15,' ', '--')) INST15_EXISTS
|
|
, MIN(DECODE(inst_id,16,' ', '--')) INST16_EXISTS
|
|
FROM gv$instance
|
|
GROUP BY version
|
|
;
|
|
set term on
|
|
|
|
|
|
PROMPT
|
|
PROMPT ******************************
|
|
PROMPT * PARALLEL SERVERS SUMMARY
|
|
PROMPT ******************************
|
|
|
|
|
|
COLUMN statistic HEADING "Statistic" FORMAT a25
|
|
COLUMN all_inst HEADING "All Instance" FORMAT 99,999,999,999
|
|
COLUMN inst1_value HEADING "Instance1" FORMAT 999,999,999
|
|
COLUMN inst2_value HEADING "Instance2" FORMAT 999,999,999
|
|
COLUMN inst3_value HEADING "Instance3" FORMAT 999,999,999
|
|
COLUMN inst4_value HEADING "Instance4" FORMAT 999,999,999
|
|
COLUMN inst5_value HEADING "Instance5" FORMAT 999,999,999
|
|
COLUMN inst6_value HEADING "Instance6" FORMAT 999,999,999
|
|
COLUMN inst7_value HEADING "Instance7" FORMAT 999,999,999
|
|
COLUMN inst8_value HEADING "Instance8" FORMAT 999,999,999
|
|
COLUMN inst9_value HEADING "Instance9" FORMAT 999,999,999
|
|
COLUMN inst10_value HEADING "Instance10" FORMAT 999,999,999
|
|
COLUMN inst11_value HEADING "Instance11" FORMAT 999,999,999
|
|
COLUMN inst12_value HEADING "Instance12" FORMAT 999,999,999
|
|
COLUMN inst13_value HEADING "Instance13" FORMAT 999,999,999
|
|
COLUMN inst14_value HEADING "Instance14" FORMAT 999,999,999
|
|
COLUMN inst15_value HEADING "Instance15" FORMAT 999,999,999
|
|
COLUMN inst16_value HEADING "Instance16" FORMAT 999,999,999
|
|
|
|
SELECT TRIM(p.statistic) statistic
|
|
, SUM(p.value) all_inst
|
|
, MAX(DECODE(p.inst_id, 1, p.value,NULL)) inst1_value
|
|
, MAX(DECODE(p.inst_id, 2, p.value,NULL)) inst2_value
|
|
, MAX(DECODE(p.inst_id, 3, p.value,NULL)) inst3_value
|
|
, MAX(DECODE(p.inst_id, 4, p.value,NULL)) inst4_value
|
|
, MAX(DECODE(p.inst_id, 5, p.value,NULL)) inst5_value
|
|
, MAX(DECODE(p.inst_id, 6, p.value,NULL)) inst6_value
|
|
, MAX(DECODE(p.inst_id, 7, p.value,NULL)) inst7_value
|
|
, MAX(DECODE(p.inst_id, 8, p.value,NULL)) inst8_value
|
|
FROM gv$pq_sysstat p
|
|
GROUP BY p.statistic
|
|
ORDER BY DECODE(TRIM(p.statistic)
|
|
,'Queries Queued' ,01
|
|
,'Sessions Active' ,02
|
|
,'Servers Busy' ,03
|
|
,'Servers Idle' ,04
|
|
,'Servers Highwater' ,05
|
|
,'Servers Started' ,06
|
|
,'Servers Shutdown' ,07
|
|
,'Servers Cleaned Up' ,08
|
|
,'Server Sessions' ,09
|
|
,'Queries Initiated' ,10
|
|
,'Queries Initiated (IPQ)',11
|
|
,'DML Initiated' ,12
|
|
,'DML Initiated (IPQ)' ,13
|
|
,'DDL Initiated' ,14
|
|
,'DDL Initiated (IPQ)' ,15
|
|
,'DFO Trees' ,16
|
|
,'Local Msgs Sent' ,17
|
|
,'Local Msgs Recv''d' ,18
|
|
,'Distr Msgs Sent' ,19
|
|
,'Distr Msgs Recv''d' ,20
|
|
,99
|
|
)
|
|
/
|
|
|
|
|
|
SELECT TRIM(p.statistic) statistic
|
|
&&_IF_INST9_EXISTS , MAX(DECODE(p.inst_id, 9, p.value,NULL)) inst9_value
|
|
&&_IF_INST10_EXISTS , MAX(DECODE(p.inst_id,10, p.value,NULL)) inst10_value
|
|
&&_IF_INST11_EXISTS , MAX(DECODE(p.inst_id,11, p.value,NULL)) inst11_value
|
|
&&_IF_INST12_EXISTS , MAX(DECODE(p.inst_id,12, p.value,NULL)) inst12_value
|
|
&&_IF_INST13_EXISTS , MAX(DECODE(p.inst_id,13, p.value,NULL)) inst13_value
|
|
&&_IF_INST14_EXISTS , MAX(DECODE(p.inst_id,14, p.value,NULL)) inst14_value
|
|
&&_IF_INST15_EXISTS , MAX(DECODE(p.inst_id,15, p.value,NULL)) inst15_value
|
|
&&_IF_INST16_EXISTS , MAX(DECODE(p.inst_id,16, p.value,NULL)) inst16_value
|
|
FROM gv$pq_sysstat p
|
|
WHERE p.inst_id > 8
|
|
GROUP BY p.statistic
|
|
ORDER BY DECODE(TRIM(p.statistic)
|
|
,'Queries Queued' ,01
|
|
,'Sessions Active' ,02
|
|
,'Servers Busy' ,03
|
|
,'Servers Idle' ,04
|
|
,'Servers Highwater' ,05
|
|
,'Servers Started' ,06
|
|
,'Servers Shutdown' ,07
|
|
,'Servers Cleaned Up' ,08
|
|
,'Server Sessions' ,09
|
|
,'Queries Initiated' ,10
|
|
,'Queries Initiated (IPQ)',11
|
|
,'DML Initiated' ,12
|
|
,'DML Initiated (IPQ)' ,13
|
|
,'DDL Initiated' ,14
|
|
,'DDL Initiated (IPQ)' ,15
|
|
,'DFO Trees' ,16
|
|
,'Local Msgs Sent' ,17
|
|
,'Local Msgs Recv''d' ,18
|
|
,'Distr Msgs Sent' ,19
|
|
,'Distr Msgs Recv''d' ,20
|
|
,99
|
|
)
|
|
/
|
|
|
|
@@footer
|