CODEWITHSUNDEEP
oracle-databaseparallel-processingexecute-immediate
user2671057
user2671057

Reputation: 1533

Manage the parallel degree of sql execution

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.

Upvotes: 1

Views: 1125

Answers (2)

Jon Heller
Jon Heller

Reputation: 36832

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.

Upvotes: 1

thatjeffsmith
thatjeffsmith

Reputation: 22447

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?

Upvotes: 0

Related Questions