One is All You Need

The /*+ PARALLEL(table degree) */ hint may actually be my favorite query tuning tool. Actually, my favorite tool is the one that makes whatever query I’m tuning run faster, but I pull this hint out of the tool chest all the time. The PARALLEL hint is powerful, but potentially dangerous if used inappropriately. With most hints, usually the worst you can do is make your own query run faster (or slower!), but with PARALLEL you can significantly and adversely affect an entire database if you’re not careful.

The PARALLEL hint is just one way to take advantage of Oracle parallel execution. During parallel processing, large database operations are split into multiple pieces for multi-threaded execution. Parallel execution can be used in queries as well as DML (i.e. INSERT and UPDATE), DDL (i.e. CREATE INDEX) and in other ways not covered here. The Oracle documentation does a good job of fully explaining the inner sanctum of parallel execution, so we will not spend to much time on that topic. Let’s take a closer look at PARALLEL as a hint (it can also be used as a clause in a SQL statement) and what it can do for you…and to you!

The once-named Parallel Query Option (PQO) was first introduced in Oracle 7 and is primarily concerned with multi-threading large SQL operations. For our purposes, a large SQL operation typically means full table scans, sorts and hash joins, although index range scans on partitioned tables can occur in parallel with the PARALLEL_INDEX hint. Nested loops (index look ups) are not by themselves executable in parallel but these operations can be done in conjunction with a full table scans in parallel. So only certain types of queries will benefit from a PARALLEL hint. Queries that already include one or more full table scans are the most likely candidates. But, as we will see, queries that exclusively use nested loops (index look ups) may also benefit with a PARALLEL hint if the query is also asked to substitute full table scans for the nested loops.

Assuming that the parallel execution is available in your database (the DBA can set the maximum and minimum size of the process pool as well as many other parameters that affect parallel execution), you can take advantage of parallel processing in a number of ways. If a table is created or altered to include a DEGREE of parallelism, queries will automatically take advantage of parallel execution when large SQL operations are involved. However, it has been my experience that tables are rarely created with a parallelism DEGREE, since the ramifications for the database can be significant. As an example, query execution plans that once used indexes might decide to use full table scans and hash joins if the underlying table provides an inherent DEGREE of parallelism. Actually, the query does not decide anything - the Cost Based Optimizer decides; but that is a topic for another day. DEGREE, most simply, is the number a parallel processes that the database will spawn to execute the large SQL operation.

Let’s take a look at a “slow” query and how the PARALLEL can help it along. In this example, the “really_big_table” table has 10M rows and the “lookup table” has 100 rows. Of special note is lookup_id = 1 which has 5M rows in the really_big_table (50% of the table) while the other 99 lookup_ids are evenly distributed (about 50K rows a piece).

SELECT lut.lookup_name, count(*)
FROM   really_big_table rbt,
          lookup_table  lut
WHERE rbt.lookup_id = lut.lookup_id
GROUP BY lookup_name
ORDER BY lookup_name;

In our Oracle 10G database, the query execution plan looks like this:

This is the expected result. We need to look at every row in the really_big_table and join this to the lookup_table to get the lookup name. After the query completes the table scan, it hash joins the results and sorts the output to get the count(*) and sorts again to get us the final order. Its an efficient plan, but its also a single-threaded approach and if this query is running on a 16 processor machine, we can do better. If we add the PARALLEL hint with the table name (if the table is aliased, you may use the alias instead of the name) and the number of parallel threads (DEGREE of 2 in this case) we want to use, we get the following query with hint:

SELECT /*+ PARALLEL(rbt 2) */ lut.lookup_name, count(*)
FROM   really_big_table rbt,
          lookup_table  lut
WHERE rbt.lookup_id = lut.lookup_id
GROUP BY lookup_name
ORDER BY lookup_name;

Please note that the arguments to the PARALLEL hint are separated by a space, not a comma as if so often mistaken. A comma will actually cause the hint to be ignored in 9i, but in 10g it works both ways. This query produces a dramatically different query execution plan:

For those of you not used to parallel processing, this execution plan may look a little strange. All the extra lines here represent the operations necessary to take a serialized query, perform parallel execution, and finally gather the results back into a final result set (we promise to discuss the intricacies of these plans in more detail in a future post). In this particular case, the new, parallelized query ran 40% faster than the original query although the execution plan really didn’t change much. If you look closely, you will see that this query still performed 2 table scans, 1 hash join and some sorting. Its magic! Without making a single change to the actual query (except adding a PARALLEL hint), we have conjured a 40% improvement. Of course its not magic, we just commandeered more of the hardware resources. I performed these tests on a small windows box with two processors. As the following CPU usage diagrams show, the first query used one processor only while the parallelized query used both processors in parallel.

No Parallel Hint	Parallel execution with a degree of 2

If two processes were, good 4, 8 or even 16 must be all that much better. Obviously, this will depend entirely on the amount of processing power and disk I/O your hardware can muster. The amount of time savings will degrade as system resources are exhausted. Ultimately, queries that use too many parallel slaves (or too many parallel queries running at the same time) will degrade system performance for all other users. So care must be taken in choosing the proper degree of parallelism. For maximum effectiveness, 2 X number of available processors is a good rule of thumb. Even if you have a dedicated server, at some point adding parallel query processors will not appreciably increase the speed of query execution. The following CPU diagram shows the same query (on my small windows box) executed with 4, 8 and 16 degrees of parallelism (see the three big CPU bumps).

As it turns out, the CPU usage and query execution time did not change at all! Since there was available excess CPU time, this query likely was limited by disk I/O. But for enterprise quality hardware configurations, the PARALLEL hint will gobble up whatever resources it can get away with. Of course, there are many ways the DBA can limit the available resources, but in environments where parallel execution is not used such governors may not be in place. The result: A greedy PARALLEL hint can bring a database server to its knees. However, it has been this author’s experience that the PARALLEL hint can significantly improve query performance using resources that may be underutilized anyway. The PARALLEL hint can make you a hero!

Part 2: Monitoring Parallel Query Execution –>

Click here to ask SingleQuery.com a question.

Greg Pike