The following query shows the current wait state of each slave (child process) and query coordinator process on the system:
SELECT px.SID "SID", p.PID, p.SPID "SPID", px.INST_ID "Inst",
px.SERVER_GROUP "Group", px.SERVER_SET "Set",
px.DEGREE "Degree", px.REQ_DEGREE "Req Degree", w.event "Wait Event"
FROM GV$SESSION s, GV$PX_SESSION px, GV$PROCESS p, GV$SESSION_WAIT w
WHERE s.sid (+) = px.sid AND s.inst_id (+) = px.inst_id AND
s.sid = w.sid (+) AND s.inst_id = w.inst_id (+) AND
s.paddr = p.addr (+) AND s.inst_id = p.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;
参考:http://docs.oracle.com/cd/E11882_01/server.112/e25523/parallel006.htm#i1008510
语句说明:
order by 子句里含有decode函数时,decode函数里的第一个参数,是order by排序时要针对的字段(对象,对哪些字段进行排序操作),而decode函数则是影响order by排序时数据行的顺序。例如,
表A的内容如下:
Aa Ab
-------- ----------
1 19
null 2
4 10
3 1
则
SQL>select * from A order by Aa;
Aa Ab
-------- ----------
1 19
3 1
4 10
null 2
SQL>select * from A ORDER BY DECODE(Aa, NULL,Ab);
Aa Ab
-------- ----------
1 19
null 2
3 1
4 10
decode用法详见:
Oracle DECODE函数的语法介绍以及使用技巧