Skip to Main Content
  • Questions
  • How to properly assign ENABLE_PARALLEL_DML to a profile?

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, Javi.

Asked: January 13, 2022 - 5:12 pm UTC

Last updated: January 19, 2022 - 12:52 am UTC

Version: 19.0

Viewed 1000+ times

You Asked

Hi,

In a 19.0, I've found a daily update that lasts for more than 30 mins in a 3rd party software.
I would like to execute it in parallel.

PDML is disabled, and when I try to create a profile for that update, I cannot get the desired plan.

I made a small case to show my problem:

CREATE TABLE TESTCASE_PDML (keynum number, attribute1 number) PARALLEL 8;

--Load some data.
INSERT INTO TESTCASE_PDML 
SELECT ROWNUM, ROWNUM*1000 FROM DUAL CONNECT BY ROWNUM <=100;
commit;


--Plans for the update:
EXPLAIN PLAN FOR
UPDATE TESTCASE_PDML 
SET attribute1 = keynum * 1001

SELECT * FROM TABLE (DBMS_XPLAN.DISPLAY()); 


Plan hash value: 3488641984
 
------------------------------------------------------------------------------------
| Id  | Operation          | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | UPDATE STATEMENT   |               |   100 |  2600 |     2   (0)| 00:00:01 |
|   1 |  UPDATE            | TESTCASE_PDML |       |       |            |          |
|   2 |   TABLE ACCESS FULL| TESTCASE_PDML |   100 |  2600 |     2   (0)| 00:00:01 |
------------------------------------------------------------------------------------
 
Note
-----
   - dynamic statistics used: dynamic sampling (level=3)
   - automatic DOP: Computed Degree of Parallelism is 1 because of parallel threshold
   
 
EXPLAIN PLAN FOR
UPDATE /*+ parallel(8) */ TESTCASE_PDML 
SET attribute1 = keynum * 1001

SELECT * FROM TABLE (DBMS_XPLAN.DISPLAY()); 

Plan hash value: 2008974791
 
--------------------------------------------------------------------------------------------------------------------
| Id  | Operation             | Name          | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------------------
|   0 | UPDATE STATEMENT      |               |   100 |  2600 |     4   (0)| 00:00:01 |        |      |            |
|   1 |  UPDATE               | TESTCASE_PDML |       |       |            |          |        |      |            | 
|   2 |   PX COORDINATOR      |               |       |       |            |          |        |      |            |
|   3 |    PX SEND QC (RANDOM)| :TQ10000      |   100 |  2600 |     4   (0)| 00:00:01 |  Q1,00 | P->S | QC (RAND)  |
|   4 |     PX BLOCK ITERATOR |               |   100 |  2600 |     4   (0)| 00:00:01 |  Q1,00 | PCWC |            |
|   5 |      TABLE ACCESS FULL| TESTCASE_PDML |   100 |  2600 |     4   (0)| 00:00:01 |  Q1,00 | PCWP |            | 
--------------------------------------------------------------------------------------------------------------------
 
Note
-----
   - dynamic statistics used: dynamic sampling (level=3)
   - Degree of Parallelism is 8 because of hint 
   <b>- PDML is disabled in current session </b>
   
   
EXPLAIN PLAN FOR
UPDATE /*+ parallel(8) enable_parallel_dml*/ TESTCASE_PDML 
SET attribute1 = keynum * 1001  
 
SELECT * FROM TABLE (DBMS_XPLAN.DISPLAY());

Plan hash value: 727441780
 
