Re: parallel dml doesn't working

From: Marian Bednar <bednar_at_nbs.sk>
Date: Wed, 16 Nov 2016 14:34:48 +0100
Message-ID: <OF87A4023D.6FF2CA17-ONC125806D.004A5BFD-C125806D.004A9903_at_nbs.sk>

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_at_nbs.sk> To: ,
Date: 16. 11. 2016 14:16

Subject:        parallel dml doesn't working
Sent by:        oracle-l-bounce_at_freelists.org

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:34:48 CET