Hello All,
In our production environment, one DML update is running in parallel and which is sporadic in nature.
Please note the session is enabled for parallel dml.
Find below one pseudo code of that update statement.
---------------
ALTER SESSION ENABLE PARALLEL DML;
UPDATE TABLE_1 A
SET
A.COLUMN_1 =
(SELECT COUNT(*)
FROM TABLE_2 B
WHERE B.KEY_COL = A.KEY_COL)
WHERE
A.KEY_COL IN
(SELECT DISTINCT KEY_COL FROM TABLE_2)------------
The parallel degree for TABLE_1 (target table and partitioned) is set as 1
The parallel degree for TABLE_2 (used in predicate) is set as DEFAULT (16)
Database Version - 11.2.0.4.0
-----------
parallel_adaptive_multi_user = FALSE
parallel_degree_limit = 16
parallel_degree_policy = auto
parallel_force_local FALSE
----------
My understanding was, if we don't add parallel hint (UPDATE /*+ parallel */) and DEGREE is set as 1 for target table , then DML Update will not execute in parallel although the session is enabled for parallel DML.
Can you please let me know if my understanding is correct ?
Appreciate your help.
Not quite...
There are two parts to your SQL:
- The update
- The query to access the other table
The optimizer can parallelize both or just the query and not the update.
If you have parallel_degree_policy set to manual or limited, then the optimizer will choose parallelism based on the table settings or hint.
Here we have parallel 1 for the target table and 2 for the source. So the query is parallized, but not the update:
alter session enable parallel dml;
alter session enable parallel query;
alter session set statistics_level = all;
set serveroutput off
create table t1 (x) as
select rownum x from dual
connect by level <= 10000;
create table t2 (x) as
select rownum x from dual
connect by level <= 10000;
alter table t2 parallel ;
exec dbms_stats.gather_table_stats(user, 'T1');
exec dbms_stats.gather_table_stats(user, 'T2');
alter session set parallel_degree_policy = manual;
update t1
set x = (
select count(*) from t2
);
select * from table(dbms_xplan.display_cursor(null, null, 'BASIC LAST +PARALLEL +NOTE'));
PLAN_TABLE_OUTPUT
EXPLAINED SQL STATEMENT:
------------------------
update t1 set x = ( select count(*) from t2 )
Plan hash value: 3079932949
-------------------------------------------------------------------------
| Id | Operation | Name | TQ |IN-OUT| PQ Distrib |
-------------------------------------------------------------------------
| 0 | UPDATE STATEMENT | | | | |
| 1 | UPDATE | T1 | | | |
| 2 | TABLE ACCESS FULL | T1 | | | |
| 3 | SORT AGGREGATE | | | | |
| 4 | PX COORDINATOR | | | | |
| 5 | PX SEND QC (RANDOM) | :TQ10000 | Q1,00 | P->S | QC (RAND) |
| 6 | SORT AGGREGATE | | Q1,00 | PCWP | |
| 7 | PX BLOCK ITERATOR | | Q1,00 | PCWC | |
| 8 | TABLE ACCESS FULL| T2 | Q1,00 | PCWP | |
-------------------------------------------------------------------------
Note
-----
- PDML disabled because object is not decorated with parallel clause
commit;But you also have parallel_degree_policy = auto. So the database will automatically determine the appropriate degree of parallelism. This overrides whatever you set as the parallel level for the table:
alter session set parallel_degree_policy = auto;
update t1
set x = (
select count(*) from t2
);
select * from table(dbms_xplan.display_cursor(null, null, 'BASIC LAST +PARALLEL +NOTE'));
PLAN_TABLE_OUTPUT
EXPLAINED SQL STATEMENT:
------------------------
update t1 set x = ( select count(*) from t2 )
Plan hash value: 2278366140
------------------------------------
| Id | Operation | Name |
------------------------------------
| 0 | UPDATE STATEMENT | |
| 1 | UPDATE | T1 |
| 2 | TABLE ACCESS FULL | T1 |
| 3 | SORT AGGREGATE | |
| 4 | TABLE ACCESS FULL| T2 |
------------------------------------
Note
-----
- automatic DOP: Computed Degree of Parallelism is 1 because of parallel threshold
- PDML disabled because object is not decorated with parallel clause
commit;
alter table t1 parallel 2;
update t1
set x = (
select count(*) from t2
);
select * from table(dbms_xplan.display_cursor(null, null, 'BASIC LAST +PARALLEL +NOTE'));
PLAN_TABLE_OUTPUT
EXPLAINED SQL STATEMENT:
------------------------
update t1 set x = ( select count(*) from t2 )
Plan hash value: 2278366140
------------------------------------
| Id | Operation | Name |
------------------------------------
| 0 | UPDATE STATEMENT | |
| 1 | UPDATE | T1 |
| 2 | TABLE ACCESS FULL | T1 |
| 3 | SORT AGGREGATE | |
| 4 | TABLE ACCESS FULL| T2 |
------------------------------------
Note
-----
- automatic DOP: Computed Degree of Parallelism is 1 because of parallel threshold
- PDML disabled because object is not decorated with parallel clause
commit;If your table grows large enough or you lower the threshold for parallelizing low enough (default 10s), then the database will choose the level of parallelism for you.
Let's lower the threshold to 1s and make the table look huge by setting the stats to 1 billion rows:
alter session set parallel_min_time_threshold = 1;
exec dbms_stats.set_table_stats( user, 'T1' , numrows => 1000000000, numblks => 1000000 );
update t1
set x = (
select count(*) from t2
);
select * from table(dbms_xplan.display_cursor(null, null, 'BASIC LAST +PARALLEL +NOTE'));
PLAN_TABLE_OUTPUT
EXPLAINED SQL STATEMENT:
------------------------
update t1 set x = ( select count(*) from t2 )
Plan hash value: 4037313505
-----------------------------------------------------------------------
| Id | Operation | Name | TQ |IN-OUT| PQ Distrib |
-----------------------------------------------------------------------
| 0 | UPDATE STATEMENT | | | | |
| 1 | PX COORDINATOR | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10000 | Q1,00 | P->S | QC (RAND) |
| 3 | UPDATE | T1 | Q1,00 | PCWP | |
| 4 | PX BLOCK ITERATOR | | Q1,00 | PCWC | |
| 5 | TABLE ACCESS FULL| T1 | Q1,00 | PCWP | |
| 6 | SORT AGGREGATE | | | | |
| 7 | TABLE ACCESS FULL| T2 | | | |
-----------------------------------------------------------------------
Note
-----
- automatic DOP: Computed Degree of Parallelism is 4 because of degree limit
commit;In this case the optimizer chose a parallel degree of 4.
To use a particular level of parallelism, you need to use a hint:
update /*+ parallel (2) */t1
set x = (
select count(*) from t2
);
select * from table(dbms_xplan.display_cursor(null, null, 'BASIC LAST +PARALLEL +NOTE'));
PLAN_TABLE_OUTPUT
EXPLAINED SQL STATEMENT:
------------------------
update /*+ parallel (2) */t1 set x = ( select count(*) from t2 )
Plan hash value: 4037313505
-----------------------------------------------------------------------
| Id | Operation | Name | TQ |IN-OUT| PQ Distrib |
-----------------------------------------------------------------------
| 0 | UPDATE STATEMENT | | | | |
| 1 | PX COORDINATOR | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10000 | Q1,00 | P->S | QC (RAND) |
| 3 | UPDATE | T1 | Q1,00 | PCWP | |
| 4 | PX BLOCK ITERATOR | | Q1,00 | PCWC | |
| 5 | TABLE ACCESS FULL| T1 | Q1,00 | PCWP | |
| 6 | SORT AGGREGATE | | | | |
| 7 | TABLE ACCESS FULL| T2 | | | |
-----------------------------------------------------------------------
Note
-----
- Degree of Parallelism is 2 because of hint
commit;Read more about auto degree of parallelism at:
https://blogs.oracle.com/datawarehousing/what-is-auto-dop https://blogs.oracle.com/datawarehousing/configuring-and-controlling-auto-dop https://blogs.oracle.com/datawarehousing/optimizer-processing-rates-for-auto-dop