RE: parallel dml doesn't working

From: Dominic Brooks <dombrooks_at_hotmail.com>
Date: Wed, 16 Nov 2016 13:55:31 +0000
Message-ID: <VI1PR07MB1390164644A64419B44F1830A1BE0_at_VI1PR07MB1390.eurprd07.prod.outlook.com>

Dbms_parallel_execute then?

Sent from my Windows Phone

---

From: Marian Bednar<mailto:bednar_at_nbs.sk> Sent: ý16/ý11/ý2016 13:37
To: oracle-l_at_freelists.org<mailto:oracle-l_at_freelists.org> Subject: Re: parallel dml doesn't working

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:55:31 CET