While waiting for Tanel Poder hacking session about sql hint invalidity which I encourage everyone to subscribe and to follow, here’re few particular cases where hints are not obeyed by the Oracle Cost Based Optimizer. It happens when the hint asks the CBO to do something which is either impossible or not yet implemented in its algorithm. When two hints are contradictory one of them will be ignored by the CBO to the detriment of the other one. We will see that the victim hint seems not to be cost based dependent but rather rule based. Let’s see this via a reproducible example
JPPD and HASH JOIN
To allow data filtering as early as possible, Oracle can use a JPPD transformation in order to apply a predicate from the outer query block (or main query block) into the contained non-mergeable query block. As such, a precise index range scan can be used and data are filtered much earlier.
Here’s a simple model with which I will illustrate this transformation:
SQL> select banner from v$version;
BANNER
----------------------------------------------------------------------
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 – Production
create table t1 as
select
rownum n1
,mod(rownum,5) n2
,trunc( (rownum - 1 / 3) ) n3
from
dual
connect by level <= 1e2;
create table t2 as
select
rownum n1
,mod(rownum,3) n2
,trunc( (rownum - 1 / 5) ) n3
from
dual
connect by level <= 1e3;
create table t3 as
select
rownum n1
,lpad('x',4000,'x') vc
from dual
connect by level <= 100;
alter table t2 add constraint t2_pk primary key (n1);
alter table t3 add constraint t3_pk primary key (n1);
begin
dbms_stats.gather_table_stats(user, 't1' );
dbms_stats.gather_table_stats(user, 't2' );
dbms_stats.gather_table_stats(user, 't3' );
end;
/
Let’s now execute the following query and get its corresponding execution plan
select
t1.n1, t1.n3
from
t1
left join
(select t2.n1 from t2, t3
where t2.n1 = t3.n1
) vw_1
on t1.n1 = vw_1.n1;
Plan hash value: 3070139659
--------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)|
--------------------------------------------------------------
| 0 | SELECT STATEMENT | | | 3 (100)|
| 1 | NESTED LOOPS OUTER | | 100 | 3 (0)|
| 2 | TABLE ACCESS FULL | T1 | 100 | 3 (0)|
| 3 | VIEW PUSHED PREDICATE | | 1 | 0 (0)| --> JPPD used
| 4 | NESTED LOOPS | | 1 | 0 (0)|
|* 5 | INDEX UNIQUE SCAN | T3_PK | 1 | 0 (0)|
|* 6 | INDEX UNIQUE SCAN | T2_PK | 1 | 0 (0)|
--------------------------------------------------------------
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('19.1.0')
DB_VERSION('19.1.0')
ALL_ROWS
OUTLINE_LEAF(@"SEL$639F1A6F")
PUSH_PRED(@"SEL$776AA54E" "VW_1"@"SEL$1" 1)
OUTLINE_LEAF(@"SEL$776AA54E")
MERGE(@"SEL$8812AA4E" >"SEL$E8571221")
OUTLINE(@"SEL$2")
OUTLINE(@"SEL$E8571221")
ANSI_REARCH(@"SEL$3")
OUTLINE(@"SEL$8812AA4E")
ANSI_REARCH(@"SEL$1")
OUTLINE(@"SEL$3")
OUTLINE(@"SEL$1")
FULL(@"SEL$776AA54E" "T1"@"SEL$1")
NO_ACCESS(@"SEL$776AA54E" "VW_1"@"SEL$1")
LEADING(@"SEL$776AA54E" "T1"@"SEL$1" "VW_1"@"SEL$1")
USE_NL(@"SEL$776AA54E" "VW_1"@"SEL$1")
INDEX(@"SEL$639F1A6F" "T3"@"SEL$2" ("T3"."N1"))
INDEX(@"SEL$639F1A6F" "T2"@"SEL$2" ("T2"."N1"))
LEADING(@"SEL$639F1A6F" "T3"@"SEL$2" "T2"@"SEL$2")
USE_NL(@"SEL$639F1A6F" "T2"@"SEL$2")
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("T3"."N1"="T1"."N1")
6 - access("T2"."N1"="T1"."N1")
Notice at operation Id n°5 that the predicate
on t1.n1 = vw_1.n1;
Has been pushed inside the view vw_1 to become
("T3"."N1"="T1"."N1")
This is simply what a JPPD transformation is.
Let’s now take the NESTED LOOPS hint from the above execution plan outline
USE_NL(@"SEL$776AA54E" "VW_1"@"SEL$1")
change it into a HASH JOIN
USE_HASH(@"SEL$776AA54E" "VW_1"@"SEL$1")
and use it in the above query as shown below:
select /*+
USE_HASH(@"SEL$776AA54E" "VW_1"@"SEL$1")
*/
t1.n1, t1.n3
from
t1
left join
(select t2.n1 from t2, t3
where t2.n1 = t3.n1
) vw_1
on t1.n1 = vw_1.n1;
----------------------------------------------------------
| Id | Operation | Name | Rows |Cost (%CPU)|
----------------------------------------------------------
| 0 | SELECT STATEMENT | | | 4 (100)|
|* 1 | HASH JOIN OUTER | | 100 | 4 (0)| --> HASH JOIN used
| 2 | TABLE ACCESS FULL | T1 | 100 | 3 (0)|
| 3 | VIEW | | 100 | 1 (0)|
| 4 | NESTED LOOPS | | 100 | 1 (0)|
| 5 | INDEX FULL SCAN | T3_PK | 100 | 1 (0)|
|* 6 | INDEX UNIQUE SCAN| T2_PK | 1 | 0 (0)|
----------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("T1"."N1"="VW_1"."N1")
6 - access("T2"."N1"="T3"."N1")
Notice that, while the HASH JOIN hint has been obeyed, the JPPD transformation has, however, not been used.
Let’s then force the JPPD transformation via the PUSH_PRED hint and see what happens
select /*+
USE_HASH(@"SEL$776AA54E" "VW_1"@"SEL$1")
PUSH_PRED(@"SEL$776AA54E" "VW_1"@"SEL$1" 1)
*/
t1.n1, t1.n3
from
t1
left join
(select t2.n1 from t2, t3
where t2.n1 = t3.n1
) vw_1
on t1.n1 = vw_1.n1;
-------------------------------------------------------------
| Id | Operation | Name | Rows |Cost (%CPU)|
-------------------------------------------------------------
| 0 | SELECT STATEMENT | | | 3 (100)|
| 1 | NESTED LOOPS OUTER | | 100 | 3 (0)| --> NESTED LOOPS vs HASH JOIN
| 2 | TABLE ACCESS FULL | T1 | 100 | 3 (0)|
| 3 | VIEW PUSHED PREDICATE | | 1 | 0 (0)| --> JPPD used
| 4 | NESTED LOOPS | | 1 | 0 (0)|
|* 5 | INDEX UNIQUE SCAN | T3_PK | 1 | 0 (0)|
|* 6 | INDEX UNIQUE SCAN | T2_PK | 1 | 0 (0)|
-------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("T3"."N1"="T1"."N1")
6 - access("T2"."N1"="T1"."N1")
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 1 (U - Unused (1))
---------------------------------------------------------------------------
1 - SEL$776AA54E / VW_1@SEL$1
U - USE_HASH(@"SEL$776AA54E" "VW_1"@"SEL$1")
Bizarrely, the HASH JOIN hint has, this time, not been used. This non-utilization of the HASH JOIN hint is also confirmed by the 19c Hint Report:
Total hints for statement: 1 (U - Unused (1)
Why?
Oracle decided to ignore the HASH JOIN hint in profit of the PUSH_PRED hint simply because JPPD transformation is not possible with HASH JOIN. When Oracle decides to follow the JPPD directive it knows that this will be possible only under a NESTED LOOPS join operation. Consequently it has to ignore the HASH JOIN hint.
The 10053 trace file is not very verbose in this case. Here’s below what we can link to the non-obeyed HASH JOIN hint from this trace file:
********************************* Number of join permutations tried: 1 ********************************* Enumerating distribution method (advanced) --- Distribution method for join between T1[T1](serial) and VW_1[from$_subquery$_002](serial); jm = 1; right side access path = TableScan ---- serial Hash-Join ---> NONE
In the Adaptive plan section of the same trace file we can read that the HASH JOIN is not legal
AP: adaptive joins bypassed for table VW_1 @ SEL$776AA54E due to Hash join is not legal
Probably through this illegality Oracle is indicating that it is impossible to obey the HASH JOIN in presence of a PUSH PREDICATE hint.
It looks like, whatever the cost of the JPPD is, it will be always prefered to the HASH JOIN as the JPPD has certainly the advantage of filtering data much earlier in the query execution path.
JPPD and ROWNUM
I think that I have already explained why one should always consider using the rownum pseudo-column with careful attention. Here’re two rownum usage side effects that I am aware of:
- Where rownum =1 might automatically switch to the buggy FIRST_ROWS mode
- Using a rownum stops view merging and JPPD transformation
Let’s examine the second case via the above query
select /*+
PUSH_PRED(@"SEL$776AA54E" "VW_1"@"SEL$1" 1)
*/
t1.n1, t1.n3
from
t1
left join
(select t2.n1 from t2, t3
where t2.n1 = t3.n1
) vw_1
on t1.n1 = vw_1.n1;
--------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)|
--------------------------------------------------------------
| 0 | SELECT STATEMENT | | | 11 (100)|
| 1 | NESTED LOOPS OUTER | | 10000 | 11 (19)|
| 2 | TABLE ACCESS FULL | T1 | 10000 | 9 (0)|
| 3 | VIEW PUSHED PREDICATE | | 1 | 0 (0)| --> JPPD used
| 4 | NESTED LOOPS | | 1 | 0 (0)|
|* 5 | INDEX UNIQUE SCAN | T3_PK | 1 | 0 (0)|
|* 6 | INDEX UNIQUE SCAN | T2_PK | 1 | 0 (0)|
--------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("T3"."N1"="T1"."N1")
6 - access("T2"."N1"="T1"."N1")
select /*+
PUSH_PRED(@"SEL$776AA54E" "VW_1"@"SEL$1" 1)
*/
t1.n1, t1.n3
from
t1
left join
(select t2.n1 from t2, t3
where t2.n1 = t3.n1
and rownum >1 --------------> spot this here
) vw_1
on t1.n1 = vw_1.n1;
-------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)|
-------------------------------------------------------------
| 0 | SELECT STATEMENT | | | 10 (100)|
|* 1 | HASH JOIN RIGHT OUTER | | 10000 | 10 (0)|
| 2 | VIEW | | 100 | 1 (0)|
| 3 | COUNT | | | |
|* 4 | FILTER | | | |
| 5 | NESTED LOOPS | | 100 | 1 (0)|
| 6 | INDEX FULL SCAN | T3_PK | 100 | 1 (0)|
|* 7 | INDEX UNIQUE SCAN| T2_PK | 1 | 0 (0)|
| 8 | TABLE ACCESS FULL | T1 | 10000 | 9 (0)|
-------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("T1"."N1"="VW_1"."N1")
4 - filter(ROWNUM>1)
7 - access("T2"."N1"="T3"."N1")
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 1 (U - Unused (1))
---------------------------------------------------------------------------
1 - SEL$776AA54E / VW_1@SEL$1
U - PUSH_PRED(@"SEL$776AA54E" "VW_1"@"SEL$1" 1)
Notice how the use of the ROWNUM pseudo-column in the contained query block impeached the JPPD hint to be obeyed. Hopefully in this case the corresponding 10053 trace file shows clearly why the JPPD transformation (hint) has been ignored
---------------------
QUERY BLOCK SIGNATURE
---------------------
signature (): qb_name=SEL$E8571221 nbfros=1 flg=0
fro(0): flg=1 objn=0 hint_alias="from$_subquery$_005"@"SEL$3"
JPPD: JPPD bypassed: View has unsupported constructs.
Check Basic Validity for Non-Union View for query block SEL$2 (#0)
JPPD: JPPD bypassed: View contains a rownum reference.
JPPD: JPPD bypassed: View contains a rownum reference. ------------> here
FPD: Considering simple filter push in query block SEL$2 (#0)
"T2"."N1"="T3"."N1" AND ROWNUM >1
Summary
There are many other situations like the ones exposed above out of which the SUBQUERY_PRUNING hint which forces a PARTITION RANGE SUBQUERY but only under a HASH JOIN operation or under a NESTED LOOPS but only when the partitioned table is on the right hand side of the join (inner row source). The combination of SUBQUERY_PRUNING and USE_NL hint would be impossible to be obeyed at the same time since this will force a recursive SQL ( SELECT distinct TBL$OR$IDX$PART$NUM) that would have been executed as many times as there are rows in the outer row source.
I may continue blogging about similar non-obeyed hints in a separate blog article to keep the current one about 5 minutes of read.
Mohamed Houri’s Oracle Notes 