Explaining Explain Plan Notes for Auto DOP
- by jean-pierre.dijcks
I've recently gotten some questions around "why do I not see a parallel plan" while Auto DOP is on (I think)...? It is probably worthwhile to quickly go over some of the ways to find out what Auto DOP was thinking. In general, there is no need to go tracing sessions and look under the hood. The thing to start with is to do an explain plan on your statement and to look at the parameter settings on the system. Parameter Settings to Look At First and foremost, make sure that parallel_degree_policy = AUTO. If you have that parameter set to LIMITED you will not have queuing and we will only do the auto magic if your objects are set to default parallel (so no degree specified). Next you want to look at the value of parallel_degree_limit. It is typically set to CPU, which in default settings equates to the Default DOP of the system. If you are testing Auto DOP itself and the impact it has on performance you may want to leave it at this CPU setting. If you are running concurrent statements you may want to give this some more thoughts. See here for more information. In general, do stick with either CPU or with a specific number. For now avoid the IO setting as I've seen some mixed results with that... In 11.2.0.2 you should also check that IO Calibrate has been run. Best to simply do a: SQL> select * from V$IO_CALIBRATION_STATUS; STATUS CALIBRATION_TIME ------------- ---------------------------------------------------------------- READY 04-JAN-11 10.04.13.104 AM You should see that your IO Calibrate is READY and therefore Auto DOP is ready. In any case, if you did not run the IO Calibrate step you will get the following note in the explain plan: Note ----- - automatic DOP: skipped because of IO calibrate statistics are missing One more note on calibrate_io, if you do not have asynchronous IO enabled you will see: ERROR at line 1: ORA-56708: Could not find any datafiles with asynchronous i/o capability ORA-06512: at "SYS.DBMS_RMIN", line 463 ORA-06512: at "SYS.DBMS_RESOURCE_MANAGER", line 1296 ORA-06512: at line 7 While this is changed in some fixes to the calibrate procedure, you should really consider switching asynchronous IO on for your data warehouse. Explain Plan Explanation To see the notes that are shown and explained here (and the above little snippet ) you can use a simple explain plan mechanism. There should be no need to add +parallel etc. explain plan for <statement> SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY());
Auto DOP
The note structure displaying why Auto DOP did not work (with the exception noted above on IO Calibrate) is like this:
Automatic degree of parallelism is disabled: <reason>
These are the reason codes:
Parameter - parallel_degree_policy = manual which will not allow Auto DOP to kick in
Hint - One of the following hints are used NOPARALLEL, PARALLEL(1), PARALLEL(MANUAL)
Outline - A SQL outline of an older version (before 11.2) is used
SQL property restriction - The statement type does not allow for parallel processing
Rule-based mode - Instead of the Cost Based Optimizer the system is using the RBO
Recursive SQL statement - The statement type does not allow for parallel processing
pq disabled/pdml disabled/pddl disabled - For some reason (alter session?) parallelism is disabled
Limited mode but no parallel objects referenced - your parallel_degree_policy = LIMITED and no objects in the statement are decorated with the default PARALLEL degree. In most cases all objects have a specific degree in which case Auto DOP will honor that degree.
Parallel Degree Limited
When Auto DOP does it works you may see the cap you imposed with parallel_degree_limit showing up in the note section of the explain plan:
Note
-----
- automatic DOP: Computed Degree of Parallelism is 16 because of degree limit
This is an obvious indication that your are being capped for this statement. There is one quite interesting one that happens when you are being capped at DOP = 1. First of you get a serial plan and the note changes slightly in that it does not indicate it is being capped (we hope to update the note at some point in time to be more specific). It right now looks like this:
Note
-----
- automatic DOP: Computed Degree of Parallelism is 1
Dynamic Sampling
With 11.2.0.2 you will start seeing another interesting change in parallel plans, and since we are talking about the note section here, I figured we throw this in for good measure. If we deem the parallel (!) statement complex enough, we will enact dynamic sampling on your query. This happens as long as you did not change the default for dynamic sampling on the system.
The note looks like this:
Note
-----
- dynamic sampling used for this statement (level=5)
