Auto DOP: Differences of parallel_degree_policy=auto/limited

Recently, I delivered a Seminar about Parallel Processing in 11g where I came across some interesting findings, that I’d like to share with the Oracle Community. See my introduction into the 11g New Feature Auto DOP here, if that topic is completely new for you. There are big differences in the handling of Hints resp. Table-assigned parallel degrees, depending on the setting of parallel_degree_policy.

The parameter defaults to MANUAL, which gives you the known behavior of versions before 11g. LIMITED will only assign a system computed degree of parallelism (DOP) for tables, decorated with a parallel degree of DEFAULT, while prallel_degree_policy=AUTO will consider to assign a system computed DOP to all tables. Let’s see some details:

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

I did my tests on 11.2.0.1 and 11.2.0.2.

SQL> grant dba to adam identified by adam;

Grant succeeded.

SQL> connect adam/adam@prima
Connected.
SQL> set pages 300
SQL> show parameter parallel

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
fast_start_parallel_rollback         string      LOW
parallel_adaptive_multi_user         boolean     TRUE
parallel_automatic_tuning            boolean     FALSE
parallel_degree_limit                string      CPU
parallel_degree_policy               string      MANUAL
parallel_execution_message_size      integer     16384
parallel_force_local                 boolean     FALSE
parallel_instance_group              string
parallel_io_cap_enabled              boolean     FALSE
parallel_max_servers                 integer     20
parallel_min_percent                 integer     0
parallel_min_servers                 integer     0
parallel_min_time_threshold          string      AUTO
parallel_server                      boolean     FALSE
parallel_server_instances            integer     1
parallel_servers_target              integer     8
parallel_threads_per_cpu             integer     2
recovery_parallelism                 integer     0

SQL> create table t as select * from dual; 
Table created.

That is my test case. All the red parameters have default values. The table t is of course way too small to justify a parallel operation; especially, it will not meet the parallel_min_time_threshold of estimated runtime (about 10 seconds with AUTO). The setting parallel_degree_policy=MANUAL would leave the system as dumb as in earlier versions regarding an appropriate DOP, though. It would give me any DOP I demand with Hints or Parallel Degree on the table. See how that is different with AUTO/LIMITED:

SQL> alter system set parallel_degree_policy=auto;

System altered.

SQL> select /*+ parallel (t,8) */ * from t;

D
-
X

SQL> select * from v$pq_sesstat;

STATISTIC                      LAST_QUERY SESSION_TOTAL
------------------------------ ---------- -------------
Queries Parallelized                    0             0
DML Parallelized                        0             0
DDL Parallelized                        0             0
DFO Trees                               0             0
Server Threads                          0             0
Allocation Height                       0             0
Allocation Width                        0             0
Local Msgs Sent                         0             0
Distr Msgs Sent                         0             0
Local Msgs Recv'd                       0             0
Distr Msgs Recv'd                       0             0

11 rows selected.

Although this was a valid hint, I got no parallel operation! That is different with LIMITED:

SQL> alter system set parallel_degree_policy=limited;

System altered.

SQL> select /*+ parallel (t,8) */ * from t;

D
-
X

SQL> select * from v$pq_sesstat;

STATISTIC                      LAST_QUERY SESSION_TOTAL
------------------------------ ---------- -------------
Queries Parallelized                    1             1
DML Parallelized                        0             0
DDL Parallelized                        0             0
DFO Trees                               1             1
Server Threads                          8             0
Allocation Height                       8             0
Allocation Width                        1             0
Local Msgs Sent                        26            26
Distr Msgs Sent                         0             0
Local Msgs Recv'd                      26            26
Distr Msgs Recv'd                       0             0

11 rows selected.

Same statement, now I got my (not sensible) DOP. There is a new hint in 11g on the statement level, though, that is also delivering my requested DOP with AUTO:

SQL> connect adam/adam@prima
Connected.
SQL> alter system set parallel_degree_policy=auto;

System altered.
SQL> select /*+ parallel (8) */ * from t;

D
-
X

SQL> select * from v$pq_sesstat;

STATISTIC                      LAST_QUERY SESSION_TOTAL
------------------------------ ---------- -------------
Queries Parallelized                    1             1
DML Parallelized                        0             0
DDL Parallelized                        0             0
DFO Trees                               1             1
Server Threads                          8             0
Allocation Height                       8             0
Allocation Width                        1             0
Local Msgs Sent                        26            26
Distr Msgs Sent                         0             0
Local Msgs Recv'd                      26            26
Distr Msgs Recv'd                       0             0

11 rows selected.

That is the only way to overrule the Auto DOP with parallel_degree_policy=AUTO. Similar that is with Parallel Degree on the table:

SQL> connect adam/adam@prima
Connected.
SQL> alter system set parallel_degree_policy=auto;

System altered.

SQL> alter table t parallel;

Table altered.

SQL> select * from t;

D
-
X

SQL> select * from v$pq_sesstat;

STATISTIC                      LAST_QUERY SESSION_TOTAL
------------------------------ ---------- -------------
Queries Parallelized                    0             0
DML Parallelized                        0             0
DDL Parallelized                        0             0
DFO Trees                               0             0
Server Threads                          0             0
Allocation Height                       0             0
Allocation Width                        0             0
Local Msgs Sent                         0             0
Distr Msgs Sent                         0             0
Local Msgs Recv'd                       0             0
Distr Msgs Recv'd                       0             0

