Oracle技术之查看并行相关信息
下面我通过实验介绍三种查看并行相关内容，比如并行度、并行执行的实例等。1、V$PQ_TQSTAT视图
– 脚本
$ cat showdop.sql
column SERVER_TYPE format a15
column PROCESS format a10
select dfo_number,
tq_id,
server_type,
process,
num_rows,
bytes,
instance
from v$pq_tqstat
order by dfo_number desc,
tq_id,
server_type desc,
process
/
luocs@LUOCS11G> select /*+ parallel(2) */ count(*) from ltb1;
COUNT(*)
----------
3560448
luocs@LUOCS11G> @showdop
DFO_NUMBER TQ_ID SERVER_TYPE PROCESS NUM_ROWS BYTES INSTANCE
---------- ---------- --------------- ---------- ---------- ---------- ----------
1 0 Producer P000 1 32 1
1 0 Producer P001 1 32 1
1 0 Consumer QC 2 64 1
2、DBMS_MONITOR.CLIENT_ID_TRACE_ENABLE
luocs@WWW> BEGIN
2DBMS_SESSION.SET_IDENTIFIER('www.luocs.com');
3DBMS_MONITOR.CLIENT_ID_TRACE_ENABLE
4 (CLIENT_ID => 'www.luocs.com',
5 WAITS => TRUE
6 );
7END;
8/
PL/SQL procedure successfully completed.
luocs@WWW> select /*+ parallel(3) */ count(*) from test1;
COUNT(*)
----------
456128
luocs@WWW> exec DBMS_MONITOR.CLIENT_ID_TRACE_DISABLE('www.luocs.com');
PL/SQL procedure successfully completed.
sys@WWW> select client_identifier from v$session where sid=(select sid from v$mystat where rownum=1);
CLIENT_IDENTIFIER
----------------------------------------------------------------------------------------------------
www.luocs.com
sys@WWW> col PRIMARY_ID for a30
sys@WWW> select trace_type, primary_id, waits, binds from dba_enabled_traces;
TRACE_TYPE PRIMARY_ID WAITS BINDS
------------------------------------------ ------------------------------ ---------- ----------
CLIENT_ID www.luocs.com TRUE FALSE
$ cd /u01/app/oracle/diag/rdbms/www/ltb1/trace/
$ trcsess clientid=www.luocs.com output=luocs_test2.trc
$ ls luocs_test2.trc
-rw-r--r-- 1 oracle oinstall 103514 Jan 27 07:06 luocs_test2.trc
-- trace内容
select /*+ parallel(test 6) */ count(*) from test
END OF STMT
PARSE #47773847239296:c=4000,e=4501,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,plh=2661943167,tim=1359240732907091
WAIT #47773847239296: nam='PX Nsq: PQ load info query' ela= 200124 p1=0 p2=0 p3=0 obj#=6204 tim=1359240733107736
WAIT #47773847239296: nam='enq: PS - contention' ela= 685 name|mode=1347616774 instance=1 slave> WAIT #47773847239296: nam='KJC: Wait for msg sends to complete' ela= 223 msg=2407172024 dest|rcvr=65536 mtype=12 obj#=6204 tim=1359240733110231
WAIT #47773847239296: nam='DFS lock handle' ela= 2548 type|mode=1128857605>
WAIT #47773847239296: nam='DFS lock handle' ela= 995 type|mode=1128857605>
WAIT #47773847239296: nam='DFS lock handle' ela= 48789 type|mode=1128857605> WAIT #47773847239296: nam='enq: PI - contention' ela= 1634 name|mode=1346961409 operation=3 serial #=229371905 obj#=6204 tim=1359240733165315
WAIT #47773847239296: nam='KJC: Wait for msg sends to complete' ela= 12 msg=2407172024 dest|rcvr=65536 mtype=8 obj#=6204 tim=1359240733165376
WAIT #47773847239296: nam='enq: PS - contention' ela= 1911 name|mode=1347616774 instance=2 slave>
WAIT #47773847239296: nam='DFS lock handle' ela= 1855 type|mode=1128857605>
WAIT #47773847239296: nam='DFS lock handle' ela= 1231 type|mode=1128857605> WAIT #47773847239296: nam='KJC: Wait for msg sends to complete' ela= 12 msg=2407172024 dest|rcvr=65536 mtype=16 obj#=6204 tim=1359240733171417
WAIT #47773847239296: nam='DFS lock handle' ela= 97952 type|mode=1128857605> WAIT #47773847239296: nam='enq: PI - contention' ela= 1131 name|mode=1346961409 operation=3 serial #=229371905 obj#=6204 tim=1359240733271545
WAIT #47773847239296: nam='KJC: Wait for msg sends to complete' ela= 14 msg=2407172024 dest|rcvr=65536 mtype=8 obj#=6204 tim=1359240733271632
WAIT #47773847239296: nam='enq: PS - contention' ela= 1486 name|mode=1347616774 instance=3 slave>
WAIT #47773847239296: nam='DFS lock handle' ela= 1413 type|mode=1128857605>
WAIT #47773847239296: nam='DFS lock handle' ela= 992 type|mode=1128857605>
WAIT #47773847239296: nam='DFS lock handle' ela= 101160 type|mode=1128857605> WAIT #47773847239296: nam='enq: PI - contention' ela= 2251 name|mode=1346961409 operation=3 serial #=229371905 obj#=6204 tim=1359240733380279
WAIT #47773847239296: nam='enq: PS - contention' ela= 1448 name|mode=1347616774 instance=3 slave>
WAIT #47773847239296: nam='DFS lock handle' ela= 1823 type|mode=1128857605>
WAIT #47773847239296: nam='DFS lock handle' ela= 914 type|mode=1128857605>
WAIT #47773847239296: nam='DFS lock handle' ela= 105135 type|mode=1128857605> WAIT #47773847239296: nam='enq: PI - contention' ela= 2181 name|mode=1346961409 operation=3 serial #=229371905 obj#=6204 tim=1359240733493364
3、10391 events
luocs@LUOCS11G>>
Session> luocs@LUOCS11G> select count(*) from ltb1;
COUNT(*)
----------
3560448
luocs@LUOCS11G>>
Session> luocs@LUOCS11G> select value from v$diag_info where name = 'Default Trace File';
VALUE
----------------------------------------------------------------------------------------------------
/u01/app/oracle/diag/rdbms/luocs11g/luocs11g/trace/luocs11g_ora_3529.trc
-- trace 内容
dumping system information
arch:3 (shared disk with global cache affinity)
sess:309 myiid:1 mynid:1 ninst:1 maxiid:1, mnodes:0
Instances running on that system:
inum:0 iid:1
Created a node map for handling affinitized placement:
map:0x78f3d4 #inst:1 max:32 elem:0x4d89d560
Inst liid:0: riid:1,32- 0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 1
4, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31.
kxfrDmpUpdSys
allocated slave set: nsset:1 nbslv:2 tinst=1 ipq=0
Slave set 0: #nodes:1
Min # slaves 2: Max # slaves:2
List of Slaves:
slv:0 nid:0 instid:1 sid:00
slv:1 nid:0 instid:1 sid:01
List of Nodes:
node 0
4、_PX_TRACE
sys@WWW>>
Session> sys@WWW> select /*+ parallel(test 3) */ count(*) from test;
COUNT(*)
----------
23145
sys@WWW>>
Session> -- trace内容非常多，只抓部分并说明：
Acquired 3 slaves on 3 instances avg> P000 inst 1 spid 13085
P000 inst 2 spid 8820
P000 inst 3 spid 8758
2013-01-27 03:23:45.637836*:PX_Messaging:kxfp.c@10588:kxfpgsg():
Instance(servers):
inst=1 #slvs=1
inst=2 #slvs=1
inst=3 #slvs=1
kxfxcp1
Sending parse to nprocs:3 slave_set:1
-- 前面一些instance信息，并给出后台服务器进程以及对应的OS进程号
…
Sending parse to slave set 1:
User sqllen sent from QC = 50
select /*+ parallel(test 3) */ count(*) from test
-- 这里可以看到我们提交的语句
…
2013-01-27 03:23:46.023484*:PX_Messaging:kxfp.c@6339:kxfpGatherSlaveStats(begin):
q=0x894afcb0 qser=228533249
qref=0x894af9c8 qrser=228533249 qrseq=1 mty=16 fmh=104
qref=0x894af6e0 qrser=228533249 qrseq=1 mty=16 fmh=104
qref=0x894aee70 qrser=228533249 qrseq=1 mty=16 fmh=72
2013-01-27 03:23:46.030395*:PX_Messaging:kxfp.c@6434:kxfpGatherSlaveStats(end):
2013-01-27 03:23:46.030395*:PX_Messaging:kxfp.c@3590:kxfpqrsod():
del qref=0x894af9c8 q=0x894afcb0 qflg=400 qrser=228533249 qrseq=3 server=3.0 action=1 sopr=0
2013-01-27 03:23:46.030687*:PX_Messaging:kxfp.c@3762:kxfpqsrls():
Release Slave q=0x894afcb0 qr=0x894af9c8 action=1 server=0 inst=3
2013-01-27 03:23:46.039907*:PX_Messaging:kxfp.c@3844:kxfpqsrls(): got the enq=0x90436888 in the exclusive mode
2013-01-27 03:23:46.039907*:PX_Messaging:kxfp.c@3590:kxfpqrsod():
del qref=0x894af6e0 q=0x894afcb0 qflg=500 qrser=228533249 qrseq=3 server=2.0 action=1 sopr=0
2013-01-27 03:23:46.040262*:PX_Messaging:kxfp.c@3762:kxfpqsrls():
Release Slave q=0x894afcb0 qr=0x894af6e0 action=1 server=0 inst=2
2013-01-27 03:23:46.041056*:PX_Messaging:kxfp.c@3844:kxfpqsrls(): got the enq=0x90436ec0 in the exclusive mode
2013-01-27 03:23:46.041056*:PX_Messaging:kxfp.c@21120:kxfpIDNDeregister():
removing link for qc 0x894afcb0 sess 216
removing link 0x894b0d90 for qc 0x894afcb0 on list 0
2013-01-27 03:23:46.041056*:PX_Messaging:kxfp.c@3590:kxfpqrsod():
del qref=0x894aee70 q=0x894afcb0 qflg=500 qrser=228533249 qrseq=3 server=1.0 action=1 sopr=0
2013-01-27 03:23:46.041056*:PX_Messaging:kxfp.c@3762:kxfpqsrls():
Release Slave q=0x894afcb0 qr=0x894aee70 action=1 server=0 inst=1
2013-01-27 03:23:46.041056*:PX_Messaging:kxfp.c@3844:kxfpqsrls(): got the enq=0x90439030 in the exclusive mode
2013-01-27 03:23:46.041056*:PX_Messaging:kxfp.c@3032:kxfpqsod_qc_sod(): all slaves> 2013-01-27 03:23:46.041272*:PX_Messaging:kxfp.c@3052:kxfpqsod_qc_sod(): clean up of q=0x894afcb0 completed
-- 从这里可以看到开始并行到释放的操作
…
2013-01-27 03:23:45.362780*:PX_Messaging:kxfp.c@11434:kxfpg1srv(): trying to get slave P000 on instance 1 for q=0x894afcb0
2013-01-27 03:23:45.362780*:PX_Messaging:kxfp.c@11532:kxfpg1srv(): slave P000 is local
2013-01-27 03:23:45.362780*:PX_Messaging:kxfp.c@11562:kxfpg1srv(): found dp=0x8ab08348 flg=18
2013-01-27 03:23:45.362780*:PX_Messaging:kxfp.c@11593:kxfpg1srv(): local slave already started.. sid = 0, iid = 1
2013-01-27 03:23:45.364538*:PX_Messaging:kxfp.c@10950:kxfpg1sg(): Got It. 1 so far.
2013-01-27 03:23:45.364538*:PX_Messaging:kxfp.c@11434:kxfpg1srv(): trying to get slave P000 on instance 2 for q=0x894afcb0
2013-01-27 03:23:45.366606*:PX_Messaging:kxfp.c@11477:kxfpg1srv(): slave P000 is remote (inst=2) 2013-01-27 03:23:45.366606*:PX_Messaging:kxfp.c@11500:kxfpg1srv(): - to be started
2013-01-27 03:23:45.433069*:PX_Messaging:kxfp.c@10950:kxfpg1sg(): Got It. 2 so far.
2013-01-27 03:23:45.433069*:PX_Messaging:kxfp.c@11434:kxfpg1srv(): trying to get slave P000 on instance 3 for q=0x894afcb0
2013-01-27 03:23:45.435237*:PX_Messaging:kxfp.c@11477:kxfpg1srv(): slave P000 is remote (inst=3) 2013-01-27 03:23:45.435237*:PX_Messaging:kxfp.c@11500:kxfpg1srv(): - to be started
2013-01-27 03:23:45.537281*:PX_Messaging:kxfp.c@10950:kxfpg1sg(): Got It. 3 so far.
2013-01-27 03:23:45.537281*:PX_Messaging:kxfp.c@11210:kxfpg1sg(): need to allocate qref to slaves
2013-01-27 03:23:45.537281*:PX_Messaging:kxfp.c@11218:kxfpg1sg(): qref=0x894aee70 qref.sid=0, qref.iid=1, ser=228533249 qdp.sid=0 qdp.iid=1
2013-01-27 03:23:45.537590*:PX_Messaging:kxfp.c@11210:kxfpg1sg(): need to allocate qref to slaves
2013-01-27 03:23:45.537590*:PX_Messaging:kxfp.c@11218:kxfpg1sg(): qref=0x894af6e0 qref.sid=0, qref.iid=2, ser=228533249 qdp.sid=0 qdp.iid=2
2013-01-27 03:23:45.537722*:PX_Messaging:kxfp.c@11210:kxfpg1sg(): need to allocate qref to slaves
2013-01-27 03:23:45.537722*:PX_Messaging:kxfp.c@11218:kxfpg1sg(): qref=0x894af9c8 qref.sid=0, qref.iid=3, ser=228533249 qdp.sid=0 qdp.iid=3
2013-01-27 03:23:45.537945*:PX_Messaging:kxfp.c@11298:kxfpg1sg(): wait reply from qref 0x894aee70
2013-01-27 03:23:45.540818*:PX_Messaging:kxfp.c@11307:kxfpg1sg(): received reply from qref 0x894aee70
2013-01-27 03:23:45.540818*:PX_Messaging:kxfp.c@11298:kxfpg1sg(): wait reply from qref 0x894af6e0
2013-01-27 03:23:45.571801*:PX_Messaging:kxfp.c@11307:kxfpg1sg(): received reply from qref 0x894af6e0
2013-01-27 03:23:45.571801*:PX_Messaging:kxfp.c@11298:kxfpg1sg(): wait reply from qref 0x894af9c8
2013-01-27 03:23:45.637836*:PX_Messaging:kxfp.c@11307:kxfpg1sg(): received reply from qref 0x894af9c8
Acquired 3 slaves on 3 instances avg> P000 inst 1 spid 13085
P000 inst 2 spid 8820
P000 inst 3 spid 8758
2013-01-27 03:23:45.637836*:PX_Messaging:kxfp.c@10588:kxfpgsg():
Instance(servers):
inst=1 #slvs=1
inst=2 #slvs=1
inst=3 #slvs=1
-- 从以上信息可以看到获取到的并行进程信息，3个实例各自获得一个。
-- 下面再看看并行度为6的情况
sys@WWW>>
Session> sys@WWW> select /*+ parallel(test 6) */ count(*) from test;
COUNT(*)
----------
23145
sys@WWW>>
Session> sys@WWW> @showdop
DFO_NUMBER TQ_ID SERVER_TYPE PROCESS NUM_ROWS BYTES INSTANCE
---------- ---------- --------------- ---------- ---------- ---------- ----------
1 0 Producer P000 1 36 3
1 0 Producer P000 1 36 1
1 0 Producer P001 1 36 1
1 0 Producer P001 1 36 3
1 0 Producer P002 1 36 3
1 0 Producer P002 1 36 1
1 0 Consumer QC 6 216 1
7 rows selected.
-- 可见参与工作的只有2个实例
-- 然后我们从trace里也可以看到获取到的并行进程信息：
2013-01-27 03:40:49.503057*:PX_Messaging:kxfp.c@11434:kxfpg1srv(): trying to get slave P000 on instance 1 for q=0x894b10d0
2013-01-27 03:40:49.505184*:PX_Messaging:kxfp.c@11532:kxfpg1srv(): slave P000 is local
2013-01-27 03:40:49.505184*:PX_Messaging:kxfp.c@11562:kxfpg1srv(): found dp=0x8ab09938 flg=0
2013-01-27 03:40:49.505184*:PX_Messaging:kxfp.c@12939:kxfpcrel():
Creating local slave 0 flg=30
2013-01-27 03:40:49.505184*:PX_Messaging:kxfp.c@12722:kxfpcre1(): Creating slave 0 flg:30
2013-01-27 03:40:49.505184*:PX_Messaging:kxfp.c@12772:kxfpcre1(): free descriptor found dp=0x8ab08348
2013-01-27 03:40:49.505184*:PX_Messaging:kxfp.c@12857:kxfpcre1(): Allocated slave P000 dp=0x8ab08348 flg=4
2013-01-27 03:40:49.546771*:PX_Messaging:kxfp.c@10950:kxfpg1sg(): Got It. 1 so far.
2013-01-27 03:40:49.546771*:PX_Messaging:kxfp.c@11434:kxfpg1srv(): trying to get slave P001 on instance 1 for q=0x894b10d0
2013-01-27 03:40:49.546771*:PX_Messaging:kxfp.c@11532:kxfpg1srv(): slave P001 is local
2013-01-27 03:40:49.546771*:PX_Messaging:kxfp.c@11562:kxfpg1srv(): found dp=0x8ab09938 flg=0
2013-01-27 03:40:49.546771*:PX_Messaging:kxfp.c@12939:kxfpcrel():
Creating local slave 1 flg=30
2013-01-27 03:40:49.546771*:PX_Messaging:kxfp.c@12722:kxfpcre1(): Creating slave 1 flg:30
2013-01-27 03:40:49.546771*:PX_Messaging:kxfp.c@12772:kxfpcre1(): free descriptor found dp=0x8ab083d8
2013-01-27 03:40:49.546771*:PX_Messaging:kxfp.c@12857:kxfpcre1(): Allocated slave P001 dp=0x8ab083d8 flg=4
*** 2013-01-27 03:40:49.600
2013-01-27 03:40:49.599941*:PX_Messaging:kxfp.c@10950:kxfpg1sg(): Got It. 2 so far.
2013-01-27 03:40:49.599941*:PX_Messaging:kxfp.c@11434:kxfpg1srv(): trying to get slave P002 on instance 1 for q=0x894b10d0
2013-01-27 03:40:49.600886*:PX_Messaging:kxfp.c@11532:kxfpg1srv(): slave P002 is local
2013-01-27 03:40:49.600886*:PX_Messaging:kxfp.c@11562:kxfpg1srv(): found dp=0x8ab09938 flg=0
2013-01-27 03:40:49.600886*:PX_Messaging:kxfp.c@12939:kxfpcrel():
Creating local slave 2 flg=30
2013-01-27 03:40:49.600886*:PX_Messaging:kxfp.c@12722:kxfpcre1(): Creating slave 2 flg:30
2013-01-27 03:40:49.600886*:PX_Messaging:kxfp.c@12772:kxfpcre1(): free descriptor found dp=0x8ab08468
2013-01-27 03:40:49.600886*:PX_Messaging:kxfp.c@12857:kxfpcre1(): Allocated slave P002 dp=0x8ab08468 flg=4
2013-01-27 03:40:49.659212*:PX_Messaging:kxfp.c@10950:kxfpg1sg(): Got It. 3 so far.
2013-01-27 03:40:49.659212*:PX_Messaging:kxfp.c@11434:kxfpg1srv(): trying to get slave P000 on instance 3 for q=0x894b10d0
2013-01-27 03:40:49.662577*:PX_Messaging:kxfp.c@11477:kxfpg1srv(): slave P000 is remote (inst=3) 2013-01-27 03:40:49.662577*:PX_Messaging:kxfp.c@11500:kxfpg1srv(): - to be started
2013-01-27 03:40:49.829562*:PX_Messaging:kxfp.c@10950:kxfpg1sg(): Got It. 4 so far.
2013-01-27 03:40:49.829562*:PX_Messaging:kxfp.c@11434:kxfpg1srv(): trying to get slave P001 on instance 3 for q=0x894b10d0
2013-01-27 03:40:49.832446*:PX_Messaging:kxfp.c@11477:kxfpg1srv(): slave P001 is remote (inst=3) 2013-01-27 03:40:49.832446*:PX_Messaging:kxfp.c@11500:kxfpg1srv(): - to be started
2013-01-27 03:40:50.011707*:PX_Messaging:kxfp.c@10950:kxfpg1sg(): Got It. 5 so far.
2013-01-27 03:40:50.011707*:PX_Messaging:kxfp.c@11434:kxfpg1srv(): trying to get slave P002 on instance 3 for q=0x894b10d0
2013-01-27 03:40:50.013168*:PX_Messaging:kxfp.c@11477:kxfpg1srv(): slave P002 is remote (inst=3) 2013-01-27 03:40:50.013168*:PX_Messaging:kxfp.c@11500:kxfpg1srv(): - to be started
2013-01-27 03:40:50.188632*:PX_Messaging:kxfp.c@10950:kxfpg1sg(): Got It. 6 so far.
2013-01-27 03:40:50.188632*:PX_Messaging:kxfp.c@11210:kxfpg1sg(): need to allocate qref to slaves
2013-01-27 03:40:50.188632*:PX_Messaging:kxfp.c@11218:kxfpg1sg(): qref=0x894ace68 qref.sid=0, qref.iid=1, ser=228594689 qdp.sid=0 qdp.iid=1
qref=0x894ace68 qrser=228594689 qrseq=0 mty=7 fmh=0
2013-01-27 03:40:50.188860*:PX_Messaging:kxfp.c@11210:kxfpg1sg(): need to allocate qref to slaves
2013-01-27 03:40:50.188860*:PX_Messaging:kxfp.c@11218:kxfpg1sg(): qref=0x894b0df8 qref.sid=1, qref.iid=1, ser=228594689 qdp.sid=1 qdp.iid=1
qref=0x894b0df8 qrser=228594689 qrseq=0 mty=7 fmh=0
2013-01-27 03:40:50.188938*:PX_Messaging:kxfp.c@11210:kxfpg1sg(): need to allocate qref to slaves
2013-01-27 03:40:50.188938*:PX_Messaging:kxfp.c@11218:kxfpg1sg(): qref=0x894af9c8 qref.sid=2, qref.iid=1, ser=228594689 qdp.sid=2 qdp.iid=1
qref=0x894af9c8 qrser=228594689 qrseq=0 mty=7 fmh=0
2013-01-27 03:40:50.189010*:PX_Messaging:kxfp.c@11210:kxfpg1sg(): need to allocate qref to slaves
adding link 0x894aee38 for qc 0x894b10d0 on list 0
2013-01-27 03:40:50.189010*:PX_Messaging:kxfp.c@11218:kxfpg1sg(): qref=0x894af6e0 qref.sid=0, qref.iid=3, ser=228594689 qdp.sid=0 qdp.iid=3
qref=0x894af6e0 qrser=228594689 qrseq=0 mty=7 fmh=0
2013-01-27 03:40:50.189104*:PX_Messaging:kxfp.c@11210:kxfpg1sg(): need to allocate qref to slaves
2013-01-27 03:40:50.189104*:PX_Messaging:kxfp.c@11218:kxfpg1sg(): qref=0x894aee70 qref.sid=1, qref.iid=3, ser=228594689 qdp.sid=1 qdp.iid=3
qref=0x894aee70 qrser=228594689 qrseq=0 mty=7 fmh=0
2013-01-27 03:40:50.189405*:PX_Messaging:kxfp.c@11210:kxfpg1sg(): need to allocate qref to slaves
2013-01-27 03:40:50.189405*:PX_Messaging:kxfp.c@11218:kxfpg1sg(): qref=0x894aeb88 qref.sid=2, qref.iid=3, ser=228594689 qdp.sid=2 qdp.iid=3
qref=0x894aeb88 qrser=228594689 qrseq=0 mty=7 fmh=0
2013-01-27 03:40:50.189632*:PX_Messaging:kxfp.c@11298:kxfpg1sg(): wait reply from qref 0x894ace68
2013-01-27 03:40:50.208868*:PX_Messaging:kxfp.c@11307:kxfpg1sg(): received reply from qref 0x894ace68
2013-01-27 03:40:50.208868*:PX_Messaging:kxfp.c@11298:kxfpg1sg(): wait reply from qref 0x894b0df8
2013-01-27 03:40:50.208868*:PX_Messaging:kxfp.c@11307:kxfpg1sg(): received reply from qref 0x894b0df8
2013-01-27 03:40:50.208868*:PX_Messaging:kxfp.c@11298:kxfpg1sg(): wait reply from qref 0x894af9c8
2013-01-27 03:40:50.208868*:PX_Messaging:kxfp.c@11307:kxfpg1sg(): received reply from qref 0x894af9c8
2013-01-27 03:40:50.208868*:PX_Messaging:kxfp.c@11298:kxfpg1sg(): wait reply from qref 0x894af6e0
2013-01-27 03:40:50.213193*:PX_Messaging:kxfp.c@11307:kxfpg1sg(): received reply from qref 0x894af6e0
2013-01-27 03:40:50.213193*:PX_Messaging:kxfp.c@11298:kxfpg1sg(): wait reply from qref 0x894aee70
2013-01-27 03:40:50.213193*:PX_Messaging:kxfp.c@11307:kxfpg1sg(): received reply from qref 0x894aee70
2013-01-27 03:40:50.213193*:PX_Messaging:kxfp.c@11298:kxfpg1sg(): wait reply from qref 0x894aeb88
2013-01-27 03:40:50.215938*:PX_Messaging:kxfp.c@11307:kxfpg1sg(): received reply from qref 0x894aeb88
Acquired 6 slaves on 2 instances avg> P000 inst 1 spid 20964
P001 inst 1 spid 20966
P002 inst 1 spid 20968
P000 inst 3 spid 9599
P001 inst 3 spid 9602
P002 inst 3 spid 9604
2013-01-27 03:40:50.215938*:PX_Messaging:kxfp.c@10588:kxfpgsg():
Instance(servers):
inst=1 #slvs=3
inst=3 #slvs=3
inst=2 #slvs=0
… 部分省略
2013-01-27 03:40:50.395810*:PX_Messaging:kxfp.c@3292:kxfpqsod():
Query end, buffer cache support for numa enabled: YES
2013-01-27 03:40:50.395810*:PX_Messaging:kxfp.c@3296:kxfpqsod(end):
2013-01-27 03:40:50.395810*:PX_Messaging:kxfp.c@3229:kxfpqsod(begin):
q=0x894b10d0 qser=228594689 action=0x1 flg=0x400
2013-01-27 03:40:50.395810*:PX_Messaging:kxfp.c@2841:kxfpqsod_qc_sod(): q=0x894b10d0 qser=228594689 qinc=1 action=0x1 flg=0x400
2013-01-27 03:40:50.398517*:PX_Messaging:kxfp.c@6339:kxfpGatherSlaveStats(begin):
q=0x894b10d0 qser=228594689
qref=0x894aeb88 qrser=228594689 qrseq=1 mty=16 fmh=104
qref=0x894aee70 qrser=228594689 qrseq=1 mty=16 fmh=104
qref=0x894af6e0 qrser=228594689 qrseq=1 mty=16 fmh=104
qref=0x894af9c8 qrser=228594689 qrseq=1 mty=16 fmh=72
qref=0x894b0df8 qrser=228594689 qrseq=1 mty=16 fmh=72
qref=0x894ace68 qrser=228594689 qrseq=1 mty=16 fmh=72
2013-01-27 03:40:50.408353*:PX_Messaging:kxfp.c@6434:kxfpGatherSlaveStats(end):
2013-01-27 03:40:50.408353*:PX_Messaging:kxfp.c@3590:kxfpqrsod():
del qref=0x894aeb88 q=0x894b10d0 qflg=400 qrser=228594689 qrseq=3 server=3.2 action=1 sopr=0
2013-01-27 03:40:50.408508*:PX_Messaging:kxfp.c@3762:kxfpqsrls():
Release Slave q=0x894b10d0 qr=0x894aeb88 action=1 server=2 inst=3
2013-01-27 03:40:50.411394*:PX_Messaging:kxfp.c@3844:kxfpqsrls(): got the enq=0x90436de0 in the exclusive mode
2013-01-27 03:40:50.411394*:PX_Messaging:kxfp.c@3590:kxfpqrsod():
del qref=0x894aee70 q=0x894b10d0 qflg=500 qrser=228594689 qrseq=3 server=3.1 action=1 sopr=0
2013-01-27 03:40:50.411560*:PX_Messaging:kxfp.c@3762:kxfpqsrls():
Release Slave q=0x894b10d0 qr=0x894aee70 action=1 server=1 inst=3
2013-01-27 03:40:50.413609*:PX_Messaging:kxfp.c@3844:kxfpqsrls(): got the enq=0x90436968 in the exclusive mode
2013-01-27 03:40:50.413609*:PX_Messaging:kxfp.c@3590:kxfpqrsod():
del qref=0x894af6e0 q=0x894b10d0 qflg=500 qrser=228594689 qrseq=3 server=3.0 action=1 sopr=0
2013-01-27 03:40:50.413679*:PX_Messaging:kxfp.c@3762:kxfpqsrls():
Release Slave q=0x894b10d0 qr=0x894af6e0 action=1 server=0 inst=3
2013-01-27 03:40:50.415272*:PX_Messaging:kxfp.c@3844:kxfpqsrls(): got the enq=0x90436b28 in the exclusive mode
2013-01-27 03:40:50.415272*:PX_Messaging:kxfp.c@21120:kxfpIDNDeregister():
removing link for qc 0x894b10d0 sess 216
removing link 0x894aee38 for qc 0x894b10d0 on list 0
2013-01-27 03:40:50.415272*:PX_Messaging:kxfp.c@3590:kxfpqrsod():
del qref=0x894af9c8 q=0x894b10d0 qflg=500 qrser=228594689 qrseq=3 server=1.2 action=1 sopr=0
2013-01-27 03:40:50.415272*:PX_Messaging:kxfp.c@3762:kxfpqsrls():
Release Slave q=0x894b10d0 qr=0x894af9c8 action=1 server=2 inst=1
2013-01-27 03:40:50.415272*:PX_Messaging:kxfp.c@3844:kxfpqsrls(): got the enq=0x90436ec0 in the exclusive mode
2013-01-27 03:40:50.415272*:PX_Messaging:kxfp.c@3590:kxfpqrsod():
del qref=0x894b0df8 q=0x894b10d0 qflg=500 qrser=228594689 qrseq=3 server=1.1 action=1 sopr=0
2013-01-27 03:40:50.415272*:PX_Messaging:kxfp.c@3762:kxfpqsrls():
Release Slave q=0x894b10d0 qr=0x894b0df8 action=1 server=1 inst=1
2013-01-27 03:40:50.415272*:PX_Messaging:kxfp.c@3844:kxfpqsrls(): got the enq=0x90436508 in the exclusive mode
2013-01-27 03:40:50.415272*:PX_Messaging:kxfp.c@3590:kxfpqrsod():
del qref=0x894ace68 q=0x894b10d0 qflg=500 qrser=228594689 qrseq=3 server=1.0 action=1 sopr=0
2013-01-27 03:40:50.415272*:PX_Messaging:kxfp.c@3762:kxfpqsrls():
Release Slave q=0x894b10d0 qr=0x894ace68 action=1 server=0 inst=1
2013-01-27 03:40:50.415272*:PX_Messaging:kxfp.c@3844:kxfpqsrls(): got the enq=0x90439030 in the exclusive mode
2013-01-27 03:40:50.415272*:PX_Messaging:kxfp.c@3032:kxfpqsod_qc_sod(): all slaves> 2013-01-27 03:40:50.415505*:PX_Messaging:kxfp.c@3052:kxfpqsod_qc_sod(): clean up of q=0x894b10d0 completed
-- 我们将parallel_force_local设置为true，再看看结果：
sys@WWW>>
Session>
sys@WWW>>
Table>
sys@WWW>>
Session> sys@WWW> select count(*) from test;
COUNT(*)
----------
23145
sys@WWW>>
Session> sys@WWW> @showdop
DFO_NUMBER TQ_ID SERVER_TYPE PROCESS NUM_ROWS BYTES INSTANCE
---------- ---------- --------------- ---------- ---------- ---------- ----------
1 0 Producer P000 1 36 1
1 0 Producer P001 1 36 1
1 0 Producer P002 1 36 1
1 0 Consumer QC 3 108 1
-- 可见只有本地的实例才参与并行工作
2013-01-27 03:47:24.187691*:PX_Messaging:kxfp.c@11434:kxfpg1srv(): trying to get slave P000 on instance 1 for q=0x894acbc8
2013-01-27 03:47:24.187691*:PX_Messaging:kxfp.c@11532:kxfpg1srv(): slave P000 is local
2013-01-27 03:47:24.187691*:PX_Messaging:kxfp.c@11562:kxfpg1srv(): found dp=0x8ab09938 flg=0
2013-01-27 03:47:24.187691*:PX_Messaging:kxfp.c@12939:kxfpcrel():
Creating local slave 0 flg=34
2013-01-27 03:47:24.187691*:PX_Messaging:kxfp.c@12722:kxfpcre1(): Creating slave 0 flg:34
2013-01-27 03:47:24.187691*:PX_Messaging:kxfp.c@12772:kxfpcre1(): free descriptor found dp=0x8ab08348
2013-01-27 03:47:24.187691*:PX_Messaging:kxfp.c@12857:kxfpcre1(): Allocated slave P000 dp=0x8ab08348 flg=4
2013-01-27 03:47:24.251243*:PX_Messaging:kxfp.c@10950:kxfpg1sg(): Got It. 1 so far.
2013-01-27 03:47:24.251243*:PX_Messaging:kxfp.c@11434:kxfpg1srv(): trying to get slave P001 on instance 1 for q=0x894acbc8
2013-01-27 03:47:24.251489*:PX_Messaging:kxfp.c@11532:kxfpg1srv(): slave P001 is local
2013-01-27 03:47:24.251489*:PX_Messaging:kxfp.c@11562:kxfpg1srv(): found dp=0x8ab09938 flg=0
2013-01-27 03:47:24.251489*:PX_Messaging:kxfp.c@12939:kxfpcrel():
Creating local slave 1 flg=34
2013-01-27 03:47:24.251489*:PX_Messaging:kxfp.c@12722:kxfpcre1(): Creating slave 1 flg:34
2013-01-27 03:47:24.251489*:PX_Messaging:kxfp.c@12772:kxfpcre1(): free descriptor found dp=0x8ab083d8
2013-01-27 03:47:24.251489*:PX_Messaging:kxfp.c@12857:kxfpcre1(): Allocated slave P001 dp=0x8ab083d8 flg=4
2013-01-27 03:47:24.306473*:PX_Messaging:kxfp.c@10950:kxfpg1sg(): Got It. 2 so far.
2013-01-27 03:47:24.306473*:PX_Messaging:kxfp.c@11434:kxfpg1srv(): trying to get slave P002 on instance 1 for q=0x894acbc8
2013-01-27 03:47:24.306473*:PX_Messaging:kxfp.c@11532:kxfpg1srv(): slave P002 is local
2013-01-27 03:47:24.306473*:PX_Messaging:kxfp.c@11562:kxfpg1srv(): found dp=0x8ab09938 flg=0
2013-01-27 03:47:24.306473*:PX_Messaging:kxfp.c@12939:kxfpcrel():
Creating local slave 2 flg=34
2013-01-27 03:47:24.306473*:PX_Messaging:kxfp.c@12722:kxfpcre1(): Creating slave 2 flg:34
2013-01-27 03:47:24.306473*:PX_Messaging:kxfp.c@12772:kxfpcre1(): free descriptor found dp=0x8ab08468
2013-01-27 03:47:24.306473*:PX_Messaging:kxfp.c@12857:kxfpcre1(): Allocated slave P002 dp=0x8ab08468 flg=4
2013-01-27 03:47:24.369114*:PX_Messaging:kxfp.c@10950:kxfpg1sg(): Got It. 3 so far.
2013-01-27 03:47:24.369114*:PX_Messaging:kxfp.c@11210:kxfpg1sg(): need to allocate qref to slaves
2013-01-27 03:47:24.369114*:PX_Messaging:kxfp.c@11218:kxfpg1sg(): qref=0x894af158 qref.sid=0, qref.iid=1, ser=228614657 qdp.sid=0 qdp.iid=1
qref=0x894af158 qrser=228614657 qrseq=0 mty=7 fmh=0
2013-01-27 03:47:24.369453*:PX_Messaging:kxfp.c@11210:kxfpg1sg(): need to allocate qref to slaves
2013-01-27 03:47:24.369453*:PX_Messaging:kxfp.c@11218:kxfpg1sg(): qref=0x894b0ae0 qref.sid=1, qref.iid=1, ser=228614657 qdp.sid=1 qdp.iid=1
qref=0x894b0ae0 qrser=228614657 qrseq=0 mty=7 fmh=0
2013-01-27 03:47:24.369555*:PX_Messaging:kxfp.c@11210:kxfpg1sg(): need to allocate qref to slaves
2013-01-27 03:47:24.369555*:PX_Messaging:kxfp.c@11218:kxfpg1sg(): qref=0x894aeb88 qref.sid=2, qref.iid=1, ser=228614657 qdp.sid=2 qdp.iid=1
qref=0x894aeb88 qrser=228614657 qrseq=0 mty=7 fmh=0
2013-01-27 03:47:24.369625*:PX_Messaging:kxfp.c@11298:kxfpg1sg(): wait reply from qref 0x894af158
2013-01-27 03:47:24.373707*:PX_Messaging:kxfp.c@11307:kxfpg1sg(): received reply from qref 0x894af158
2013-01-27 03:47:24.373707*:PX_Messaging:kxfp.c@11298:kxfpg1sg(): wait reply from qref 0x894b0ae0
2013-01-27 03:47:24.381890*:PX_Messaging:kxfp.c@11307:kxfpg1sg(): received reply from qref 0x894b0ae0
2013-01-27 03:47:24.381890*:PX_Messaging:kxfp.c@11298:kxfpg1sg(): wait reply from qref 0x894aeb88
2013-01-27 03:47:24.381890*:PX_Messaging:kxfp.c@11307:kxfpg1sg(): received reply from qref 0x894aeb88
Acquired 3 slaves on 1 instances avg> P000 inst 1 spid 22504
P001 inst 1 spid 22506
P002 inst 1 spid 22508
2013-01-27 03:47:24.381890*:PX_Messaging:kxfp.c@10588:kxfpgsg():
Instance(servers):
inst=1 #slvs=3
-- 注意上面信息中slave P000、P001、P002 is local，而如果不激活parallel_force_local参数，我们将看到类似slave P001 is remote (inst=3)的信息。
oracle视频教程请关注:http://u.youku.com/user_video/id_UMzAzMjkxMjE2.html
页:
[1]