--------------------------------------------------------------------------------------------------------------------
| Id  | Operation             | Name          | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------------------
|   0 | UPDATE STATEMENT      |               |   100 |  2600 |     4   (0)| 00:00:01 |        |      |            |
|   1 |  PX COORDINATOR       |               |       |       |            |          |        |      |            |
|   2 |   PX SEND QC (RANDOM) | :TQ10000      |   100 |  2600 |     4   (0)| 00:00:01 |  Q1,00 | P->S | QC (RAND)  |
|   3 |    UPDATE             | TESTCASE_PDML |       |       |            |          |  Q1,00 | PCWP |            | 
|   4 |     PX BLOCK ITERATOR |               |   100 |  2600 |     4   (0)| 00:00:01 |  Q1,00 | PCWC |            |
|   5 |      TABLE ACCESS FULL| TESTCASE_PDML |   100 |  2600 |     4   (0)| 00:00:01 |  Q1,00 | PCWP |            |
--------------------------------------------------------------------------------------------------------------------
 
Outline Data
-------------
 
  /*+
      BEGIN_OUTLINE_DATA
      FULL(@"UPD$1" "TESTCASE_PDML"@"UPD$1")
      OUTLINE_LEAF(@"UPD$1")
      ALL_ROWS
      OPT_PARAM('_fix_control' '8560951:1')
      OPT_PARAM('optimizer_dynamic_sampling' 3)
      DB_VERSION('19.1.0')
      OPTIMIZER_FEATURES_ENABLE('19.1.0')
      IGNORE_OPTIM_EMBEDDED_HINTS
      END_OUTLINE_DATA
  */
 
Note
-----
   - dynamic statistics used: dynamic sampling (level=3)
   - Degree of Parallelism is 8 because of hint


When I try to create a profile to assign both hints I no longer get the desired plan (727441780, with UPDATE inside COORDINATOR).


DECLARE
     l_sql               clob;
     BEGIN
        l_sql := 'UPDATE TESTCASE_PDML SET attribute1 = keynum * 1001';
        dbms_sqltune.import_sql_profile(sql_text => l_sql, 
        profile => sqlprof_attr(q'[PARALLEL (12)]',
                             q'[ENABLE_PARALLEL_DML]'),        
        name => 'enable_PDML_test',    
        force_match => false);
    END;
    

EXPLAIN PLAN FOR
UPDATE TESTCASE_PDML 
SET attribute1 = keynum * 1001;

SELECT * FROM TABLE (DBMS_XPLAN.DISPLAY(null, null, 'OUTLINE'));  

Plan hash value: 2008974791
 
--------------------------------------------------------------------------------------------------------------------
| Id  | Operation             | Name          | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------------------
|   0 | UPDATE STATEMENT      |               |   100 |  2600 |     4   (0)| 00:00:01 |        |      |            |
|   1 |  UPDATE               | TESTCASE_PDML |       |       |            |          |        |      |            | 
|   2 |   PX COORDINATOR      |               |       |       |            |          |        |      |            |
|   3 |    PX SEND QC (RANDOM)| :TQ10000      |   100 |  2600 |     4   (0)| 00:00:01 |  Q1,00 | P->S | QC (RAND)  |
|   4 |     PX BLOCK ITERATOR |               |   100 |  2600 |     4   (0)| 00:00:01 |  Q1,00 | PCWC |            |
|   5 |      TABLE ACCESS FULL| TESTCASE_PDML |   100 |  2600 |     4   (0)| 00:00:01 |  Q1,00 | PCWP |            |
--------------------------------------------------------------------------------------------------------------------
 
Outline Data
-------------
 
  /*+
      BEGIN_OUTLINE_DATA
      FULL(@"UPD$1" "TESTCASE_PDML"@"UPD$1")
      OUTLINE_LEAF(@"UPD$1")
      ALL_ROWS
      OPT_PARAM('_fix_control' '8560951:1')
      OPT_PARAM('optimizer_dynamic_sampling' 3)
      DB_VERSION('19.1.0')
      OPTIMIZER_FEATURES_ENABLE('19.1.0')
      IGNORE_OPTIM_EMBEDDED_HINTS
      END_OUTLINE_DATA
  */
 
Note
-----
   - dynamic statistics used: dynamic sampling (level=3)
   - Degree of Parallelism is 12 because of hint
   <b>- PDML is disabled in current session</b>
   - SQL profile "enable_PDML_test" used for this statement



Could you please show me how to properly create a profile to enable parallel dml?

