parallel dml doesn't working

From: Marian Bednar <bednar_at_nbs.sk>
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-l
Received on Wed Nov 16 2016 - 14:15:50 CET

Original text of this message