脚本:监控并行进程状态

脚本正文:

col username for a12
col "QC SID" for A6
col "SID" for A6
col "QC/Slave" for A8
col "Req. DOP" for 9999
col "Actual DOP" for 9999
col "Slaveset" for A8
col "Slave INST" for A9
col "QC INST" for A6
set pages 300 lines 300
col wait_event format a30
select
decode(px.qcinst_id,NULL,username, 
' - '||lower(substr(pp.SERVER_NAME,
length(pp.SERVER_NAME)-4,4) ) )"Username",
decode(px.qcinst_id,NULL, 'QC', '(Slave)') "QC/Slave" ,
to_char( px.server_set) "SlaveSet",
to_char(s.sid) "SID",
to_char(px.inst_id) "Slave INST",
decode(sw.state,'WAITING', 'WAIT', 'NOT WAIT' ) as STATE,     
case  sw.state WHEN 'WAITING' THEN substr(sw.event,1,30) ELSE NULL end as wait_event ,
decode(px.qcinst_id, NULL ,to_char(s.sid) ,px.qcsid) "QC SID",
to_char(px.qcinst_id) "QC INST",
px.req_degree "Req. DOP",
px.degree "Actual DOP"
from gv$px_session px,
gv$session s ,
gv$px_process pp,
gv$session_wait sw
where px.sid=s.sid (+)
and px.serial#=s.serial#(+)
and px.inst_id = s.inst_id(+)
and px.sid = pp.sid (+)
and px.serial#=pp.serial#(+)
and sw.sid = s.sid  
and sw.inst_id = s.inst_id   
order by
  decode(px.QCINST_ID,  NULL, px.INST_ID,  px.QCINST_ID),
  px.QCSID,
  decode(px.SERVER_GROUP, NULL, 0, px.SERVER_GROUP), 
  px.SERVER_SET, 
  px.INST_ID
/

set pages 300 lines 300
col wait_event format a30
select 
  sw.SID as RCVSID,
  decode(pp.server_name, 
         NULL, 'A QC', 
         pp.server_name) as RCVR,
  sw.inst_id as RCVRINST,
case  sw.state WHEN 'WAITING' THEN substr(sw.event,1,30) ELSE NULL end as wait_event ,
  decode(bitand(p1, 65535),
         65535, 'QC', 
         'P'||to_char(bitand(p1, 65535),'fm000')) as SNDR,
  bitand(p1, 16711680) - 65535 as SNDRINST,
  decode(bitand(p1, 65535),
         65535, ps.qcsid,
         (select 
            sid 
          from 
            gv$px_process 
          where 
            server_name = 'P'||to_char(bitand(sw.p1, 65535),'fm000') and
            inst_id = bitand(sw.p1, 16711680) - 65535)
        ) as SNDRSID,
   decode(sw.state,'WAITING', 'WAIT', 'NOT WAIT' ) as STATE     
from 
  gv$session_wait sw,
  gv$px_process pp,
  gv$px_session ps
where
  sw.sid = pp.sid (+) and
  sw.inst_id = pp.inst_id (+) and 
  sw.sid = ps.sid (+) and
  sw.inst_id = ps.inst_id (+) and 
  p1text  = 'sleeptime/senderid' and
  bitand(p1, 268435456) = 268435456
order by
  decode(ps.QCINST_ID,  NULL, ps.INST_ID,  ps.QCINST_ID),
  ps.QCSID,
  decode(ps.SERVER_GROUP, NULL, 0, ps.SERVER_GROUP), 
  ps.SERVER_SET, 
  ps.INST_ID
/



set pages 300 lines 300
col "Username" for a12
col "QC/Slave" for A8
col "Slaveset" for A8
col "Slave INST" for A9
col "QC SID" for A6
col "QC INST" for A6
col "operation_name" for A30
col "target" for A30

