Keep in mind that dbms_parallel_execute will take your single transaction and
split it into x number of smaller transactions, meaning other users in the
system may see “inconsistent” results while this runs.
Thanks,
Scott
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On ;
Behalf Of Dominic Brooks
Sent: Wednesday, November 16, 2016 8:56 AM
To: bednar@xxxxxx; oracle-l@xxxxxxxxxxxxx
Subject: RE: parallel dml doesn't working
Dbms_parallel_execute then?
Sent from my Windows Phone
________________________________
From: Marian Bednar<mailto:bednar@xxxxxx>
Sent: 16/11/2016 13:37
To: oracle-l@xxxxxxxxxxxxx<mailto:oracle-l@xxxxxxxxxxxxx>
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@xxxxxx<mailto:bednar@xxxxxx>>
To: ,
Date: 16. 11. 2016 14:16
Subject: parallel dml doesn't working
Sent by:
oracle-l-bounce@xxxxxxxxxxxxx<mailto: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
Notice of Confidentiality: **This E-mail and any of its attachments may contain
Lincoln National Corporation proprietary information, which is privileged,
confidential,
or subject to copyright belonging to the Lincoln National Corporation family of
companies. This E-mail is intended solely for the use of the individual or
entity to
which it is addressed. If you are not the intended recipient of this E-mail,
you are
hereby notified that any dissemination, distribution, copying, or action taken
in
relation to the contents of and attachments to this E-mail is strictly
prohibited
and may be unlawful. If you have received this E-mail in error, please notify
the
sender immediately and permanently delete the original and any copy of this
E-mail
and any printout. Thank You.**
