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