在rac 系统中可以使用
gv$px_session视图来查看系统中正在运行的并行sql的一些信息,该视图的字段如下:
Column Datatype Description
INSTANCE NUMBER rac中的节点号
SADDR RAW(4 | 8) Session 地址
SID NUMBER Session identifier
SERIAL# NUMBER Session serial number
QCSID NUMBER 并行协调进程的sid
QCSERIAL# NUMBER 并行协调进程的sid的序列号
QCINST_ID NUMBER parallel coordinator所在的instance id
SERVER_GROUP NUMBER The logical group of servers to which this cluster database process belongs
SERVER_SET NUMBER The logical set of servers to which this cluster database process belongs. A single server group will have at most two server sets.
SERVER# NUMBER The logical number of the cluster database process within a server set
DEGREE NUMBER 实际是使用的并行度
REQ_DEGREE NUMBER 用户在语句中指定要求的并行度,并且优先于其他资源,用户,负载均衡要求减少的值(load balancing reductions)
主要使用的是QCSID,SID,SERIAL# 比如:其中QCSID是发起并行操作的session ID
ops$admin@yangdb>select * from gv$px_session;
INST_ID SADDR SID SERIAL# QCSID QCSERIAL# QCINST_ID SERVER_GROUP SERVER_SET SERVER# DEGREE REQ_DEGREE
---------- ---------------- ---------- ---------- ---------- ---------- ---------- ------------ ---------- ---------- ---------- ----------
1 0000000A10B86320 969 58951 1111 27913 1 1 1 1 2 2
1 0000000A58C349F8 1111 27913 1111
2 0000000A10C180C0 1298 41171 1111 27913 1 1 1 2 2 2
例子中的(1111,27913)会话执行的sql有DEGREE为2,(969,58951)在节点1 上运行,(1298,41171)在节点2上运行!
查看并行会话的等待事件:
ops$admin@yangdb>select p.inst_id,p.sid,p.qcsid,s.event,s.p1,s.p2,s.p3
2 from gv$px_session p,
3 gv$session_wait s
4 where p.qcsid=&sid
5 and s.sid=p.sid
6 and p.inst_id=s.inst_id;
Enter value for sid: 1063
old 4: where p.qcsid=&sid
new 4: where p.qcsid=1063
INST_ID SID QCSID EVENT P1 P2 P3
---------- ---------- ---------- ---------------------------------------------------------------- ---------- ---------- ----------
1 3 1063 PX Deq Credit: send blkd 268566527 1 4.4023E+10
1 246 1063 PX Deq: Execution Msg 268566527 915 4.4022E+10
1 396 1063 PX Deq: Execution Msg 268566527 912 4.4022E+10
1 1063 1063 db file sequential read 485 1900503 1
1 1498 1063 PX Deq: Execution Msg 268566527 749 4.4022E+10
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/22664653/viewspace-720663/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/22664653/viewspace-720663/