Limiting DOPs – Who rules over whom?

I've gotten a couple of questions from Dan Morgan and figured I start to answer them in this way. While Dan is running on a big system he is running with Database Resource Manager and he is trying to make sure the system doesn't go crazy (remember end user are never, ever crazy!) on very high DOPs.

Q: How do I control statements with very high DOPs driven from user hints in queries?

A: The best way to do this is to work with DBRM and impose limits on consumer groups. The Max DOP setting you can set in DBRM allows you to overwrite the hint.

Now let's go into some more detail here.

Assume my object (and for simplicity we assume there is a single object - and do remember that we always pick the highest DOP when in doubt and when conflicting DOPs are available in a query) has PARALLEL 64 as its setting.

Assume that the query that selects something cool from that table lives in a consumer group with a max DOP of 32.

Assume no goofy things (like running out of parallel_max_servers) are happening.

A query selecting from this table will run at DOP 32 because DBRM caps the DOP. As of we also use the DBRM cap to create the original plan (at compile time) and not just enforce the cap at runtime.

Now, my user is smart and writes a query with a parallel hint requesting DOP 128. This query is still capped by DBRM and DBRM overrules the hint in the statement. The statement, despite the hint, runs at DOP 32. Note that in the hinted scenario we do compile the statement with DOP 128 (the optimizer obeys the hint). This is another reason to use table decoration rather than hints.

Q: What happens if I set parallel_max_servers higher than processes (e.g. the max number of processes allowed to run on my machine)?

A: Processes rules. It is important to understand that processes are fixed at startup time. If you increase parallel_max_servers above the number of processes in the processes parameter you should get a warning in the alert log stating it can not take effect.

As a follow up, a hinted query requesting more parallel processes than either parallel_max_servers or processes will not be able to acquire the requested number. Parallel_max_processes will prevent this. And since parallel_max_servers should be lower than max processes you can never go over either...


JP -- this is good info. I didn't realize the DBRM enforcement at compile time changed in 11.2. Quick clarifying question with respect to your comment on using table decoration vs. hints -- if a parallel hint is used, will DBRM cap the DOP at compile time in 11.2 just as it would with table level parallel settings or does this imply that hinting in 11.2 will still result in pre-11.2-like DBRM enforcement after compile time? If the latter, will this change in future releases (i.e., 12)? Thanks!

Posted by Brian Beckman on January 11, 2011 at 09:46 PM PST #

If you use a hint (and this is yet another reason not to use hints all over the place) we will compile with the hint and cap at runtime. The desire with hints is therefore not the one you want. And we're always looking at improving things, so that might be one we consider.

Posted by jean-pierre.dijcks on January 12, 2011 at 12:48 AM PST #

One more clarifying comment on the DBRM enforcement, the change is in effect when you are running this with parallel_degree_policy = auto.

Posted by Jean-Pierre on January 26, 2011 at 02:23 AM PST #

Post a Comment:
Comments are closed for this entry.

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


« June 2016