Automatic DOP in 11gR2

We have a probably very needful new feature introduced in 11g Release 2, related to parallel query: Automatically determined Degree of Parallelism (DOP). In earlier versions of the Oracle Database, we had to determine the DOP more or less manually, either with a parallel hint or by setting a parallel degree with alter table:

select /*+ parallel (sales,2) */ * from sales;

or

alter table sales parallel 2;

There was an automatic computation of the DOP available, derived from the simple formula CPU_COUNT * PARALLEL_THREADS_PER_CPU. That is what’s done internally if we would have said

select /*+ parallel (sales) */ * from sales;

or

alter table sales parallel;

The drawback with these approaches was always, that we could hardly be sure, whether the DOP is appropriate or not for the table, the statement and the hardware, we are running on. It was mostly a case of try & error. Especially problematic was the alter table approach, as this leads to the parallelization of each and every following select on those tables, even if totally inappropriate. A popular pitfall is the creation of tables with a parallel clause on OLTP-systems, because those tables inherit the parallel degree of their creation, which leads to parallel query for every statement accessing the tables afterwards – most likely not desirable for OLTP. In so far, parallelization was quite dumb (from a system-internal perspective) in versions before 11gR2. Now to the new feature:

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
PL/SQL Release 11.2.0.1.0 - Production
CORE    11.2.0.1.0      Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

SQL> select bytes/1024/1024 as mb from user_segments where segment_name='SALES';

 MB
----------
 563

SQL> select degree from user_tables where table_name='SALES';

DEGREE
----------------------------------------
 1

For my tiny machine, this table is huge. I allow automatic determination of the DOP with the following new dynamic parameter:

SQL> alter session set parallel_degree_policy=auto;

Session altered.

SQL> set autotrace on explain

SQL> select sum(amount_sold) from sales

SUM(AMOUNT_SOLD)
----------------
 1571293299

Execution Plan
----------------------------------------------------------
Plan hash value: 3130505568

-----------------------------------------------------------------------------------
| Id  | Operation              | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |          |     1 |     5 | 10798   (1)| 00:02:10 |
|   1 |  SORT AGGREGATE        |          |     1 |     5 |            |          |
|   2 |   PX COORDINATOR       |          |       |       |            |          |
|   3 |    PX SEND QC (RANDOM) | :TQ10000 |     1 |     5 |            |          |
|   4 |     SORT AGGREGATE     |          |     1 |     5 |            |          |
|   5 |      PX BLOCK ITERATOR |          |    14M|    70M| 10798   (1)| 00:02:10 |
|   6 |       TABLE ACCESS FULL| SALES    |    14M|    70M| 10798   (1)| 00:02:10 |
-----------------------------------------------------------------------------------

Note
-----
 - automatic DOP: Computed Degree of Parallelism is 2 because of degree limit

I have got a moderate DOP, most likely appropriate for my relatively weak hardware, but still speeding up the query on the relatively big table. I could always override the automatic DOP determination by specifying a parallel hint as in earlier versions. [See in my newer posting why the sentence here was crossed] Also, the parameter defaults to manual, so unless we change it, automatic parallelization will not take place. In order to demonstrate the quite intelligent computation of the DOP, compared to earlier versions, i will access another, much smaller table in the same session:

SQL> select count(*) from customers;

 COUNT(*)
----------
 30501

Execution Plan
----------------------------------------------------------
Plan hash value: 296924608

------------------------------------------------------------------------
| Id  | Operation          | Name      | Rows  | Cost (%CPU)| Time     |
------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |           |     1 |   218   (0)| 00:00:03 |
|   1 |  SORT AGGREGATE    |           |     1 |            |          |
|   2 |   TABLE ACCESS FULL| CUSTOMERS | 30501 |   218   (0)| 00:00:03 |
------------------------------------------------------------------------

Note
-----
 - automatic DOP: Computed Degree of Parallelism is 1 because of parallel threshold

