1

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.

user2671057
  • 1,411
  • 2
  • 25
  • 43

2 Answers2

1

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

Statement queuing

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.

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.

Jon Heller
  • 34,999
  • 6
  • 74
  • 132
0

Yes, you can use the Resource Manager.

The 11gr2 docs even have an example scenario basically up your alley...some jobs are more important than others, how to limit parallel processes to free up resources for the important ones

https://docs.oracle.com/cd/E11882_01/server.112/e25494/dbrm.htm#ADMIN13471

Oracle-Base of course also covers this feature. -- which as you can see has been around for a long time.

Note, you'll need an Enterprise Edition licensed copy of Oracle Database to use this feature...but if you're using that much CPU to begin with, I imagine you already have that?

thatjeffsmith
  • 20,522
  • 6
  • 37
  • 120
  • thatjeffsmith thank you. this is very interesting feature. I didn't know it. In my case all the statements are coming from the same source and user and I think that it is too complicated to build it with this solution. Is there a way to impact the parallelism directly in the query? – user2671057 May 28 '19 at 13:15
  • yeah, create a new user for the important or not important query, have it run as THAT user, and have a properly defined RESOURCE CONSUMER GROUP that throttles the DOP as appropriate. Or, have you tried letting the Optimizer decide what DOP is needed vs coding it in via HINTS? – thatjeffsmith May 28 '19 at 13:27