probably I know the reason - table contains one BLOB column
from docs
For non-partitioned tables with LOB columns, parallel INSERT operations
are supported provided that the LOB columns are declared as SecureFiles
LOBs. Parallel UPDATE, DELETE, and MERGE operations on such tables are not
supported.
candidate for ER?
From: Marian Bednar <bednar@xxxxxx>
To: ,
Date: 16. 11. 2016 14:16
Subject: parallel dml doesn't working
Sent by: oracle-l-bounce@xxxxxxxxxxxxx
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
