Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Tom Kyte

Thanks for the question.

Asked: December 09, 2007 - 2:09 am UTC

Last updated: January 07, 2008 - 11:06 am UTC

Version: 10.2.0.3

Viewed 1000+ times

You Asked

Hello ,Mr. Tom:
Hop you fine!
Could you kind tell me how can I decided to use parallel and get benifit from it?
Regards
Alan

and Tom said...

<quote src = Expert Oracle Database Architecture... a book>

When to Use Parallel Execution

Parallel execution can be fantastic. It can allow you to take a process that executes over many hours or days and complete it in minutes. Breaking down a huge problem into small components may, in some cases, dramatically reduce the processing time. However, one underlying concept that it will be useful to keep in mind while considering parallel execution is summarized by this very short quote from Practical Oracle8i: Building Efficient Databases (Addison-Wesley, 2001) by Jonathan Lewis:

PARALLEL QUERY option is essentially nonscalable.

Parallel execution is essentially a nonscalable solution. It was designed to allow an individual user or a particular SQL statement to consume all resources of a database. If you have a feature that allows an individual to make use of everything that is available, and then allow two individuals to use that feature, you¿ll have obvious contention issues. As the number of concurrent users on your system begins to overwhelm the number of resources you have (memory, CPU, and I/O), the ability to deploy parallel operations becomes questionable. If you have a four-CPU machine, for example, and on average you have 32 users executing queries simultaneously, then the odds are that you do not want to parallelize their operations. If you allowed each user to perform just a ¿parallel 2¿ query, then you would now have 64 concurrent operations taking place on a machine with just four CPUs. If the machine were not overwhelmed before parallel execution, it almost certainly would be now.
In short, parallel execution can also be a terrible idea. In many cases, the application of parallel processing will only lead to increased resource consumption, as parallel execution attempts to use all available resources. In a system where resources must be shared by many concurrent transactions, such as an OLTP system, you would likely observe increased response times due to this. It avoids certain execution techniques that it can use efficiently in a serial execution plan and adopts execution paths such as full scans in the hope that by performing many pieces of the larger, bulk operation in parallel, it would be better than the serial plan. Parallel execution, when applied inappropriately, may be the cause of your performance problem, not the solution for it.
So, before applying parallel execution, you need the following two things to be true:
* You must have a very large task, such as the full scan of 50GB of data.
* You must have sufficient available resources. Before parallel full scanning 50GB of data, you would want to make sure that there is sufficient free CPU (to accommodate the parallel processes) as well as sufficient I/O. The 50GB should be spread over more than one physical disk to allow for many concurrent read requests to happen simultaneously, there should be sufficient I/O channels from the disk to the computer to retrieve the data from disk in parallel, and so on.

If you have a small task, as generally typified by the queries carried out in an OLTP system, or you have insufficient available resources, again as is typical in an OLTP system where CPU and I/O resources are often already used to their maximum, then parallel execution is not something you¿ll want to consider. So you can better understand this concept, I present the following analogy.


A Parallel Processing Analogy

I often use an analogy to describe parallel processing and why you need both a large task and sufficient free resources in the database. It goes like this: suppose you have two tasks to complete. The first is to write a one-page summary of a new product. The other is to write a ten-chapter comprehensive report, with each chapter being very much independent of the others. For example, consider this book: this chapter, ¿Parallel Execution,¿ is very much separate and distinct from the chapter titled ¿Redo and Undo¿¿they did not have to be written sequentially.

How do you approach each task? Which one do you think would benefit from parallel processing?

One-Page Summary

In this analogy, the one-page summary you have been assigned is not a large task. You would either do it yourself or assign it to a single individual. Why? Because the amount of work required to ¿parallelize¿ this process would exceed the work needed just to write the paper yourself. You would have to sit down, figure out that there should be 12 paragraphs, determine that each paragraph is not dependent on the other paragraphs, hold a team meeting, pick 12 individuals, explain to them the problem and assign them each a paragraph, act as the coordinator and collect all of their paragraphs, sequence them into the right order, verify they are correct, and then print the report. This is all likely to take longer than it would to just write the paper yourself, serially. The overhead of managing a large group of people on a project of this scale will far outweigh any gains to be had from having the 12 paragraphs written in parallel.
The exact same principle applies to parallel execution in the database. If you have a job that takes seconds or less to complete serially, then the introduction of parallel execution and its associated managerial overhead will likely make the entire thing take longer.

Ten-Chapter Report

But consider the second task. If you want that ten-chapter report fast¿as fast as possible¿the slowest way to accomplish it would be to assign all of the work to a single individual (trust me, I know¿look at this book! Some days I wished there were 15 of me working on it). Here you would hold the meeting, review the process, assign the work, act as the coordinator, collect the results, bind up the finished report, and deliver it. It would not have been done in one-tenth the time, but perhaps one-eighth or so. Again, I say this with the proviso that you have sufficient free resources. If you have a large staff that is currently not actually doing anything, then splitting the work up makes complete sense.
However, consider that as the manager, your staff is multitasking and they have a lot on their plates. In that case, you have to be careful with that big project. You need to be sure not to overwhelm them; you don¿t want to work them beyond the point of exhaustion. You can¿t delegate out more work than your resources (your people) can cope with, otherwise they¿ll quit. If your staff is already fully utilized, adding more work will cause all schedules to slip and all projects to be delayed.

Parallel execution in Oracle is very much the same. If you have a task that takes many minutes, hours, or days, then the introduction of parallel execution may be the thing that makes it run eight times faster. But then again, if you are already seriously low on resources (the overworked team of people), then the introduction of parallel execution would be something to avoid, as the system will become even more bogged down. While the Oracle server processes won¿t ¿quit¿ in protest, they could start running out of RAM and failing, or just suffer from such long waits for I/O or CPU as to make it appear as if they were doing no work whatsoever.

If you keep that in mind, remembering never to take an analogy to illogical extremes, you¿ll have the commonsense guiding rule to see if parallelism can be of some use. If you have a job that takes seconds, it is doubtful that parallel execution can be used to make it go faster¿the converse would be more likely. If you are low on resources already (i.e., your resources are fully utilized), adding parallel execution would likely make things worse, not better. Parallel execution is excellent for when you have a really big job and plenty of excess capacity. In this chapter, we¿ll take a look at some of the ways we can exploit those resources.

</quote>

Rating

  (3 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Good book! Thanks Tom.

A reader, December 09, 2007 - 9:44 pm UTC


parallel limits

Peter, January 07, 2008 - 6:02 am UTC

Great analogy.
My only question is if there is a way to tell Oracle to run the transaction in parallel, but not to take ALL the resources - but rather just assign something like 70% of each CPU to the task and leave the other 30% of the CPU for whatever else it's doing? So using the analogy, tell the employees to continue multi-tasking on their other stuff 30% of the time but give this new task a higher priority and allocate 70% of your time to get it done.

Or maybe it's just the manager I have now influencing this thought,
Tom Kyte
January 07, 2008 - 11:06 am UTC

resource management.

It will not box off something to 70% leaving 30% in reserve (since you cannot put cpu into the bank) but it will make it so that a consumer in one resource group gets about 70% of the machine whilst consumers in another group get about 30% - when the machine approaches 100% utilization....

see admin guide.

Perfect

Peter, January 07, 2008 - 12:19 pm UTC

Great idea with Resource Manager. Will follow up on that. Thanks for the advice.