Parallel Reasoning

17 02 2010

February 17, 2010 (Updated October 7, 2010: 11.2 documentation links fixed)

This article is a light-weight exploration of parallel query which is available in the Enterprise Edition of Oracle Database.  There are a lot of great articles on the Internet that discuss parallel query benefits and pitfalls, and Metalink contains a couple of decent articles also.  For example, Metalink Doc ID 263153.1 includes the following bit of advice:

“The parallel hint on the object make sure that we access this object in parallel.”

Neat.  Let’s see if it’s true.  We will experiment with the test table found in this blog article, being sure to collect statistics on all of the columns, and executing the test queries on Oracle Database 11.2.0.1 using my VBS tool for automatically generating execution plans.  Here is the query that will be executed:

SELECT /*+ PARALLEL(2) */
  *
FROM
  T1
WHERE
  C1 BETWEEN 1 AND 10000;

The following execution plan was generated for the query:

SQL_ID  gjcx2fjkwt7xc, child number 0
-------------------------------------
SELECT /*+ PARALLEL(2) */    *  FROM    T1  WHERE    C1 BETWEEN 1 AND 
10000

Plan hash value: 2332705540

-----------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name         | Rows  | Bytes | Cost (%CPU)| Time     | Inst   |
-----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |              |       |       |   343 (100)|          |        |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1           | 10000 |  2236K|   343   (0)| 00:00:05 |  OR112 |
|*  2 |   INDEX RANGE SCAN          | SYS_C0018049 | 10000 |       |    20   (0)| 00:00:01 |  OR112 |
-----------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("C1">=1 AND "C1"<=10000)

Note
-----
   - Degree of Parallelism is 1 because of hint

That’s a strange note at the end of the execution plan.  Maybe Doc ID 263153.1 does not apply to Oracle 11.2.0.1, because the query did not execute in parallel?  Let’s try again:

SELECT /*+ PARALLEL(3) */
  *
FROM
  T1
WHERE
  C1 BETWEEN 1 AND 10000;

SQL_ID  45mgp20qp9rfu, child number 0
-------------------------------------
SELECT /*+ PARALLEL(3) */    *  FROM    T1  WHERE    C1 BETWEEN 1 AND 
10000

Plan hash value: 2494645258

--------------------------------------------------------------------------------------------------------------
| Id  | Operation            | Name     | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |          |       |       |   329 (100)|          |        |      |            |
|   1 |  PX COORDINATOR      |          |       |       |            |          |        |      |            |
|   2 |   PX SEND QC (RANDOM)| :TQ10000 | 10000 |  2236K|   329   (0)| 00:00:04 |  Q1,00 | P->S | QC (RAND)  |
|   3 |    PX BLOCK ITERATOR |          | 10000 |  2236K|   329   (0)| 00:00:04 |  Q1,00 | PCWC |            |
|*  4 |     TABLE ACCESS FULL| T1       | 10000 |  2236K|   329   (0)| 00:00:04 |  Q1,00 | PCWP |            |
--------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   4 - access(:Z>=:Z AND :Z<=:Z)
       filter(("C1"<=10000 AND "C1">=1))

Note
-----
   - Degree of Parallelism is 3 because of hint

Well, that’s odd.  Doc ID 263153.1 first doesn’t apply to Oracle 11.2.0.1, and then does apply to Oracle 11.2.0.1.  Notice that the calculated total query cost of the first plan is 343 and the calculated total query cost of the second plan is 329.  Time for another test:

SELECT /*+ PARALLEL(4) */
  *
FROM
  T1
WHERE
  C1 BETWEEN 1 AND 10000;

SQL_ID  1m142v1crjs2j, child number 0
-------------------------------------
SELECT /*+ PARALLEL(4) */    *  FROM    T1  WHERE    C1 BETWEEN 1 AND 
10000

Plan hash value: 2494645258

