parallel dml doesn't working
Date: Wed, 16 Nov 2016 14:15:50 +0100
Message-ID: <OF1B1B68F5.287E69D5-ONC125806D.00427B0C-C125806D.0048DC72_at_nbs.sk>
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
-- http://www.freelists.org/webpage/oracle-lReceived on Wed Nov 16 2016 - 14:15:50 CET