11 rows selected.

SQL> alter table t parallel 8;

Table altered.

SQL> select * from t;

D
-
X

SQL> select * from v$pq_sesstat;

STATISTIC                      LAST_QUERY SESSION_TOTAL
------------------------------ ---------- -------------
Queries Parallelized                    0             0
DML Parallelized                        0             0
DDL Parallelized                        0             0
DFO Trees                               0             0
Server Threads                          0             0
Allocation Height                       0             0
Allocation Width                        0             0
Local Msgs Sent                         0             0
Distr Msgs Sent                         0             0
Local Msgs Recv'd                       0             0
Distr Msgs Recv'd                       0             0

11 rows selected.

SQL>  alter system set parallel_degree_policy=limited;

System altered.

SQL> alter table t parallel;

Table altered.

SQL> select * from t;

D
-
X

SQL> select * from v$pq_sesstat;

STATISTIC                      LAST_QUERY SESSION_TOTAL
------------------------------ ---------- -------------
Queries Parallelized                    0             0
DML Parallelized                        0             0
DDL Parallelized                        0             0
DFO Trees                               0             0
Server Threads                          0             0
Allocation Height                       0             0
Allocation Width                        0             0
Local Msgs Sent                         0             0
Distr Msgs Sent                         0             0
Local Msgs Recv'd                       0             0
Distr Msgs Recv'd                       0             0

11 rows selected.

SQL> alter table t parallel 8;

Table altered.

SQL> select * from t;

D
-
X

SQL> select * from v$pq_sesstat;

STATISTIC                      LAST_QUERY SESSION_TOTAL
------------------------------ ---------- -------------
Queries Parallelized                    1             1
DML Parallelized                        0             0
DDL Parallelized                        0             0
DFO Trees                               1             1
Server Threads                          8             0
Allocation Height                       8             0
Allocation Width                        1             0
Local Msgs Sent                        26            26
Distr Msgs Sent                         0             0
Local Msgs Recv'd                      26            26
Distr Msgs Recv'd                       0             0

11 rows selected.

You saw a behavior like in the below table described:

parallel_degree_policy parallel (t,8) Parallel (8) degree DEFAULT degree 8
manual 8 8 4 8
limited 8 8 1 8
auto 1* 8 1 1

The default degree with parallel_degree_policy=MANUAL is cpu_count * parallel_threads_per_cpu; 4 in my case.

Apart from the shown differences between AUTO and LIMITED, only AUTO enables the also New Features Parallel Statement Queueing and In-Memory Parallel Execution. My personal impression is that LIMITED works like we have hoped that parallel_automatic_tuning would but never did :-)

Conclusion: parallel_degree_policy=LIMITED will give you the DOP you request and compute an appropriate DOP only with a parallel degree of DEFAULT as an attribute of the table. Use this parameter if you trust that your applications/designers know why they use a certain DOP. parallel_degree_policy=AUTO will overrule any specific DOP you gave – except the new 11g parallel (n) Hint – and consider to do things in parallel for all tables even without a Hint or Degree.

Addendum: See this nice related posting by Gwen Shapira, especially the part about the I/O calibration.

* Second Addendum: With 11.2.0.3, the hint /*+ parallel (t,8) */ determines the DOP to 8, regardless of the parallel_degree_policy setting. Everything else is the same as shown, especially the different behavior of the values AUTO and LIMITED with the parallel degree of the table t explicitly set to 8.

, ,

  1. #1 by Nassyam Basha on October 12, 2011 - 17:26

    Nice explanation. Thanks for sharing.

  2. #2 by Greg Rahn on October 12, 2011 - 17:58

    Your first example of using parallel_degree_policy=auto with the /*+ parallel (t,8) */ hint not running parallel is bug 10628995 which is fixed in 11.2.0.3.

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

    Thanks Uwe, That ‘s great post :)

    Cheers,
    Wissem

  4. #4 by Greg Rahn on October 12, 2011 - 18:23

    To clarify my comment further – you would likely hit bug 10628995 if your table was big enough. In your case the table is too small so it runs serial anyway.

  5. #5 by Uwe Hesse on October 12, 2011 - 22:29

    @Nassyam & Wissem: Thank you guys for the nice feedback :-)

    @Greg: I know that my table is too small to justify a parallel operation. That is not the point. The point is whether the system is supposed to follow the hint regardless whether it is sensible or not, with parallel_degree_policy=AUTO in the same way it does with MANUAL or LIMITED.

    I think therefore that you are not spot on with your remark “In your case the table is too small so it runs serial anyway.” Thank you for mentioning the bugfix with 11.2.0.3! I have not yet installed it. Will the /*+ parallel (t,8) */ hint be followed with that bugfix regardless of its appropriateness? Sorry for being nitpicking :-)

  6. #6 by Uwe Hesse on January 3, 2012 - 16:52

    I finally could find some time to install 11.2.0.3 (very busy these days!) and I can now confirm that indeed the hint /*+ parallel (t,8) */ is followed also with parallel_degree_policy=auto, although the table is way too small to justify a (sensible) parallel query.

    So it is not “… too small so it runs serial anyway”.

  1. Automatic DOP in 11gR2 « 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,960 other followers

%d bloggers like this: