db 12.1.0.2
I am trying to delete rows from non-partitionend table, but delete is not
run in parallel.
I've tried to enable PDML with hint and also with alter session, but
still not run in paralell.
I cannot find anything about this note "PDML disabled because single
fragment or non partitioned table used".
(Table has 16 indexes, so their maintenance during delete is quite
expensive...)
12:19:23 SQL> delete /*+ full(acc_tx) parallel(6) enable_parallel_dml */
from ACC_TX where tx_state=3;
1642635 rows deleted.
Elapsed: 00:12:36.10
Execution Plan
----------------------------------------------------------
Plan hash value: 1915889986
---------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
Time | TQ |IN-OUT| PQ Distrib |
---------------------------------------------------------------------------------------------------------------
| 0 | DELETE STATEMENT | | 1657K| 208M| 16417 (1)|
00:00:01 | | | |
| 1 | DELETE | ACC_TX | | | | |
| | |
| 2 | PX COORDINATOR | | | | | |
| | |
| 3 | PX SEND QC (RANDOM)| :TQ10000 | 1657K| 208M| 16417 (1)|
00:00:01 | Q1,00 | P->S | QC (RAND) |
| 4 | PX BLOCK ITERATOR | | 1657K| 208M| 16417 (1)|
00:00:01 | Q1,00 | PCWC | |
|* 5 | TABLE ACCESS FULL| ACC_TX | 1657K| 208M| 16417 (1)|
00:00:01 | Q1,00 | PCWP | |
---------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - filter("TX_STATE"=3)
Note
-----
- dynamic statistics used: dynamic sampling (level=AUTO)
- Degree of Parallelism is 6 because of hint
- PDML disabled because single fragment or non partitioned table used
Statistics
----------------------------------------------------------
102 recursive calls
60567425 db block gets
316125 consistent gets
570857 physical reads
6523670524 redo size
572 bytes sent via SQL*Net to client
820 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
1642635 rows processed
Thanks.
Marian
