I found this was interesting with _px_trace gave some insight to the DOP and its funtionality. This example will generate a trace in the user_dump_dest. This query was executed on the two node cluster, on 11.2.0.3
SQL> create table t1(n number);
SQL> insert into t1 values(100);
SQL> insert into t1 values(100);
SQL> insert into t1 values(100);
SQL> commit;
SQL> alter session set "_px_trace"="compilation","execution","messaging";
SQL> alter table t1 parallel 2;
SQL> select count(*) from t1;
DOP goes with PX Blackbox trace dump as shown in below trace output.
<= COMMENTS are given with this symbol, lets rock!
-----------trace output-----------
2012-12-12 21:28:35.079510*:PX_Messaging:kxfp.c@19122:kxfpldbl():
compute default DOP = 512 (from kxfrDefaultDOP()) for load balancing <= Computes load allocation before load balance
pre ldbl state, instance #: 1, instance_load: 0.000000 <= Load balance on inst1
pre ldbl state, instance #: 2, instance_load: 0.187500 <= Load balance on inst2
var=2 limit=512 use_aff=0 aff_num=0 unit=16
Slave allocation unit=16 CPU allocation unit=16
post ldbl state, instance #: 1, instance_load: 0.125000, threads allocated: 2 <= Load after allocating slaves to inst1
post ldbl state, instance #: 2, instance_load: 0.187500, threads allocated: 0 <= Load after allocating slaves to inst2
Trace Bucket Dump Begin: PX Blackbox <= Blackbox dump begins
Acquired 2 slaves on 1 instances avg height=2 #set=1 qser=10202113 <= Two slaves allocated on same instance
P000 inst 1 spid 1633 <= Process id - slave1 on inst1
P001 inst 1 spid 1716 <= Process id - slave2 on inst2
2012-12-12 21:28:35.278514*:PX_Messaging:kxfp.c@10588:kxfpgsg():
Instance(servers):
inst=1 #slvs=2 <= 2 slaves on node1
inst=2 #slvs=0 <= 0 slaves on node2
kxfxcp1
Sending parse to nprocs:2 slave_set:1
Sending parse to slave set 1:
User sqllen sent from QC = 24 <= length of the given query
select count(*) from t1
Trace Bucket Dump End: PX Blackbox <= Blackbox dump end
2012-12-12 21:28:35.303858*:PX_Messaging:kxfp.c@3292:kxfpqsod():
Query end, buffer cache support for numa enabled: YES
2012-12-12 21:28:35.303858*:PX_Messaging:kxfp.c@3296:kxfpqsod(end):
Does, Oracle Dump the trace into the black bucket.
Happy DBA... ;)
No comments:
Post a Comment