As you can see, in this case, the system does not think that parallelization is appropriate for the select. So it is much smarter than the old force parallel query:

SQL> alter session set parallel_degree_policy=manual;

Session altered.

SQL> alter session force parallel query;

Session altered.

SQL>  select count(*) from customers;

 COUNT(*)
----------
 30501

Execution Plan
----------------------------------------------------------
Plan hash value: 1221513835

----------------------------------------------------------------------------
| Id  | Operation              | Name      | Rows  | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |           |     1 |   121   (0)| 00:00:02 |
|   1 |  SORT AGGREGATE        |           |     1 |            |          |
|   2 |   PX COORDINATOR       |           |       |            |          |
|   3 |    PX SEND QC (RANDOM) | :TQ10000  |     1 |            |          |
|   4 |     SORT AGGREGATE     |           |     1 |            |          |
|   5 |      PX BLOCK ITERATOR |           | 30501 |   121   (0)| 00:00:02 |
|   6 |       TABLE ACCESS FULL| CUSTOMERS | 30501 |   121   (0)| 00:00:02 |
----------------------------------------------------------------------------

Also, we have a remedy now against inappropriate parallel degrees on tables:

SQL> alter session enable parallel query -- the default, no force;
Session altered.
SQL> alter table customers parallel -- would cause parallel query before 11gR2;
Table altered.
SQL> set autotrace on explain
SQL> select count(*) from customers;
 COUNT(*)
----------
 30501
Execution Plan
----------------------------------------------------------
Plan hash value: 296924608
------------------------------------------------------------------------
| Id  | Operation          | Name      | Rows  | Cost (%CPU)| Time     |
------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |           |     1 |   218   (0)| 00:00:03 |
|   1 |  SORT AGGREGATE    |           |     1 |            |          |
|   2 |   TABLE ACCESS FULL| CUSTOMERS | 30501 |   218   (0)| 00:00:03 |
------------------------------------------------------------------------
Note
-----
 - automatic DOP: Computed Degree of Parallelism is 1 because of parallel threshold

About these ads

, ,

  1. #1 by Surachart Opun on November 25, 2009 - 09:43

    Thank You
    Good to use Degree of Parallelism

  2. #2 by Dion Cho on November 26, 2009 - 03:02

    Uwe. Thanks for sharing valueable test cases.

    By the way, parallel_degree_policy parameter has the 3rd value – “limited”, which is not covered in this post.

    Do you have any idea or info on this value? I was trying to search Metalink, but I have a terrible login problem with it.

    Cheers.

  3. #3 by Uwe Hesse on November 26, 2009 - 07:41

    Dion,
    my intention was not to cover the Automatic DOP completely – there is much more to say about it, of course – but to bring it to the attention of the Oracle Community with an easy example that shows an obvious benefit of it.

    To answer your question: The main difference of the values “AUTO and “LIMITED” is, that with “LIMITED”, this feature is limited to tables that have got an “ALTER TABLE t PARALLEL” designation.

  4. #4 by Uwe Hesse on February 8, 2010 - 17:11

    Just came across this related and very well written posting by Mark Rittman:

    http://www.rittmanmead.com/2010/01/19/in-memory-parallel-execution-in-oracle-database-11gr2/

  5. #5 by Marek on February 17, 2010 - 22:38

    Hello Uwe,
    thanks for the explanation.
    Greetings :-)

  6. #6 by Uwe Hesse on February 18, 2010 - 11:49

    Marek,
    you’re welcome :-)

  7. #7 by Wissem on October 12, 2011 - 18:02

    Thanks Uwe, That ‘s great post :)

    Cheers,
    Wissem

  8. #8 by Zuhra on April 20, 2012 - 11:30

    great information.

  1. Blogroll Report 20/11/2009-27/11/2009 « Coskan’s Approach to Oracle
  2. Auto DOP: Differences of parallel_degree_policy=auto/limited « The Oracle Instructor

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 2,760 other followers

%d bloggers like this: