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,
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.