Limiting DOPs - Who Manners Over Whom?

I've gotten a couple of questions from Dan Morgan and figured we beginning to answer them in this way. While Dan is running on a large network he is running with Database Resource Manager and he is perplexing to ensure the network doesn't go funny (remember finish user are never, ever crazy!) on really high DOPs.

Q: How do we manage statements with really high DOPs driven from user hints in queries?

A: The most appropriate way to do this is to work with DBRM and levy boundary on consumer groups. The Max DOP surroundings you can set in DBRM allows you to overwrite the hint.

Now let's go in to a few more item here.

Assume my intent (and for ease we pretence there is a singular intent - and do recollect that we always collect the top DOP when in skepticism and when contrary DOPs are existing in a query) has PARALLEL 64 as its setting.

Assume that the question that selects something cold from that list lives in a consumer organisation with a max DOP of 32.

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

A question selecting from this list will run at DOP 32 since DBRM caps the DOP. As of 11.2.0.1 we moreover use the DBRM hat to emanate the initial outline (at put together time) and not only enforce the hat at runtime.

Now, my user is chic and writes a question with a parallel suggestion requesting DOP 128. This question is still capped by DBRM and DBRM overrules the suggestion in the statement. The statement, notwithstanding the hint, runs at DOP 32 . Note that in the hinted unfolding we do put together the matter with DOP 128 (the optimizer obeys the hint). This is other reason to use list embellishment rsther than than hints.

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

A: Processes rules. It is critical to comprehend that processes are prearranged at startup time. If you enlarge parallel_max_servers on top of the number of processes in the processes parameter you should obtain a bell in the inform record saying it can not take effect.

As a follow up, a hinted question requesting more parallel processes than possibly parallel_max_servers or processes will not be able to pick up the requested number. Parallel_max_processes will head off this. And since parallel_max_servers should be descend than max processes you can never go over either...