select
decode(px.qcinst_id,NULL,username, 
' - '||lower(substr(pp.SERVER_NAME,
length(pp.SERVER_NAME)-4,4) ) )"Username",
decode(px.qcinst_id,NULL, 'QC', '(Slave)') "QC/Slave" ,
to_char( px.server_set) "SlaveSet",
to_char(px.inst_id) "Slave INST",
substr(opname,1,30)  operation_name,
substr(target,1,30) target,
sofar,
totalwork,
units,
start_time,
timestamp,
decode(px.qcinst_id, NULL ,to_char(s.sid) ,px.qcsid) "QC SID",
to_char(px.qcinst_id) "QC INST"
from gv$px_session px,
gv$px_process pp,
gv$session_longops s 
where px.sid=s.sid 
and px.serial#=s.serial#
and px.inst_id = s.inst_id
and px.sid = pp.sid (+)
and px.serial#=pp.serial#(+)
order by
  decode(px.QCINST_ID,  NULL, px.INST_ID,  px.QCINST_ID),
  px.QCSID,
  decode(px.SERVER_GROUP, NULL, 0, px.SERVER_GROUP), 
  px.SERVER_SET, 
  px.INST_ID
/ 

示例输出:

Username     QC/Slave SlaveSet SID    Slave INS STATE    WAIT_EVENT                     QC SID QC INS Req. DOP Actual DOP
------------ -------- -------- ------ --------- -------- ------------------------------ ------ ------ -------- ---------- 
SCOTT      QC                923    1         WAIT     db file sequential read        923
 - p003      (Slave)  1        935    1         WAIT     PX Deq Credit: send blkd       923    1             4          4
 - p001      (Slave)  1        961    1         WAIT     PX Deq: Execution Msg          923    1             4          4
 - p002      (Slave)  1        1035   1         WAIT     PX Deq: Execution Msg          923    1             4          4
 - p004      (Slave)  1        977    1         WAIT     PX Deq Credit: send blkd       923    1             4          4
 - p006      (Slave)  2        609    1         WAIT     PX Deq: Execution Msg          923    1             4          4
 - p007      (Slave)  2        642    1         WAIT     PX Deq: Execution Msg          923    1             4          4
 - p008      (Slave)  2        970    1         WAIT     PX Deq: Execution Msg          923    1             4          4
 - p005      (Slave)  2        953    1         WAIT     PX Deq: Execution Msg          923    1             4          4
SCOTT      QC                1003   1         WAIT     SQL*Net message from client    1003
 - p015      (Slave)  1        608    1         WAIT     PX Deq Credit: send blkd       1003   1             8          8
 - p011      (Slave)  1        639    1         WAIT     PX Deq Credit: send blkd       1003   1             8          8
 - p012      (Slave)  1        1115   1         WAIT     PX Deq: Execution Msg          1003   1             8          8
 - p000      (Slave)  1        1253   1         WAIT     PX Deq Credit: send blkd       1003   1             8          8
 - p010      (Slave)  1        1420   1         WAIT     PX Deq: Execution Msg          1003   1             8          8
 - p009      (Slave)  1        1421   1         WAIT     PX Deq Credit: send blkd       1003   1             8          8
 - p014      (Slave)  1        1417   1         WAIT     PX Deq: Execution Msg          1003   1             8          8
 - p013      (Slave)  1        1180   1         WAIT     PX Deq: Execution Msg          1003   1             8          8
 - p020      (Slave)  2        1422   1         WAIT     PX Deq: Execution Msg          1003   1             8          8
 - p023      (Slave)  2        1423   1         WAIT     PX Deq: Execution Msg          1003   1             8          8
 - p018      (Slave)  2        1424   1         WAIT     PX Deq: Execution Msg          1003   1             8          8
 - p021      (Slave)  2        1426   1         WAIT     PX Deq: Execution Msg          1003   1             8          8
 - p019      (Slave)  2        1428   1         WAIT     PX Deq: Execution Msg          1003   1             8          8
 - p016      (Slave)  2        1429   1         WAIT     PX Deq: Execution Msg          1003   1             8          8
 - p022      (Slave)  2        1427   1         WAIT     PX Deq: Execution Msg          1003   1             8          8
 - p017      (Slave)  2        1425   1         WAIT     PX Deq: Execution Msg          1003   1             8          8 

沪ICP备14014813号-2

沪公网安备 31010802001379号