Wednesday, December 12, 2012

Trace Bucket Dump - Blackbox on DOP


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