在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