Database Reference
In-Depth Information
Subsequent lines show the reasoning behind serial execution. In this database, the Database Resource Manager
disabled parallelism for the current user by granting zero PX servers. That's the reason why the SQL statement did not
get any PX servers.
PX_Messaging:kxfp.c@17808:kxfpclinfo(): 2 (78 :0 :100:35 :16 :0 :0 ...)
Ressource Manager reduced num server to maxdop = 0
serial -- Requested 8 threads,
Granted 0
Aborting!
Max Servers=32 Adaptive=0 RM Max-DOP=0
After switching to the correct resource consumer group and retracing the execution of the statement, we can
see that the query allocated eight threads each in instances 1 and 2. As you can see, two sets of eight threads were
requested by this parallel query, and all of them were granted by the Resource Manager. In the following output,
P000 is allocated from instance 2 and the complete trace file lists all 16 PX server allocations.
kxfpgsg [ 586/ 0]
getting 2 sets of 8 threads, client parallel query execution flg=0x234
Height=8, Affinity List Size=0, inst_total=1, coord=2
Insts 2
Threads 8
kxfpg1sg [ 587/ 1]
q:e8dfd9668 req_threads:8 nthreads:8 unit:1 #inst:1 normal
jStart:0 jEnd:32 jIncr:1 isGV:0 i:0 instno:2 kxfpilthno:8
kxfpg1srv [ 587/ 0]
trying to get slave
P000 on instance 2
for q:e8dfd9668
slave
P000 is local
found slave P000 dp:e11cdc888 flg:18
Got It. 1 so far.
You can debug PX execution problems by tracing the statements with _px_trace event and reviewing the
generated trace file. Refer to Oracle support document ID 400886.1 for further information about _px_trace command.
Index Creation in RAC
Parallel index creation is a much-discussed-topic in a RAC database. Should you allocate PX servers from all instances
to create a big index? Or should you allocate PX servers from just one instance? The strategy depends upon the
environment, and the following resource constraints should be considered before deciding the strategy:
1.
Does one node have enough memory, CPU, and I/O capacity to complete index creation?
2.
How much interconnect bandwidth is available for PQ workload? Can the bandwidth
sustain the burst of interconnect traffic generated by index creation statements?
3.
Is there sufficient I/O bandwidth such that the LGWR process in one node can write all
index redo entries to the log files without incurring any performance delay? Index creation
in two nodes uses the LGWR processes in both nodes and might perform better if LGWR is
a bottleneck.
For example, a 6GB index creation using two nodes induced an interconnect traffic of 2.6GB. The following shows
the interconnect traffic bytes from x$ksxpclient.
