在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)
<b>主要使用的是QCSID,SID,SERIAL# 比如:其中QCSID是發起并行操作的session ID</b>
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