--------------------------------------------------------------------------------------------------------------
| Id  | Operation            | Name     | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |          |       |       |   247 (100)|          |        |      |            |
|   1 |  PX COORDINATOR      |          |       |       |            |          |        |      |            |
|   2 |   PX SEND QC (RANDOM)| :TQ10000 | 10000 |  2236K|   247   (1)| 00:00:03 |  Q1,00 | P->S | QC (RAND)  |
|   3 |    PX BLOCK ITERATOR |          | 10000 |  2236K|   247   (1)| 00:00:03 |  Q1,00 | PCWC |            |
|*  4 |     TABLE ACCESS FULL| T1       | 10000 |  2236K|   247   (1)| 00:00:03 |  Q1,00 | PCWP |            |
--------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   4 - access(:Z>=:Z AND :Z<=:Z)
       filter(("C1"<=10000 AND "C1">=1))

Note
-----
   - Degree of Parallelism is 4 because of hint

The calculated total query cost decreased to 247.  OK, if we keep bumping up the parallel degree, the calculated cost becomes lower, so we might as well get it over with and do this:

SQL_ID  9xccz9nu4hrqx, child number 0
-------------------------------------
SELECT /*+ PARALLEL(1000) */    *  FROM    T1  WHERE    C1 BETWEEN 1 
AND 10000

Plan hash value: 2494645258

--------------------------------------------------------------------------------------------------------------
| Id  | Operation            | Name     | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |          |       |       |     2 (100)|          |        |      |            |
|   1 |  PX COORDINATOR      |          |       |       |            |          |        |      |            |
|   2 |   PX SEND QC (RANDOM)| :TQ10000 | 10000 |  2236K|     2   (0)| 00:00:01 |  Q1,00 | P->S | QC (RAND)  |
|   3 |    PX BLOCK ITERATOR |          | 10000 |  2236K|     2   (0)| 00:00:01 |  Q1,00 | PCWC |            |
|*  4 |     TABLE ACCESS FULL| T1       | 10000 |  2236K|     2   (0)| 00:00:01 |  Q1,00 | PCWP |            |
--------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - access(:Z>=:Z AND :Z<=:Z)
       filter(("C1"<=10000 AND "C1">=1))

Note
-----
   - Degree of Parallelism is 1000 because of hint

There, we now have a cost of 2 – the query should now finish instantaneously.  🙂  But why didn’t the query excute in parallel when PARALLEL(2) was specified in the hint?  A search of the Internet provides the answer:

Hints on Hinting (page 2)

“The hint parallel(t1, N) does not tell Oracle to run the query in parallel at degree N – it tells the optimizer that when it is calculating the cost of a tablescan on table t1 it should cost for parallel degree N, and if that plan then happens to be the cheapest of all the plans to consider that that’s the plan to use.”

Hints – Again

“But the hint we supplied is only relevant to the tablescan – and we can see it being used in the arithmetic above: the cost_io is the serial I/O cost of the tablescan, the resp_io is the ‘parallel response’ time for the query which is (cost_io / (0.9 * degree)).”

Calculated costs of the access paths matter… so that’s why the first query did not execute in parallel.

For those who decide to rebuild indexes for whatever reason, and decide to do so in parallel, you might be setting yourself up for headaches:

A Gotcha with Parallel Index Builds, Parallel Degree and Query Plans

I haven’t enabled parallelism for my table! How can Oracle go parallel without my consent?  Parallel index (re)build will persistently set the index parallel degree in data dictionary to the value used during build!

—

Oracle 11.2.0.1 introduced a couple of changes to parallel query, and a couple of those changes are listed below:

http://download.oracle.com/docs/cd/E11882_01/server.112/e17118/sql_elements006.htm#BABHFDDH

“Beginning with Oracle Database 11g Release 2, the PARALLEL and NO_PARALLEL hints are statement-level hints and supersede the earlier object-level hints: PARALLEL_INDEX, NO_PARALLEL_INDEX, and previously specified PARALLEL and NO_PARALLEL hints. For PARALLEL, if you specify integer, then that degree of parallelism will be used for the statement. If you omit integer, then the database computes the degree of parallelism. All the access paths that can use parallelism will use the specified or computed degree of parallelism.”