Thanks in advance,

and Connor said...

I don't think you can activate "enable_parallel_dml" via outline (or any other hint insertion mechanism). In that sense, its a "fake" hint, ie, a shortcut.
I've tried in the past using SQL patch as well - and it seems to not allow it

SQL> create table t as select * from dba_objects;

Table created.

SQL> create index ix on t (object_id);

Index created.

SQL> set feedback on sql_id
SQL> update t
  2  set owner = lower(owner)
  3  where object_id < 100;

98 rows updated.

SQL_ID: 5w6u0n8ku317a
SQL> set feedback on
SQL> variable p varchar2(100);
SQL> begin :p := dbms_sqldiag.create_sql_patch(
  2       sql_id=>'5w6u0n8ku317a',
  3       hint_text=>' ENABLE_PARALLEL_DML PARALLEL(4)');
  4  end;
  5  /

PL/SQL procedure successfully completed.

SQL> print p

P
----------------------------------------------------------------------------------------------------
SYS_SQLPTCH_017e662bf8d70006

PL/SQL procedure successfully completed.

SQL> update t
  2  set owner = lower(owner)
  3  where object_id < 100;

98 rows updated.

SQL> select * from dbms_xplan.display_cursor();

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID  5w6u0n8ku317a, child number 0
-------------------------------------
update t set owner = lower(owner) where object_id < 100

Plan hash value: 917028044

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | UPDATE STATEMENT  |      |       |       |     3 (100)|          |
|   1 |  UPDATE           | T    |       |       |            |          |
|*  2 |   INDEX RANGE SCAN| IX   |     3 |    33 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------

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

   2 - access("OBJECT_ID"<100)

Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 1 (U - Unused (1))
---------------------------------------------------------------------------

   0 -  STATEMENT
         U -  PARALLEL(4)

Note
-----
   - SQL patch "SYS_SQLPTCH_017e662bf8d70006" used for this statement


30 rows selected.


Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Extreme measures

emaN, January 18, 2022 - 6:31 am UTC

SQL> alter session set sql_translation_profile=stp_test1;

Session altered.

SQL> alter system flush shared_pool;

System FLUSH altered.

SQL> EXPLAIN PLAN FOR
  2  UPDATE TESTCASE_PDML
  3* SET attribute1 = keynum * 1001;

Explained.

SQL> SELECT * FROM TABLE (DBMS_XPLAN.DISPLAY());

                                                                                                      PLAN_TABLE_OUTPUT
_______________________________________________________________________________________________________________________
Plan hash value: 3968084660

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

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)
   - Degree of Parallelism is 4 because of hint

17 rows selected.

Translation profile:
create or replace package stp_test1 as
procedure translate_sql(sql_text        in  clob,
                        translated_text out clob);
end stp_test1;
/

create or replace package body stp_test1 as
procedure translate_sql(sql_text        in  clob,
                        translated_text out clob) is
begin
  if sql_text like '%UPDATE TESTCASE_PDML%' -- includes EXPLAIN PLAN FOR ...
  then
    translated_text := replace(sql_text, 'UPDATE TESTCASE_PDML',
                               'UPDATE /*+ enable_parallel_dml parallel(4) */ TESTCASE_PDML');
  else
    translated_text := sql_text;
  end if;
end translate_sql;
end stp_test1;
/

begin
  dbms_sql_translator.create_profile('STP_TEST1');
  dbms_sql_translator.set_attribute ('STP_TEST1', 'FOREIGN_SQL_SYNTAX', 'FALSE');
  dbms_sql_translator.set_attribute ('STP_TEST1', 'TRANSLATOR', 'STP_TEST1');
end;
/

--exec dbms_sql_translator.drop_profile('STP_TEST1')
--drop package stp_test1;



Connor McDonald
January 19, 2022 - 12:52 am UTC

Nice stuff. I'd totally forgotten about translations ... somewhat embarrassing because I have an entire video on it :-)


More to Explore

Performance

Get all the information about database performance in the Database Performance guide.