Automatic DOP and Oracle EBS

What is Auto DOP?

Starting with 11g, Oracle introduced Automatic Degree of Parallelism, or Auto DOP.  I’ve blogged about this at http://www.centroid.com/knowledgebase/blog/automatic-degree-of-parallelism-in-oracle-11gr2.  I’m not going to review all the ins and outs of Auto DOP in this post.  Here, I’ll talk about a few things specific to Oracle e-Business Suite that are worth noting.

Enabling Auto DOP: First Tests

In order to take advantage of Auto DOP, we’ll need to color our tables with a “Default” degree of parallelism.   Below is a script that can be used to achieve what we want:

When this completed, I stopped and started the applications and when attempting to login to the front-end, I received this error:

Is this a consequence of giving everything a default DOP and configuring Auto DOP?   During this same general time period, we also saw the following PQ behavior in the SQL Monitoring window of EM Database Control:

As you can see, we witnessed 96-way DOPs, spanning both instances, for queries like these:

If we check the row counts of these tables, we can clearly see that they don’t have too many rows – so why did Auto DOP yield a DOP of 96 for these tables?

Part of the answer is in the fact that IO calibration statistics are missing.  We can see this from a simple Explain Plan on one of our queries above:

So let’s fix it:

Now that this is complete, let’s bounce and re-test:

In addition to being able to login to the Application, we also do not see inappropriate session with 96 parallel query slaves.  The takeaway from this experience is that you absolutely need to calibrate IO or else Auto DOP could greatly over-parallelize things queries and cause the application not to start.

Auto DOP and EBS: Other Observations

After coloring all the EBS Apps tables with a DEFAULT DOP, I noticed that several other sessions were running with a DOP=96, which is the system-calculated maximum. Let’s dig a little deeper into this “by looking at one of the SQL statements – see below, from SQL Monitoring:

As we can see, the SQL statement spawned the maximum number of PQ slaves as specified by (cpu_count * parallel_thread_per_cpu * parallel_server_instances).  If, however, I copy this SQL statement and run manually, I can see that no PQ slaves were launched, as we’d expect.  Also, if I run an EXPLAIN PLAN on it, I can that Auto DOP calculated a DOP of 1 for the statement:

Upon further research, I noticed that the sessions spawning the 96 PQ slaves are all running by background processes; specifically, attached to SYS$BACKGROUND service and running inside a DBMS_AQ.AQIN package – part of normal Advanced Queuing de-queuing and en-queuing.  Since my individual tests above seemed to generate the appropriate DOP when run in isolation, I was leaning toward a couple other theories:

* Auto DOP is using the maximum DOP because it’s running from SYS$BACKGROUND service, or

* Auto DOP is calculating the max DOP because it’s running inside a PL/SQL package, or

* Auto DOP is calculating the max DOP because of some other reason.  I know, this seems like a cop-out …

On the second bullet, let’s create a test PL/SQL package to we can determine whether this is indeed the case.  I’ll grab one of the SQL cursors that we found as having been colored with a DOP of 96 for starters, from SQL Monitoring in EM:

If I run this package as SYS, the output and SQL Monitoring output looks like the below:

We can see that the session did not run with a DOP of 96 – which, to me, rules out the PL/SQL theory. This leads me to believe that the session we observed to run with a high DOP did so because they were initiated by Oracle background processes – something that may be difficult to prove and/or test. 

But let’s try using a “_px_trace” trace.  First, let’s enable things:

Now after starting the instance, we’ll find a session that used a DOP=96:

So we’ve got a trace file that shows PQ trace operations

Now let’s do a “normal” Auto DOP thing:

We saw it spawn 2 PQ slaves:

If we check the PX trace file, we see the below:

Compared with the initial, background slave process:

Now this does not tell us exactly why the background process estimated 96 slaves, specifically, but it does tell us that it did.  It would be more direct if we could do a SQL compiler trace on the background process, but I couldn't get this to show anything because by the time I was able to snap a 10053 trace on a background session, its SQL cursors had already been hard parsed so the 10053 traces didn’t show anything.  I suppose I could enable event 10053 system-wide but I shied away from this due to trace file space concerns. 

Based on these observations, it seems as if background process cursors will not qualify for Auto DOP calculations.

Summary

* Automatic DOP is a good thing, despite the anomalies with advanced queuing related background processes

* Make sure you calibrate IO for Auto DOP to work, and do this before you color all the EBS tables with a DEFAULT DOP

* For all EBS tables, do a “alter table <table name> parallel (degree default)” – except AQ tables

* For the AQ tables, set the DOP to 1, or noparallel.  The script below will do this and the previous bullet