The following is a straight, continuous, untouched, cut-n-paste from an SQL*Plus session on 12.1.0.2. How come the update doesn’t execute in parallel – noting that parallel DML has been enabled (in fact forced) and the tablescan to identify rows to be updated does execute in parallel ?
rem
rem Script: parallel_dml_blocking.sql
rem Author: Jonathan Lewis
rem Dated: Mar 2017
rem
rem Last tested
rem 12.1.0.2
rem 11.2.0.4
rem
SQL> desc t1
Name Null? Type
------------------------------------------------------------------------------- -------- ----------------------
OWNER NOT NULL VARCHAR2(128)
OBJECT_NAME NOT NULL VARCHAR2(128)
SUBOBJECT_NAME VARCHAR2(128)
OBJECT_ID NOT NULL NUMBER
DATA_OBJECT_ID NUMBER
OBJECT_TYPE VARCHAR2(23)
CREATED NOT NULL DATE
LAST_DDL_TIME NOT NULL DATE
TIMESTAMP VARCHAR2(19)
STATUS VARCHAR2(7)
TEMPORARY VARCHAR2(1)
GENERATED VARCHAR2(1)
SECONDARY VARCHAR2(1)
NAMESPACE NOT NULL NUMBER
EDITION_NAME VARCHAR2(128)
SHARING VARCHAR2(13)
EDITIONABLE VARCHAR2(1)
ORACLE_MAINTAINED VARCHAR2(1)
SQL> select * from t1 minus select * from all_objects;
OWNER OBJECT_NAME SUBOBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE CREATED LAST_DDL_
--------------- -------------------- ---------------------- ---------- -------------- ----------------------- --------- ---------
TIMESTAMP STATUS T G S NAMESPACE EDITION_NAME SHARING E O
------------------- ------- - - - --------------- -------------------- ------------- - -
TEST_USER T1 159331 159331 TABLE 09-MAR-17 09-MAR-17
2017-03-09:22:16:36 VALID N N N 1 NONE N
1 row selected.
SQL> alter session force parallel dml;
Session altered.
SQL> set serveroutput off
SQL> update t1 set object_name = lower(object_name) where data_object_id is null;
78324 rows updated.
SQL> select * from table(dbms_xplan.display_cursor);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------
SQL_ID b16abyv8p2790, child number 0
-------------------------------------
update t1 set object_name = lower(object_name) where data_object_id is
null
Plan hash value: 121765358
---------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
---------------------------------------------------------------------------------------------------------------
| 0 | UPDATE STATEMENT | | | | 26 (100)| | | | |
| 1 | UPDATE | T1 | | | | | | | |
| 2 | PX COORDINATOR | | | | | | | | |
| 3 | PX SEND QC (RANDOM)| :TQ10000 | 78324 | 2141K| 26 (0)| 00:00:01 | Q1,00 | P->S | QC (RAND) |
| 4 | PX BLOCK ITERATOR | | 78324 | 2141K| 26 (0)| 00:00:01 | Q1,00 | PCWC | |
|* 5 | TABLE ACCESS FULL| T1 | 78324 | 2141K| 26 (0)| 00:00:01 | Q1,00 | PCWP | |
---------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access(:Z>=:Z AND :Z<=:Z)
filter("DATA_OBJECT_ID" IS NULL)
Note
-----
- Degree of Parallelism is 8 because of table property
- PDML disabled because single fragment or non partitioned table used
29 rows selected.
SQL> select * from v$pq_tqstat;
DFO_NUMBER TQ_ID SERVER_TYPE NUM_ROWS BYTES OPEN_TIME AVG_LATENCY WAITS TIMEOUTS PROCESS INSTANCE CON_ID
---------- ---------- --------------- ---------- ---------- ---------- ----------- ---------- ---------- --------------- -------- ----------
1 0 Producer 8997 363737 ########## 0 14 0 P004 1 0
9721 409075 ########## 0 12 0 P007 1 0
9774 408591 ########## 0 12 0 P005 1 0
9844 396816 ########## 0 12 0 P003 1 0
9965 403926 ########## 0 13 0 P006 1 0
9727 388829 ########## 0 12 0 P002 1 0
9951 399162 ########## 0 14 0 P001 1 0
10345 408987 ########## 0 13 0 P000 1 0
Consumer 78324 3179123 ########## 0 0 0 QC 1 0
9 rows selected.
If you want to see the fully parallel plan, it would look like this (after running the query above against v$pq_tqstat I executed one statement that I’m not showing before carrying on with the statements below):
SQL> update t1 set object_name = lower(object_name) where data_object_id is null;
78324 rows updated.
SQL> select * from table(dbms_xplan.display_cursor);
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------
SQL_ID b16abyv8p2790, child number 0
-------------------------------------
update t1 set object_name = lower(object_name) where data_object_id is
null
Plan hash value: 3991856572
---------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
---------------------------------------------------------------------------------------------------------------
| 0 | UPDATE STATEMENT | | | | 26 (100)| | | | |
| 1 | PX COORDINATOR | | | | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10000 | 78324 | 2141K| 26 (0)| 00:00:01 | Q1,00 | P->S | QC (RAND) |
| 3 | UPDATE | T1 | | | | | Q1,00 | PCWP | |
| 4 | PX BLOCK ITERATOR | | 78324 | 2141K| 26 (0)| 00:00:01 | Q1,00 | PCWC | |
|* 5 | TABLE ACCESS FULL| T1 | 78324 | 2141K| 26 (0)| 00:00:01 | Q1,00 | PCWP | |
---------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access(:Z>=:Z AND :Z<=:Z)
filter("DATA_OBJECT_ID" IS NULL)
Note
-----
- Degree of Parallelism is 8 because of table property
28 rows selected.
SQL> select object_name, object_type from user_objects;
OBJECT_NAME OBJECT_TYPE
-------------------- -----------------------
T1 TABLE
1 row selected.
Answer (late-breaking)
Thanks to a very recent comment, I’ve just discovered (26th May 2017) that I never got around to adding the answer (for my case) to the body of the posting. Franck Pachot came very close with a suggestion about an invisible LOB column, but (in my reply to him) I owned up to having had a LOB column that I had marked as unused.
A LOB column is a documented restriction on parallel execution – though the manuals (perfectly reasonably, in my view) leave it to you to realise that it’s still a restriction even if you’ve marked the column unusable.
Oracle Scratchpad 
Hi Jonathan,
Did you have a LOB column (invisible as we don’t see it in desc)? PDML UPDATE on SecureFile LOB is possible in 12.1 only if table is partitioned. Then the hidden command is an alter table drop column.
Regards,
Franck.
Comment by @FranckPachot — March 9, 2017 @ 11:24 pm GMT Mar 9,2017 |
As it seems there are many solution, I’ve build the test case for mine (invisible BLOB) hoping that code formatting is ok
SQLcl: Release 4.2.0 Production on Fri Mar 10 11:50:30 2017 Copyright (c) 1982, 2017, Oracle. All rights reserved. SQL> set sqlformat ansiconsole SQL> connect demo/demo@//localhost/PDB1; Connected. SQL> create table T1 as select all_objects.* from all_objects; Table T1 created. SQL> alter table T1 add x clob invisible; Table T1 altered. SQL> select * from T1 minus select * from all_objects; no rows selected SQL> select * from T1 minus select * from all_objects; no rows selected SQL> alter session force parallel dml; Session altered. SQL> update T1 set object_name = lower(object_name) where data_object_id is null; 63,751 rows updated. SQL> select * from table(dbms_xplan.display_cursor); PLAN_TABLE_OUTPUT ----------------- SQL_ID dw670mt80tdvx, child number 0 ------------------------------------- update T1 set object_name = lower(object_name) where data_object_id is null Plan hash value: 121765358 --------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib | --------------------------------------------------------------------------------------------------------------- | 0 | UPDATE STATEMENT | | | | 207 (100)| | | | | | 1 | UPDATE | T1 | | | | | | | | | 2 | PX COORDINATOR | | | | | | | | | | 3 | PX SEND QC (RANDOM)| :TQ10000 | 63751 | 2365K| 207 (0)| 00:00:01 | Q1,00 | P->S | QC (RAND) | | 4 | PX BLOCK ITERATOR | | 63751 | 2365K| 207 (0)| 00:00:01 | Q1,00 | PCWC | | |* 5 | TABLE ACCESS FULL| T1 | 63751 | 2365K| 207 (0)| 00:00:01 | Q1,00 | PCWP | | --------------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 5 - access(:Z>=:Z AND :Z<=:Z) filter("DATA_OBJECT_ID" IS NULL) Note ----- - Degree of Parallelism is 2 because of table property - PDML disabled because single fragment or non partitioned table used 29 rows selected. SQL> select * from v$pq_tqstat; DFO_NUMBER TQ_ID SERVER_TYPE NUM_ROWS BYTES OPEN_TIME AVG_LATENCY WAITS TIMEOUTS PROCESS INSTANCE CON_ID ---------- ----- ----------- -------- ----- --------- ----------- ----- -------- ------- -------- ------ 1 0 Producer 32844 1659762 0 0 16 3 P001 1 3 1 0 Producer 30907 1655999 0 0 14 1 P000 1 3 1 0 Consumer 63751 3315761 0 0 64 0 QC 1 3 SQL> alter table T1 drop column x; Table T1 altered. SQL> update T1 set object_name = lower(object_name) where data_object_id is null; 63,751 rows updated. SQL> select * from table(dbms_xplan.display_cursor); PLAN_TABLE_OUTPUT ----------------- SQL_ID dw670mt80tdvx, child number 0 ------------------------------------- update T1 set object_name = lower(object_name) where data_object_id is null Plan hash value: 3991856572 --------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib | --------------------------------------------------------------------------------------------------------------- | 0 | UPDATE STATEMENT | | | | 207 (100)| | | | | | 1 | PX COORDINATOR | | | | | | | | | | 2 | PX SEND QC (RANDOM) | :TQ10000 | 63751 | 2365K| 207 (0)| 00:00:01 | Q1,00 | P->S | QC (RAND) | | 3 | UPDATE | T1 | | | | | Q1,00 | PCWP | | | 4 | PX BLOCK ITERATOR | | 63751 | 2365K| 207 (0)| 00:00:01 | Q1,00 | PCWC | | |* 5 | TABLE ACCESS FULL| T1 | 63751 | 2365K| 207 (0)| 00:00:01 | Q1,00 | PCWP | | --------------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 5 - access(:Z>=:Z AND :Z<=:Z) filter("DATA_OBJECT_ID" IS NULL) Note ----- - Degree of Parallelism is 2 because of table property 28 rows selected. SQL> select object_name, object_type from user_objects; OBJECT_NAME OBJECT_TYPE ----------- ----------- T1 TABLE SQL> exit Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit ProductionComment by @FranckPachot — March 10, 2017 @ 10:58 am GMT Mar 10,2017 |
Franck,
There are, indeed, many reasons why the update will serialise – and the manual (a) doesn’t list them all (b) descibes some of them badly and (c) lists some which aren’t true. The manual also fails to point out which Note you might get for which issue – and the “single fragment” note applies to some very particular circumstances.
Just to make life harder for you – here’s the output from a call to dbms_metadata.get_ddl() for the table before running the first update:
DBMS_METADATA.GET_DDL('TABLE','T1') -------------------------------------------------------------------------------- CREATE TABLE "TEST_USER"."T1" ( "OWNER" VARCHAR2(128) NOT NULL ENABLE, "OBJECT_NAME" VARCHAR2(128) NOT NULL ENABLE, "SUBOBJECT_NAME" VARCHAR2(128), "OBJECT_ID" NUMBER NOT NULL ENABLE, "DATA_OBJECT_ID" NUMBER, "OBJECT_TYPE" VARCHAR2(23), "CREATED" DATE NOT NULL ENABLE, "LAST_DDL_TIME" DATE NOT NULL ENABLE, "TIMESTAMP" VARCHAR2(19), "STATUS" VARCHAR2(7), "TEMPORARY" VARCHAR2(1), "GENERATED" VARCHAR2(1), "SECONDARY" VARCHAR2(1), "NAMESPACE" NUMBER NOT NULL ENABLE, "EDITION_NAME" VARCHAR2(128), "SHARING" VARCHAR2(13), "EDITIONABLE" VARCHAR2(1), "ORACLE_MAINTAINED" VARCHAR2(1) ) SEGMENT CREATION IMMEDIATE PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS NOLOGGING STORAGE(INITIAL 8388608 NEXT 8388608 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "TEST_8K_ASSM" 1 row selected.If there were an invisible LOB column it would appear here with its definition, but marked as INVISIBLE.
You were so close, though, that it would be uncharitable of me to lead you astray. There was a CLOB, but I had set it unused. That way it won’t show in the dbms_metadata call, it could only be seen in user_tab_cols (with a funny “date and time” name) or user_objects – though not in user_lobs.
Comment by Jonathan Lewis — March 10, 2017 @ 11:22 am GMT Mar 10,2017 |
Hi,
DML could use parallel on partitioned table only. t1 table is not partitioned. Is it a such case? ;-)
Beste Regards,
Lucas
Comment by Lucas B. — March 9, 2017 @ 11:41 pm GMT Mar 9,2017 |
*t1 table is not partitioned or have only one partition.
Comment by Lucas B. — March 10, 2017 @ 12:18 am GMT Mar 10,2017 |
Second shot just before sleep – there was a trigger.
Good night,
Lucas B.
Comment by Lucas B. — March 10, 2017 @ 12:25 am GMT Mar 10,2017 |
Lucas,
Parallel DML is allowed on non-partitioned table – with some restrictions, and triggers are one of the things that can result in parallel DML being disabled.
Unfortunately the manuals are not sufficiently informative about triggers – so it’s easy to be fooled if you don’t do exhaustive testing. There is some connection between the type of DML and the type of trigger, for example my update will not be affected by the presence of a “before INSERT for each row” trigger but it will serialise if there’s a “before UPDATE for each row” trigger.
Some of the conflicts will, of course, appear obvious AFTER the event and then some of the obvious ones that don’t cause problems at first sight might cause problems in different circumstances – what’s the difference, for example, between and UPDATE and a delete followed by an INSERT if row movement is enabled ? (It’s interesting to note that some of the restrictions apply to tables where row movement is enabled).
Comment by Jonathan Lewis — March 10, 2017 @ 11:45 am GMT Mar 10,2017
Hi Jonathan,
Looking at this line from the serial plan – “PDML disabled because single fragment or non partitioned table used”
I feel it has to do something with bitmap indexes
regards
Srivenu Kadiyala
Comment by Srivenu KADIYALA — March 10, 2017 @ 4:56 am GMT Mar 10,2017 |
Srivenu,
The manuals do make the claim that PDML is not supported on a table with bitmap indexes if the table is not partitioned. This doesn’t seem to be true in 12c; here’s the plan for my update after creating a bitmap index on the column I’m updating:
(If I add a full(t1) hint the plan looks just like the first plan in the posting – the optimizer was a little fooled by the presence of the bitmap index to choose what is actually a bad plan thanks to its strategy of calculating the cost of an update like this as just the cost of identifying the required rowids)
Comment by Jonathan Lewis — March 10, 2017 @ 11:38 am GMT Mar 10,2017 |
11.2.0.4 Database:
SQL> create table t1 as select * from dba_objects; Table created. SQL> alter table t1 add constraint t1_u unique(object_id) deferrable initially deferred enable; Table altered. SQL> alter session force parallel dml; Session altered. SQL> update t1 set object_name = lower(object_name) where data_object_id is null; 260978 rows updated. SQL> select * from table(dbms_xplan.display_cursor); PLAN_TABLE_OUTPUT -------------------------------------------------------------- SQL_ID b16abyv8p2790, child number 0 ------------------------------------- update t1 set object_name = lower(object_name) where data_object_id is null Plan hash value: 121765358 --------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib | --------------------------------------------------------------------------------------------------------------- | 0 | UPDATE STATEMENT | | | | 379 (100)| | | | | | 1 | UPDATE | T1 | | | | | | | | | 2 | PX COORDINATOR | | | | | | | | | | 3 | PX SEND QC (RANDOM)| :TQ10000 | 246K| 18M| 379 (1)| 00:00:05 | Q1,00 | P->S | QC (RAND) | | 4 | PX BLOCK ITERATOR | | 246K| 18M| 379 (1)| 00:00:05 | Q1,00 | PCWC | | |* 5 | TABLE ACCESS FULL| T1 | 246K| 18M| 379 (1)| 00:00:05 | Q1,00 | PCWP | | --------------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 5 - access(:Z>=:Z AND :Z<=:Z) -- > hack to avoid formatting problem with wordpress filter("DATA_OBJECT_ID" IS NULL) Note ----- - dynamic sampling used for this statement (level=2) SQL> alter table t1 drop constraint t1_u drop index; Table altered. SQL> update t1 set object_name = lower(object_name) where data_object_id is null; 260978 rows updated. SQL> select * from table(dbms_xplan.display_cursor); PLAN_TABLE_OUTPUT -------------------------------------------------------------- SQL_ID b16abyv8p2790, child number 0 ------------------------------------- update t1 set object_name = lower(object_name) where data_object_id is null Plan hash value: 3991856572 --------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib | --------------------------------------------------------------------------------------------------------------- | 0 | UPDATE STATEMENT | | | | 379 (100)| | | | | | 1 | PX COORDINATOR | | | | | | | | | | 2 | PX SEND QC (RANDOM) | :TQ10000 | 246K| 18M| 379 (1)| 00:00:05 | Q1,00 | P->S | QC (RAND) | | 3 | UPDATE | T1 | | | | | Q1,00 | PCWP | | | 4 | PX BLOCK ITERATOR | | 246K| 18M| 379 (1)| 00:00:05 | Q1,00 | PCWC | | |* 5 | TABLE ACCESS FULL| T1 | 246K| 18M| 379 (1)| 00:00:05 | Q1,00 | PCWP | | --------------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 5 - access(:Z>=:Z AND :Z<=:Z) -- > hack to avoid formatting problem with wordpress filter("DATA_OBJECT_ID" IS NULL) Note ----- - dynamic sampling used for this statement (level=2) 28 rows selected.Regards,
Arian
Comment by Arian — March 10, 2017 @ 10:07 am GMT Mar 10,2017 |
Arian,
Thanks for the suggestion.
Deferrable constraints can cause the update step to serialise, but for 12.1.0.2 the Note section makes a specific statement about it, viz:
PDML disabled because deferrable constraints are used
Comment by Jonathan Lewis — March 10, 2017 @ 11:29 am GMT Mar 10,2017 |
One more reason to upgrade our databases :-)
Thank you.
Comment by Arian — March 14, 2017 @ 11:06 pm GMT Mar 14,2017 |
Dear Jonathan
Thank you for this post, I have been looking for an explanation for the “PDML disabled because single fragment or non partitioned table used” message since middle of last year.
My case is a DELETE on a view which joins the range partitioned table to an global temp table containing the relevant range IDs.
The reason why I am looking into it is the fact that the serial execution does Bloom pruning, the parallel execution does a PARTITION RANGE ALL if any triggers exis on the view.
https://docs.oracle.com/database/121/VLDBG/GUID-6626C70C-876C-47A4-8C01-9B66574062D8.htm#GUID-6626C70C-876C-47A4-8C01-9B66574062D8
says that PDML is not supported on tables with triggers but views are not explicitely mentioned.
The plans below are for serial, parallel with triggers and parallel without triggers in that order.
Any ideas are welcome.
br, Patrik
Comment by Patrik Kleindl — May 24, 2017 @ 2:23 pm BST May 24,2017 |
Patrik,
There are many places in the manuals where the descriptions are ambiguous or not quite complete. If you find that creating a trigger on a view results in the the DML going serial and the manual says that triggers on tables block parallel DML then it’s a reasonably safe bet that the manuals should say “tables or views” but the author didn’t think about the combination of DML and views.
If what you see contradicts the manuals I’d raise an SR; if the manuals seem to be incomplete but the case is really important I’d raise an SR.
In your case I’d also try to create a very small test to see if it’s any particular feature of the view-based trigger that has the effect just in case the current trigger can be modified to a form where the restriction doesn’t appear. (I’d be pessimistic about this, though, because I’ve seen several examples where Oracle has code takes a generic approach to solving a problem that blocks activities that appear to be special “safe” cases).
Comment by Jonathan Lewis — May 26, 2017 @ 11:27 am BST May 26,2017 |
Hello Jonathan,
sorry for the late reply but the SR took a while and led to some answers and more questions.
1) Oracle has confirmed that the usage of “instead of” triggers on views is treated the same way as on tables.
The documentation should be updated in a future version and the optimizer message for this case will be changed to
“PDML disabled because instead of triggers defined ” instead of the general and misleading message “PDML disabled because single fragment or non partitioned table used”
2) Our main problem was the fact that then plan with the disabled PDML also did not use the Bloom Filter and changed to a PARTITION RANGE ALL.
The answer from Oracle regarding this is worth sharing because it might be related to https://jonathanlewis.wordpress.com/2016/07/08/dml-and-bloom/:
“Initially, we didn’t allow bloom filtering/bloom pruning for DML queries at all.
Starting 12.1, we relaxed the restriction and allowed for the following case
– parallel DML only (not for serial DML)
– serial query
The customer case is a parallel query with serial DML, and bloom filter/pruning needs to be used in the parallel plan.
With DML, there might be cases where nodes under DML are restarted, but bloom filter/pruning do not support restart yet.
Bloom filter/pruning has to be executed only once if it’s part of parallel plan.
For parallel DML and serial query, we know that bloom filter/pruning will be executed only once and that’s why we allowed bloom filtering/pruning for those cases.
There are several cases even in non-DMLs where bloom filter/pruning is disabled due to the same reason that we don’t allow bloom pruning for the customer cases.”
So now we know why no Bloom Pruning is used which leads to the next problem:
3) Why the plan changes to PARTITION RANGE ALL and not PARTITION RANGE SUBQUERY could not be explained yet, I tried adapting the hint from the outline but it did not work
Even on SELECTs I get mixed results where similar queries alternate between those two.
A simplified test-case:
part_table is a range-partitioned table, partitions is a table which holds the partition IDs.
For me I get Bloom pruning on both queries when it is enabled, but PARTITION RANGE SUBQUERY on the first and PARTITION RANGE ALL on the second when I disable Bloom pruning.
Data types etc. are the same on partitions and p, also tried it with the same unique constraint/index.
Comment by Patrik Kleindl — July 31, 2017 @ 9:33 am BST Jul 31,2017 |
Patrik,
Thanks for the follow-up.
The explicit subquery version of your code may have lost something in copying, or maybe the WHERE simply shouldn’t be there.
The rules about when subquery pruning occurs are multi-part – something about percentage of rows from the driving table, percentage of partitions eliminated, and so on … I don’t remember the details – but I do remember thinking that the optimizer seemed to be very pessimistic about the possible benefits.
It’s possible that “unparsed” text for the two queries is slightly different and produces different arithmetic for the two cases, leading to subquery pruning being sufficiently valuable in just one of the cases.
I have the following notes from a demo script originally dated 2003, though last tested on 11.1.0.6 – so any comments may be out of date:
Comment by Jonathan Lewis — August 2, 2017 @ 11:10 am BST Aug 2,2017 |
Jonathan,
thank you for your reply.
Yes, the where condition got lost because I put it in < > brackets, the point was that the condition used was exactly the same and resulted in the same number for rows (3) on a table which had 70 partitions.
Thanks too for the hidden parameters, I will try to test if those make a difference.
I don’t have the example handy but I have seen cases when anything other than comparison with defined values seemed to cause very strange estimates for partition pruning. In some cases the optimizer regarded a full scan over several 100 partitions less costly than partition pruning which contained less keys than then number of partitions.
Comment by Patrik Kleindl — August 3, 2017 @ 3:50 pm BST Aug 3,2017 |
Jonathan,
I tried changing the suggested parameters but all I can manage is to break the cases where the subquery pruning already works.
Any straight join (a inner join b) will not use the subquery pruning, any join with a subquery (a inner join (select id from b where x)) seems to work, regardless if both variants return the same number of IDs.
First plan is the join with the original subquery.
Second plan is a join with a table created with the original subquery.
Third plan is a join with a subquery which shows that the number of rows returned from the subquery and the expected rows after the hash join don’t seem to matter.
Interestingly the cost is the same for all plans.
Comment by Patrik Kleindl — August 4, 2017 @ 8:54 am BST Aug 4,2017 |
Wow! I have a monster SQL statement that does an insert, joining several tables and views, and thanks to this post, I was able to determine that one of the views “poisoned” the Parallel DML!
That view was doing a WITH clause that had a MAX(event_id) clause in it that was probably causing the issue.
Now I just need to see if that view’s MAX(event_id) is needed with current/expected data.
Comment by Mark Stewart — November 18, 2020 @ 6:44 pm GMT Nov 18,2020 |
[…] Quiz Night 31 (March 2017): Why is this simple statement not updating using parallel DML? (The answer is the same even in 19.11.0.0) […]
Pingback by Parallel Execution Catalogue | Oracle Scratchpad — August 20, 2022 @ 3:00 pm BST Aug 20,2022 |
[…] Quiz Night 31 (March 2017): Why is this simple statement not updating using parallel DML? (The answer is the same even in 19.11.0.0) […]
Pingback by Quiz Catalogue | Oracle Scratchpad — August 20, 2022 @ 3:13 pm BST Aug 20,2022 |
[…] are, however, many reasons why Oracle will disallow parallel DML (e.g. this example from 2013) and some of them are listed in the documentation (Link is for 19c). Bear in mind that some things […]
Pingback by Parallel DML – not | Oracle Scratchpad — July 12, 2023 @ 2:21 pm BST Jul 12,2023 |