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

September 30, 2011

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.