Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum Rss Feed

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Rss Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted February 11, 2013

Parallel Processing -- Bane or Boon

By David Fitzjarrell

An oft-used (and subsequently oft-abused) execution path is parallel execution, usually 'instigated' by some sort of parallel hint. Developers, albeit with the best intentions, misuse and abuse this mechanism because of faulty logic, that 'logic' being that if one process executes in X amount of time then Y parallel processes will execute in X/Y amount of time, and nothing could be further from the truth in many cases. Yes, there are opportunities to use parallelism to speed up processing but in most of the cases I've seen outside of Exadata it's doing more harm than good.

To illustrate this in a 'real world' situation say you're in a restaurant and the service appears to be slow; you think 'if I had three waitresses instead of one I'd get my food faster', but let's look at that from a different point of view. Three waitresses for one table means that all three waitresses need to communicate with each other to avoid repeating work done by the others (this, of course, takes time). Each waitress needs to be assigned specific duties but also must be available to take over for another if something happens (one broke her toe, for instance); such an occurrence requires reassignment of duties and adjusting the schedule to accomodate the change. Eventually you get your order but it will take MORE time than if a single waitress performed all of the necessary tasks.

Parallel processing does more than simply 'divide and conquer' as it requires several steps most developers may not know about or may ignore entirely. Exadata, DSS and DW systems, running with large numbers of CPUs or, in the case of Exadata, multiple storage servers where processing can be offloaded, can benefit from parallel processing as the load can be distributed among the CPUs reducing the load on a single processor. OLTP systems, on the other hand, usually involve operations, which are quick to begin with and the overhead of implementing parallel processing is quite large compared to the overall execution time; additionally it may take longer to complete the parallel execution than it would to properly tune the query for single-threaded processing and eliminate the parallelism altogether.

So what does parallel processing bring to the table? Obviously the possibility, but not the guarantee, of reduced execution time. What does it cost? That depends on the system but for OLTP systems it usually creates more work than it accomplishes as a parallel query coordinator process is spawned, along with X number of parallel slaves all 'talking' to that coordinator as well as a parallel-to-serial operation by the coordinator to assemble the final results. Many OLTP systems aren't designed for parallel processing (and rightly so) as parallel execution is designed to utilize resources that may remain idle (CPU, memory) and are in sufficient quantity to warrant generating the additional overhead required. Queries and statements should be tuned properly (there, I've said it again) for speedy execution and slapping a parallel hint on them isn't the correct way to go about the tuning process. Looking at an example of the differences between parallel and serial execution might help clear the air.

SQL> select id, txtval, status
  2  from para_tst pt
  3  where id between 9001 and 34001;

        ID TXTVAL                          STATUS
---------- ------------------------------ -------
     20772 /130d52e2_JDK2Sorter             VALID
     20773 /130d52e2_JDK2Sorter             VALID
     20774 /4c28cb16_ToolLogOptions         VALID
     20775 /4c28cb16_ToolLogOptions         VALID
     20776 /cbd9a55f_AbortException         VALID
     20777 /cbd9a55f_AbortException         VALID

591960 rows selected.

Elapsed: 00:02:03.68

Execution Plan
Plan hash value: 350193380

| Id | Operation                | Name     | Rows | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
|  0 | SELECT STATEMENT         |          |  593K|    20M|  1698   (2)| 00:00:21 |       |       |
|  1 |  PARTITION RANGE ITERATOR|          |  593K|    20M|  1698   (2)| 00:00:21 |    19 |    21 |
|* 2 |   TABLE ACCESS FULL      | PARA_TST |  593K|    20M|  1698   (2)| 00:00:21 |    19 |    21 |