http://download.oracle.com/docs/cd/E11882_01/server.112/e16541/parallel002.htm

“When the parameter PARALLEL_DEGREE_POLICY is set to AUTO, Oracle Database automatically decides if a statement should execute in parallel or not and what DOP it should use. Oracle Database also determines if the statement can be executed immediately or if it is queued until more system resources are available. Finally, it decides if the statement can take advantage of the aggregated cluster memory or not.

The following example illustrates computing the DOP the statement should use:

SELECT /*+ parallel(auto) */ ename, dname FROM emp e, dept d
WHERE e.deptno=d.deptno;

The following example forces the statement to use Oracle Database 11g, Release 1 (11.1) behavior:

SELECT /*+ parallel(manual) */ ename, dname FROM emp e, dept d WHERE e.deptno=d.deptno;"

Also from the documentation:

“Oracle ignores parallel hints on temporary tables. Refer to CREATE TABLE and Oracle Database Concepts for more information on parallel execution.”

As I mentioned at the beginning of this article, I only intend to provide a light-weight exploration of parallel query, so I will stop here… or better yet after providing a couple of potentially helpful Metalink article references that you can review using your shiny new Apple iPad:

  • Doc ID 263153.1 “FAQ’s about Parallel/Noparallel Hints” (briefly mentioned at the beginning of this article)
  • Doc ID 203238.1 “Using Parallel Execution”
  • Doc ID 826893.1 “Invalid Hint in 10g Can Cause Other Hints To Be Ignored, Including Parallel Hints”
  • Doc ID 199272.1 “Why didn’t my parallel query use the expected number of slaves?”
  • Doc ID 233754.1 “Why doesn’t my query run in parallel?”
  • Doc ID 267330.1 “SQL statements that run in parallel with NO_PARALLEL hints”
  • Doc ID 196938.1 “Why did my query go parallel?”
  • Doc ID 752816.1 “Parallel Update On Table Does Not Work If Statistics Have Been Calculated”
  • Doc ID 237287.1 “How To Verify Parallel Execution is running”
  • Doc ID 457857.1 “Script to monitor parallel queries”
  • Doc ID 280939.1 “Checklist for Performance Problems with Parallel Execution”
  • Doc ID 46234.1 “Interpreting Explain plan”
  • Doc ID 260845.1 “Old and new Syntax for setting Degree of Parallelism”
  • Doc ID 235400.1 “Disable Parallel Execution on Session/System Level”
  • Doc ID 763419.1 “Neither Table DOP nor parallel hint is saved in the Stored Outline hints”
  • Doc ID 6474009.8 “Bug 6474009 – On ASSM tablespaces Scans using Parallel Query can be slower than in 9.2”
  • Doc ID 6748058.8 “Bug 6748058 – A parallel query executes serially due to correlation variable”
  • Doc ID 6402957.8 “Bug 6402957 – Suboptimal parallel query performance for Top-n rank queries.”
  • Doc ID 873392.1 “Ora-04030 with high PGA allocation for ‘kxs-heap-p’ under session heap”
  • Doc ID 844538.1 “Wrong Results For ROWNUM During Parallel Execution on RAC”
  • Doc ID 755975.1 “Parallel Query With Virtual Private Database Returns Wrong Result or Fails With ORA-600[kzrtgpp – kxfxslavesql]”

Be on the lookout for hard hitting parallel query articles by other authors (such as Doug Burns), and try not to abuse this feature.  I will leave you with this paraphrase from the book “Practical Oracle 8i”

Parallel query’s objective is to maximize resource utilization in order to reduce query execution time.  Oracle may select a high CPU consumption plan using a full tablescan, rather than a lower CPU consuming index range scan – this situation may cause performance problems in an OLTP system.  Parallel query is a data crunching, non-scalable, batch processor.


Comments : 7 Comments »

Categories : Performance