Information, tips, tricks and sample code for Big Data Warehousing in an autonomous, cloud-driven world

Limiting DOPs – Who rules over whom?

Jean-Pierre Dijcks
Master Product Manager

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...

Join the discussion

Comments ( 3 )
  • Brian Beckman Wednesday, January 12, 2011
    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!
  • jean-pierre.dijcks Wednesday, January 12, 2011
    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.
  • Jean-Pierre Wednesday, January 26, 2011
    One more clarifying comment on the DBRM enforcement, the change is in effect when you are running this with parallel_degree_policy = auto.
Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.