I have a process that sends a lot of commands to be executed with parallel.
exmaple of command:
Insert /*+ parallel(16) */ ...
The problem is that if the requested parallel is higher than the available servers, the command gets downgraded in the parallel degree, and because I send several commands togather they "steal" parallel servers from each others.
I have one specific query, that’s more important and needs to run with the requested parallel, while the other queries are less important.
Is there a way to enforce the optimizer to decrease the other statements parallel degree and increase the parallel of the important query?
Is there a way to change the parallel degree of statement after it started running?
I'm using oracle 11g.
Look into statement queuing and parameters like PARALLEL_MAX_SERVERS to ensure that important processes are getting enough parallel servers. (In my experience, setting up resource manager is too complicated, and is rarely done well.)
There is no way to move parallel servers from one statement to another while they are running. However, with statement queuing we can ask Oracle to not run an important statement until it has the necessary parallel resources. It might be better for a large parallel process to wait a minute and get all requested parallel servers than to run immediately with only partial resources.
Use the hint /*+ parallel(16) statement_queuing*/ in the most important statements.
Make sure that the parameter PARALLEL_SERVERS_TARGET is set appropriately - statement queuing will only use parallel servers up to that number. You may want to set that parameter to be the same as PARALLEL_MAX_SERVERS.
Make sure that the parameter PARALLEL_MAX_SEERVERS is set appropriately. This is a difficult value to set and I frequently see low values. Keep in mind that Oracle parallel servers are relatively lightweight processes. In most cases, half of the allocated servers will only be used to store intermediate results, and won't be running.
For example, if your system has 32 cores you should set PARALLEL_MAX_SERVERS to at least 64, if not 128 or 256. Many of DBAs incorrectly decrease the value for PARALLEL_MAX_SERVERS before testing. Based on my experience and testing, Oracle servers can efficiently handle more parallel servers than most people realize.