DISCLAIMER 😀 : probably because of my previous job but I REALLY don’t like to write blog posts about potential or confirmed bugs in the Oracle code. This time is no exception, the focus is on the curious plan and not the bugs around it.
I have a SQL with an OR chain that undergoes OR-expansion transformation, executed in parallel. In the real case where I noticed it both decision (OR-expansion and parallel) were made by the optimizer, I’ll force them both to keep the testcase very simple.
Here is the single table:
drop table t1; create table t1 as select * from dba_objects; exec dbms_stats.gather_table_stats(user,'T1'); create index t_idx on t1(status);
and my SQL is
select /*+ USE_CONCAT(@SEL$1) PARALLEL(4) */ owner, count(*) mycount from t1 a where status between 'A' and 'B' or status = 'VALID' group by owner;
The USE_CONCAT hint will force the OR-expansion, it works in this case even though it’s VERY hard to manually write it properly (specially after it was enhanced to be semantically correct, bug fix 8429273).
The execution plan is way more complex than I would expect
--------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 15 | 195 | 47 (7)| 00:00:01 | | | |
| 1 | PX COORDINATOR | | | | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ20002 | 15 | 195 | | | Q2,02 | P->S | QC (RAND) |
| 3 | HASH GROUP BY | | 15 | 195 | | | Q2,02 | PCWP | |
| 4 | PX RECEIVE | | 15 | 195 | | | Q2,02 | PCWP | |
| 5 | PX SEND HASH | :TQ20001 | 15 | 195 | | | Q2,01 | P->P | HASH |
| 6 | HASH GROUP BY | | 15 | 195 | | | Q2,01 | PCWP | |
| 7 | CONCATENATION | | | | | | Q2,01 | PCWP | |
| 8 | PX BLOCK ITERATOR | | | | | | Q2,01 | PCWC | |
|* 9 | TABLE ACCESS FULL | T1 | 10385 | 131K| 23 (5)| 00:00:01 | Q2,01 | PCWP | |
| 10 | BUFFER SORT | | | | | | Q2,01 | PCWC | |
| 11 | PX RECEIVE | | 8072 | 102K| 23 (5)| 00:00:01 | Q2,01 | PCWP | |
| 12 | PX SEND ROUND-ROBIN | :TQ20000 | 8072 | 102K| 23 (5)| 00:00:01 | | S->P | RND-ROBIN |
| 13 | BUFFER SORT | | 15 | 195 | | | | | |
| 14 | PX COORDINATOR | | | | | | | | |
| 15 | PX SEND QC (RANDOM) | :TQ10000 | 8072 | 102K| 23 (5)| 00:00:01 | Q1,00 | P->S | QC (RAND) |
| 16 | BUFFER SORT | | 15 | 195 | | | Q1,00 | PCWP | |
| 17 | PX BLOCK ITERATOR | | 8072 | 102K| 23 (5)| 00:00:01 | Q1,00 | PCWC | |
|* 18 | TABLE ACCESS FULL| T1 | 8072 | 102K| 23 (5)| 00:00:01 | Q1,00 | PCWP | |
--------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
9 - filter("STATUS"<='B' AND "STATUS">='A')
18 - filter("STATUS"='VALID' AND (LNNVL("STATUS"<='B') OR LNNVL("STATUS">='A')))
The most surprising part IMHO is the second DFO tree under step 14.
Basically the first branch of the CONCATENATION is executed under one DFO tree while the second under another DFO tree.
The main side effect of this choice is a likely inferior performance, mostly caused by:
- The slaves that execute step 17-18 will have to send the data to the QC at step 14 that in turn will distribute the data again to the other PX processes. Basically the execute will be P->S->P.
- Having two DFO trees means that each of them can potentially be downgraded, further slowing down the performance.
This plan seems to be the consequence of the fix for Wrong Result bug 9732434 (superseded by 10411726, superseded by 17006727) . Strange enough the fix for 9732434 has a fix_control to turn the fix off (WR fixes, as well as ORA-600/7445 don’t have reasons to be turned off usually), probably just some small parts rather than the whole fix.
Turning the fix_control for 9732434 OFF (OPT_PARAM(‘_fix_control’ ‘9732434:0’)) then the plan has a more familiar shape
-------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
-------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 15 | 195 | 47 (7)| 00:00:01 | | | |
| 1 | PX COORDINATOR | | | | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10001 | 15 | 195 | | | Q1,01 | P->S | QC (RAND) |
| 3 | HASH GROUP BY | | 15 | 195 | | | Q1,01 | PCWP | |
| 4 | PX RECEIVE | | 15 | 195 | | | Q1,01 | PCWP | |
| 5 | PX SEND HASH | :TQ10000 | 15 | 195 | | | Q1,00 | P->P | HASH |
| 6 | HASH GROUP BY | | 15 | 195 | | | Q1,00 | PCWP | |
| 7 | CONCATENATION | | | | | | Q1,00 | PCWP | |
| 8 | PX BLOCK ITERATOR | | | | | | Q1,00 | PCWC | |
|* 9 | TABLE ACCESS FULL| T1 | 10385 | 131K| 23 (5)| 00:00:01 | Q1,00 | PCWP | |
| 10 | PX BLOCK ITERATOR | | 8072 | 102K| 23 (5)| 00:00:01 | Q1,00 | PCWC | |
|* 11 | TABLE ACCESS FULL| T1 | 8072 | 102K| 23 (5)| 00:00:01 | Q1,00 | PCWP | |
-------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
9 - filter("STATUS"<='B' AND "STATUS">='A')
11 - filter("STATUS"='VALID' AND (LNNVL("STATUS"<='B') OR LNNVL("STATUS">='A')))
So if you have some SQLs that use OR-expansion and run parallel you may want to consider taking a closer look at them.
Mauro Pagano's Blog 