Database Reference
In-Depth Information
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 in 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 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 topic: 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 topic! Some days
I wished there were 15 of me working on it). So you would hold the meeting, review the process, assign the work,
