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