Explaining Explain Plan Notes for Auto DOP

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)

Comments:

I have a query that is entirely CPU bound mostly during large window sort operations on about 2M rows (data is coming from buffer cache entirely). It takes roughly 20 seconds on my hardware fully utilizing 1 of 8 CPU cores during execution. As you would expect this is cut nearly in half (11 seconds) with DOP 2 using hints in the query (11GR2 using in-memory PX). But without hints I always get DOP 1 with the message: " - automatic DOP: Computed Degree of Parallelism is 1" I am working with the web application engineers from which these queries originate to insert PX hints into their queries, but I would much rather have Auto DOP handle this. Parallel parameters below: parallel_adaptive_multi_user boolean TRUE parallel_automatic_tuning boolean FALSE parallel_degree_limit string 16 parallel_degree_policy string AUTO parallel_execution_message_size integer 16384 parallel_force_local boolean TRUE parallel_instance_group string parallel_io_cap_enabled boolean FALSE parallel_max_servers integer 85 parallel_min_percent integer 0 parallel_min_servers integer 0 parallel_min_time_threshold string 0 parallel_server boolean FALSE parallel_server_instances integer 1 parallel_servers_target integer 128 parallel_threads_per_cpu integer 2 Any advice on how to tune Auto DOP to be more likely to compute a higher DOP?

Posted by Eric on March 04, 2011 at 08:26 PM PST #

As the general idea is to work with Scan speed, you would have to adjust the IO calibration statistics to account for the largely in-memory and CPU bound portion of this. There are a couple of other observations on the parameter settings: - Parallel_adaptive_multi_user should be FALSE - parallel_max_servers should be set to a value a little higher or parallel_servers_target should be set to a lower value than parallel_max_Servers - Set parallel_min_servers to something a little bigger than 0, for example 32 I will try to write some better guidelines for the CPU bound workloads you may see as a follow up blog post. JP

Posted by jean-pierre.dijcks on March 22, 2011 at 08:03 AM PDT #

Post a Comment:
Comments are closed for this entry.
About

The data warehouse insider is written by the Oracle product management team and sheds lights on all thing data warehousing and big data.

Search

Archives
« April 2014
SunMonTueWedThuFriSat
  
2
4
5
6
7
8
9
10
11
12
13
14
16
18
19
20
21
22
23
24
25
26
27
28
29
30
   
       
Today