parallel hint with no degree specified

In this post we try to investigate what degree of parallelism (DOP) Oracle assigns to DML operations when a parallel hint is used.


nkarag@DWHPRD> select * from v$version
  2  /

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

Assume we have a table with a default  DOP of 4:

nkarag@DWHPRD> create table lala parallel 4 as select * from dba_objects

  2  /

Table created.

Lets fill this table enough so that a DML operation with a  full table scan will take some time to complete.

nkarag@DWHPRD> insert /*+ append */ into lala select * from dba_objects

  2  /

559717 rows created.

nkarag@DWHPRD> commit;

Commit complete.

nkarag@DWHPRD> insert /*+ append */ into lala select * from dba_objects

  2  /

559717 rows created.

nkarag@DWHPRD> commit;

Commit complete.

nkarag@DWHPRD> exec dbms_stats.gather_Table_stats('NKARAG', 'LALA')

PL/SQL procedure successfully completed.

Lets try some parallel DML with different cases regarding the parallel hint.

nkarag@DWHPRD> alter session enable parallel dml;

Session altered.

Case 1: A parallel hint with specified DOP

nkarag@DWHPRD> merge /*+ append parallel(t 16) */ into lala t

  2  using(select * from dba_objects) s

  3  on(T.OBJECT_ID = S.OBJECT_ID)

  4  when matched then update

  5      set T.OBJECT_NAME = S.OBJECT_NAME

  6  /

1678791 rows merged.

From a different session I do "sql monitoring" to find the actual DOP assigned by Oracle

nkarag@DWHPRD> select  SID, PX_MAXDOP, PX_SERVERS_REQUESTED, PX_SERVERS_ALLOCATED

  2  from gv$sql_monitor

  3  where

  4  sql_id = '&sql_id'

  5  and px_maxdop is not null

  6  /

Enter value for sql_id: 4m1j50zy33dnk

old   4: sql_id = '&sql_id'

new   4: sql_id = '4m1j50zy33dnk'

  SID  PX_MAXDOP PX_SERVERS_REQUESTED PX_SERVERS_ALLOCATED

----- ---------- -------------------- --------------------

   12         16                   32                   32

So in this case, we see that we have asked for a DOP of 16 and indeed that is the DOP that we got (although the table DOP is 4)

Case 2: A parallel hint with no specified DOP

nkarag@DWHPRD> merge /*+ append parallel(t) */ into lala t

  2  using(select * from dba_objects) s

  3  on(T.OBJECT_ID = S.OBJECT_ID)

  4  when matched then update

  5      set T.OBJECT_NAME = S.OBJECT_NAME

  6  /

1678791 rows merged.

From a different session I do "sql monitoring" to find the actual DOP assigned by Oracle

nkarag@DWHPRD> select  SID, PX_MAXDOP, PX_SERVERS_REQUESTED, PX_SERVERS_ALLOCATED

  2  from gv$sql_monitor

  3  where

  4  sql_id = '&sql_id'

  5  and px_maxdop is not null

  6  /

Enter value for sql_id: b6xx2tyv7vzvu

old   4: sql_id = '&sql_id'

new   4: sql_id = 'b6xx2tyv7vzvu'

  SID  PX_MAXDOP PX_SERVERS_REQUESTED PX_SERVERS_ALLOCATED

----- ---------- -------------------- --------------------

   12         64                  192                  128

So in this case, we see that Oracle has computed the "appropriate" DOP for our merge statement, which in fact is quite larger than the table DOP (which is 4). This means that one should be carefull when specifying a parallel hint with no DOP specification, since the parallelism might go wild!

But when will Oracle use the table default DOP (which is 4 in our example)? This is our 3rd case.

Case 3: No hint (parallel) specified but parallel dml enabled

nkarag@DWHPRD> merge /*+ append */ into lala t

  2  using(select * from dba_objects) s

  3  on(T.OBJECT_ID = S.OBJECT_ID)

  4  when matched then update

  5      set T.OBJECT_NAME = S.OBJECT_NAME

  6  /

1678791 rows merged.

From a different session I do "sql monitoring" to find the actual DOP assigned by Oracle.

nkarag@DWHPRD> select  SID, PX_MAXDOP, PX_SERVERS_REQUESTED, PX_SERVERS_ALLOCATED

  2  from gv$sql_monitor

  3  where

  4  sql_id = '&sql_id'

  5  and px_maxdop is not null

  6  /

Enter value for sql_id: 4cmsgumfqgxdp

old   4: sql_id = '&sql_id'

new   4: sql_id = '4cmsgumfqgxdp'

  SID  PX_MAXDOP PX_SERVERS_REQUESTED PX_SERVERS_ALLOCATED

----- ---------- -------------------- --------------------

   12          4                    8                    8

So we see that when no parallel hint was added and at the same time parallel dml was enabled, Oracle assigned the default DOP of the table.

Enjoy,

OL