脚本:监控并行进程状态

脚本正文:

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 

Comments

  1. 赞一个!!

  2. 我也想了解,请大家都说说

Comment

*

沪ICP备14014813号

沪公网安备 31010802001379号