Predicate Information (identified by operation id):

   2 - filter("ID" 
SQL> select /*+ parallel(pt 2) pq_distribute(pt partition) */
  2        id, txtval, status
  3  from para_tst pt
  4  where id between 9001 and 34001;

        ID TXTVAL                           STATUS
---------- ------------------------------- -------
     18404 /79bc64f9_JvmMemoryMeta           VALID
     18405 /b80019c2_EnumJvmThreadContent    VALID
     18406 /b80019c2_EnumJvmThreadContent    VALID
     18407 /bcfa29b5_EnumJvmThreadCpuTime    VALID
     18408 /bcfa29b5_EnumJvmThreadCpuTime    VALID
     17997 /b3bd73eb_CommonClassObject       VALID
     17998 /b3bd73eb_CommonClassObject       VALID
     17999 /c5a69e17_ServerSchemaObject1     VALID
     20724 /4bd3ef8d_KnownOptions4           VALID
     20725 /4bd3ef8d_KnownOptions4           VALID
     20726 /75d2b0ba_KnownOptions5           VALID
     20727 /75d2b0ba_KnownOptions5           VALID
     20728 /75e9b2d4_KnownOptions            VALID
     20729 /75e9b2d4_KnownOptions            VALID

591960 rows selected.

Elapsed: 00:02:23.59

Execution Plan
Plan hash value: 1393746857

| Id | Operation            | Name     | Rows | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |    TQ |IN-OUT| PQ Distr |
|  0 | SELECT STATEMENT     |          |  593K|    20M|   942   (1)| 00:00:12 |       |       |       |      |          |
|  1 |  PX COORDINATOR      |          |      |       |            |          |       |       |       |      |          |
|  2 |   PX SEND QC (RANDOM)| :TQ10000 |  593K|    20M|   942   (1)| 00:00:12 |       |       | Q1,00 | P->S | QC (RAND)|
|  3 |    PX BLOCK ITERATOR |          |  593K|    20M|   942   (1)| 00:00:12 |    19 |    21 | Q1,00 | PCWC |          |
|* 4 |     TABLE ACCESS FULL| PARA_TST |  593K|    20M|   942   (1)| 00:00:12 |    19 |    21 | Q1,00 | PCWP |          |

Predicate Information (identified by operation id):

   4 - filter("ID" 

On a conventional system running with absolutely no load the parallel execution takes almost twenty seconds longer; on a heavily used OLTP system this difference will likely be greater as the overhead to run in parallel is greater than that for the 'straight' query. Resources allocated in OLTP configurations are intended for multiple users performing concurrent processing thus there usually aren't 'extra' CPU cycles or memory to toss at parallel execution. Since the parallel query slaves will be waiting for resources (just like their user session counterparts) this will increase the response time and delay processing until such resources are freed. Notice also the execution path, which includes the parallel-to-serial operation that consolodates the outputs from the parallel query slaves into the final result set. Depending upon the server configuration there could be many more parallel slaves than shown here, and 'funneling' all of that data into a single 'pipe' consumes time and resources you most likely won't have in the OLTP environment.

When is it good practice to use parallel processing? One situation that comes to mind is in creating/populating tables using complex queries that may return faster in parallel than with standard serial processing. An application on which I worked was populating a table with a rather benign join but it was taking far too long to complete the load -- the elapsed time exceeded the batch processing window. Using parallelism (along with regularly updated statistics) dramatically reduced the response time from over an hour to less than two minutes, well within the batch window allowing the rest of the processing to continue. [Note that this was a batch process, run outside of the normal business day, which freed resources normally allocated to user sessions.] There are others, outside of data warehousing applications, but they're exceptions and not the rule.

So, we've learned that even though parallelism would appear to make things go faster in reality that's not often the case due to the extra overhead in managing additional processes and consolodating the individual results into the final result set. We've also learned that simply slapping a /*+ parallel */ hint into a query doesn't constitute tuning and doing so can make performance worse instead of better, outside of running on Exadata. The correct choice is to properly tune the query or queries in question using resources such as AWR and ASH reports, execution plans and wait statistics to pinpoint the problem area or areas to address. Parallelism isn't a silver bullet and wasn't intended to be and should be used sparingly, if at all.

See all articles by David Fitzjarrell

Oracle Archives

Latest Forum Threads
Oracle Forum
Topic By Replies Updated
Oracle Data Mining: Classification jan.hasller 0 July 5th, 07:19 AM
Find duplicates - Unique IDs Lava 5 July 2nd, 08:30 AM
no matching unique or primary key rcanter 1 April 25th, 12:32 PM
Update values of one table based on condition of values in other table using Trigger Gladiator 3 February 29th, 06:01 PM