Is controlling parallelism with an Oracle resource plan ever a good option?

Ever wanted to be able to run some code in parallel, while keeping the rest execute sequentially? Oracle Resource Groups allows us to dispatch sessions into groups and impose restrictions about the resource usage of those groups. Parallelism is one of the resources that can be managed by Resource Groups, so things look promising.

 It works too. I was fortunate enough to avoid hitting any bugs.

 So here the problem: All plans are generated as if all parallel processes are available for the query. A query that is to be executed serially can get a parallel execution plan, which often evolves full table scans. When it comes to actually running the query, Oracle will abide by restriction of the Resource Plan and run it sequentially. This is major structural problem causing suboptimal plans. It is like planning your move based on the assumption that would get a large U-Haul truck, and coming move day all you can get is a compact car… Not pretty.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: