A question has just appeared on a note I wrote in 2012 about the incorrect use of the use_nl() hint in some sys-recursive SQL, linking forward to an explanation I wrote in 2017 of the use_nl() hint – particularly the interpretation of the form use_nl(a,b), which does not mean “use a nested loop from table A to table B)”.
The question is essentially turns into – “does Oracle pick the join order before it looks at the hints”?
I’m going to look at one of the queries (based on the 2017 table creation code) that was supplied in the question and explain how Oracle gets to the plan it uses in my (21.3) system; here’s the query, followed by the plan:
select
/*+ use_nl(b) */
a.v1, b.v1, c.v1, d.v1
from
a, b, c, d
where
d.n100 = 0
and a.n100 = d.id
and b.n100 = a.n2
and c.id = a.id
/
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 20000 | 1347K| 105 (5)| 00:00:01 |
|* 1 | HASH JOIN | | 20000 | 1347K| 105 (5)| 00:00:01 |
| 2 | TABLE ACCESS FULL | C | 10000 | 146K| 26 (4)| 00:00:01 |
|* 3 | HASH JOIN | | 20000 | 1054K| 78 (4)| 00:00:01 |
|* 4 | TABLE ACCESS FULL | D | 100 | 1800 | 26 (4)| 00:00:01 |
|* 5 | HASH JOIN | | 20000 | 703K| 52 (4)| 00:00:01 |
| 6 | TABLE ACCESS FULL| B | 10000 | 136K| 26 (4)| 00:00:01 |
| 7 | TABLE ACCESS FULL| A | 10000 | 214K| 26 (4)| 00:00:01 |
-----------------------------------------------------------------------------
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
SWAP_JOIN_INPUTS(@"SEL$1" "C"@"SEL$1")
SWAP_JOIN_INPUTS(@"SEL$1" "D"@"SEL$1")
USE_HASH(@"SEL$1" "C"@"SEL$1")
USE_HASH(@"SEL$1" "D"@"SEL$1")
USE_HASH(@"SEL$1" "A"@"SEL$1")
LEADING(@"SEL$1" "B"@"SEL$1" "A"@"SEL$1" "D"@"SEL$1" "C"@"SEL$1")
FULL(@"SEL$1" "C"@"SEL$1")
FULL(@"SEL$1" "D"@"SEL$1")
FULL(@"SEL$1" "A"@"SEL$1")
FULL(@"SEL$1" "B"@"SEL$1")
OUTLINE_LEAF(@"SEL$1")
ALL_ROWS
DB_VERSION('21.1.0')
OPTIMIZER_FEATURES_ENABLE('21.1.0')
IGNORE_OPTIM_EMBEDDED_HINTS
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("C"."ID"="A"."ID")
3 - access("A"."N100"="D"."ID")
4 - filter("D"."N100"=0)
5 - access("B"."N100"="A"."N2")
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 1 (U - Unused (1))
---------------------------------------------------------------------------
6 - SEL$1 / "B"@"SEL$1"
U - use_nl(b)
Note
-----
- this is an adaptive plan
Points to note:
The Hint Report says the plan final did not use the use_nl(b) hint.
Whatever you may think the join order is by looking at the bodyy of the plan, the leading() hint in the Outline Information tells us that the join order was (B A D C) – and that explains why the use_nl(b) hint could not be used, because B was never “the next table in the join order”.
The “visible” order of activity displayed in the plan is C D B A, but that’s because we swap_join_inputs(D) to put it about the (B,A) join, then swap_join_inputs(C) to put that above D.
So did Oracle completely pre-empt any plans that allowed B to be “the next table”, thus avoiding the hint, or did it consider some plans where B wasn’t the first table in the join order, and if would it have used a nested loop into B if that plan had had a low enough cost?
The only way to answer these questions is to look at the CBO (10053) trace file; and for very simply queries it’s often enough to pick out a few lines as a starting point – in my case using egrep:
egrep -e "^Join order" -e"Best so far" or21_ora_15956.trc
Join order[1]: D[D]#0 A[A]#1 B[B]#2 C[C]#3
Best so far: Table#: 0 cost: 25.752439 card: 100.000000 bytes: 1800.000000
Join order[2]: D[D]#0 A[A]#1 C[C]#3 B[B]#2
Best so far: Table#: 0 cost: 25.752439 card: 100.000000 bytes: 1800.000000
Join order[3]: D[D]#0 B[B]#2 A[A]#1 C[C]#3
Best so far: Table#: 0 cost: 25.752439 card: 100.000000 bytes: 1800.000000
Join order[4]: D[D]#0 B[B]#2 C[C]#3 A[A]#1
Join order aborted2: cost > best plan cost
Join order[5]: D[D]#0 C[C]#3 A[A]#1 B[B]#2
Join order aborted2: cost > best plan cost
Join order[6]: D[D]#0 C[C]#3 B[B]#2 A[A]#1
Join order aborted2: cost > best plan cost
Join order[7]: A[A]#1 D[D]#0 B[B]#2 C[C]#3
Join order aborted2: cost > best plan cost
Join order[8]: A[A]#1 D[D]#0 C[C]#3 B[B]#2
Join order aborted2: cost > best plan cost
Join order[9]: A[A]#1 B[B]#2 D[D]#0 C[C]#3
Join order aborted2: cost > best plan cost
Join order[10]: A[A]#1 C[C]#3 D[D]#0 B[B]#2
Join order aborted2: cost > best plan cost
Join order[11]: A[A]#1 C[C]#3 B[B]#2 D[D]#0
Join order aborted2: cost > best plan cost
Join order[12]: B[B]#2 D[D]#0 A[A]#1 C[C]#3
Join order aborted2: cost > best plan cost
Join order[13]: B[B]#2 A[A]#1 D[D]#0 C[C]#3
Best so far: Table#: 2 cost: 25.692039 card: 10000.000000 bytes: 140000.000000
Join order[14]: B[B]#2 A[A]#1 C[C]#3 D[D]#0
Join order aborted2: cost > best plan cost
Join order[15]: B[B]#2 C[C]#3 D[D]#0 A[A]#1
Join order aborted2: cost > best plan cost
Join order[16]: C[C]#3 D[D]#0 A[A]#1 B[B]#2
Join order aborted2: cost > best plan cost
Join order[17]: C[C]#3 A[A]#1 D[D]#0 B[B]#2
Join order aborted2: cost > best plan cost
Join order[18]: C[C]#3 A[A]#1 B[B]#2 D[D]#0
Join order aborted2: cost > best plan cost
Join order[19]: C[C]#3 B[B]#2 D[D]#0 A[A]#1
Join order aborted2: cost > best plan cost
Oracle has considerd 19 possible join orders (out of a maximum of 24 (= 4!). In theory we should see 6 plans starting with wach of the 4 tables. In fact we we that the optimizer’s first choice started with table D, producing 6 join orders, then switched to starting with table A, producing only 5 join orders.
The “missing” order is (A, B, C, D) which should have appeared between join orders 9 and 10. If we check the trace file in more detail we’ll see that the optimizer aborted after calculation the join from A to B because the cost had already exceeded the “Best so far” by then so it didn’t carry on to calculate the cost going on to D. Clearly , then, there was no point in considering any other order that starting with (A, B) hence the absence of (A, B, C, D).
I’ve highlighted all the join orders where the optimizer didn’t abort. The “Best so far” line that I have reported (for ease of searching and reporting) is misleading – it’s only the cost of the first table in join order, this is what the 4 non-aborted summaries look like:
As you can see, when we start with (B A) the estimated cost drops dramatically.
Now that we’ve see that Oracle looks at many (though not a completely exhaustive set of) plans on the way to the one it picks the thing we need to do (in theory) is check that for every single calculation where B is “the next table”, Oracle obeys our hint. Each time the optimizer join “the next” table it usually considers the cost of a Nested Loop, a Sort Merge, and a Hash Join in that order; if the optimizer is obeying the hint it will only consider the nested loop join. Here’s a suitable call to egrep with the first four join orders::
egrep -e "^Join order" -e "^Now joining" -e"^NL Join" -e"^SM Join" -e"^HA Join" or21_ora_15956.trc
Join order[1]: D[D]#0 A[A]#1 B[B]#2 C[C]#3
Now joining: A[A]#1
NL Join
SM Join
SM Join (with index on outer)
HA Join
Now joining: B[B]#2
NL Join
Now joining: C[C]#3
NL Join
SM Join
HA Join
Join order[2]: D[D]#0 A[A]#1 C[C]#3 B[B]#2
Now joining: C[C]#3
NL Join
SM Join
HA Join
Now joining: B[B]#2
NL Join
Join order[3]: D[D]#0 B[B]#2 A[A]#1 C[C]#3
Now joining: B[B]#2
NL Join
Now joining: A[A]#1
NL Join
SM Join
HA Join
Now joining: C[C]#3
NL Join
SM Join
HA Join
Join order[4]: D[D]#0 B[B]#2 C[C]#3 A[A]#1
Now joining: C[C]#3
NL Join
Join order aborted2: cost > best plan cost
As you can see, the only join considered when “Now joining” B is a nested loop join; for all other tables the three possible joins (and sometimes two variants of the Sort Merge join) are evaluated.
You may also notice another of the clever strategies the optimizer uses to minimise its workload. On the second join order the optimizer goes straight to “Now joining C” because it has remembered the result of joining A from the previous join order.
This is only a very simple example and analysis, but I hope it’s given you some idea of how the optimizer works, and how clever it tries to be about minimising the work; and how it can obey a hint while still producing an execution plan that appears to have ignored the hint.
It’s been such a long time since Oracle deprecated the /*+ ordered */ hint that I can’t remember when it happened. The hint you should be using is the /*+ leading(…) */ hint which initially – maybe some time in 9i – would only allow you to specify the first table that the optimizer should use when examining join orders, but which soon changed to allow you to specify a complete join order.
I’ve written a few notes about the need to get rid of any /*+ ordered */ hints in production SQL, because it can produce a join order you’re not expecting. I’ve just found an extreme case of this running a quick test on 19.11.0.0 then 21.3.0.0
I’m not going to bother with the data setup for the query, but it’s a simple parent/child query that exhibits a surprising pattern. Here’s the query:
select
/*+
no_adaptive_plan
ordered
use_nl(ch)
*/
par.n1,
par.small_vc,
sum(ch.n1)
from
parent par,
child ch
where
par.n1 <= 20
and ch.id_par = par.id
group by
par.n1,
par.small_vc
;
And here’s the plan, pulled from memory with a call to dbms_xplan.display_cursor() with ordered hint in place. I’ve included the outline information, hint report and (since this is from 21c) the query block registry:
The optimizer seems to have got rather carried away with how clever it cn be; so here’s the result of switching from /*+ ordered */ to using /*+ leading(par ch) */ – I won’t bother with all the extras since it’s a very simple plan:
Here’s an example that appeared on the Oracle Developer Community forum about a year ago that prompted me to do a little investigative work. The question involved a distributed query that was “misbehaving” – the interesting points were the appearance of the /*+ rule */ and /*+ driving_site() */ hints in the original query when combined with a suggestion to address the problem using the /*+ materialize */ hint with factored subqueries (common table expressions – CTEs), or when combined with my suggestion to use the /*+ no_merge */ hint.
If you don’t want to read the whole article there’s a tl;dr summary just before the end.
The original question was posed with a handful of poorly constructed code fragments that were supposed to describe the problem, viz:
select /*+ DRIVING_SITE (s1) */ * from Table1 s1 WHERE condition in (select att1 from local_table) ; -- query n°1
select /*+ RULE DRIVING_SITE (s2) */ * from Table2 s2 where condition in (select att1 from local_table); -- query n°2
select * from
select /*+ DRIVING_SITE (s1) */ * from Table1 s1 WHERE condition in (select att1 from local_table) ,
select /*+ RULE DRIVING_SITE (s2) */ * from Table2 s2 where condition in (select att1 from local_table)
where att_table_1 = att_table_2 -- sic
The crux of the problem was that the two separate statements individually produced an acceptable execution plan but the attempt to use the queries in inline views with a join resulted in a plan that (from the description) sounded like the result of Oracle merging the two inline views and running the two IN subqueries as FILTER (existence) subqueries.
We weren’t shown any execution plans and only had the title of the question (“Distributed sql query through multiple databases”) to give us the clue that there might be three different databases involved.
Obviously there are several questions worth asking when presented with this problem. The first being “can we have a more realistic piece of code”, also “which vesion of Oracle”, and “where are the execution plans”. I can’t help feeling that there’s more to the problem than just the three tables that seem to be suggested by the fragments supplied.
More significant, though, was the surprise that rule and driving_site should work together. There’s a long-standing (but incorrect) assertion that “any other hint invalidates the RULE hint”. I think I’ve published an example somewhere showing that /*+ unnest */ would affect an execution plan where the optimizer still obeyed the /*+ rule */ hint, and there’s an old post on this blog which points out that transformation and optimisation are (or were, at the time) independent of each other, implying that you could combine the rule hint with “transformational” hints and still end up with a rule-based execution plan.
Despite old memories suggesting the contrary my first thought was that the rule and driving_site hints couldn’t be working together – and that made it worth running a little test. Then one of the other specialists on the forums suggested using subquery factoring with the materialize hint – and I thought that probably wouldn’t help because when you insert into a global temporary table the driving site has to become the site that holds the global temporary tables (in fact this isn’t just a feature of GTTs). So there was another thing prompting me to run a test. (And then I suggested using the /*+ no_merge */ hint – but thought I’d check if that idea was going to work before I suggested it.)
So here’s a code sample to create some data, and the first two simple queries with calls for their predicted execution plans:
rem
rem Script: distributed_multi.sql
rem Author: Jonathan Lewis
rem Dated: Jul 2020
rem Purpose:
rem
rem Last tested
rem 19.3.0.0
rem 12.2.0.1
rem 11.2.0.4
rem
rem create public database link test@loopback using 'test';
rem create public database link test2@loopback using 'test2';
rem create public database link orcl@loopback using 'orcl';
rem create public database link orcl2@loopback using 'orcl2';
rem create public database link orclpdb@loopback using 'orclpdb';
rem create public database link orclpdb2@loopback using 'orclpdb2';
define m_target=test@loopback
define m_target2=test2@loopback
define m_target=orcl@loopback
define m_target2=orcl2@loopback
define m_target=orclpdb@loopback
define m_target2=orclpdb2@loopback
create table t0
as
select *
from all_objects
where mod(object_id,4) = 1
;
create table t1
as
select *
from all_objects
where mod(object_id,11) = 0
;
create table t2
as
select *
from all_Objects
where mod(object_id,13) = 0
;
explain plan for
select /*+ driving_site(t1) */
t1.object_name, t1.object_id
from t1@&m_target t1
where
t1.object_id in (
select t0.object_id
from t0
)
;
select * from table(dbms_xplan.display);
explain plan for
select
/*+ rule driving_site(t2) */
t2.object_name, t2.object_id
from t2@&m_target2 t2
where
t2.object_id in (
select t0.object_id
from t0
)
;
select * from table(dbms_xplan.display);
Reading from the top down – t0 is in the local database, t1 is in remote database 1, t2 is in remote database 2. I’ve indicated the creation and selection of a pair of public database links at the top of the script – in this case both of them are loopback links to the local database, but I’ve used substitition variables in the SQL to allow me to adjust which databases are the remote ones. Since there are no indexes on any of the tables the optimizer is very limited in its choice of execution plans, which are as follows in 19.3 (the oraclepdb/orclpdb2 links).
First, the query against t1@orclpdb1 – which will run cost-based:
You’ll note that operation 3 is simply REMOTE, and t0 is the object accessed – which means this query is behaving as if the (local) t0 table is the remote one as far as the execution plan is concerned. The IN-OUT column tells us that this operation is “Remote to Serial” (R->S)” and the instance called to is named “!” which is how the local database is identified in the plan from a remote database.
We can also see that the execution plan gives us the “Remote SQL Information” for operation 2 – and that’s the text of the query that gets sent by the driving site to the instance that holds the object of interest. In this case the query is simply selecting the object_id values from all the rows in t0.
Now the plan for the query against t2@orclpdb2 which includes a /*+ rule */ hint:
The most striking feature of this plan is that it is an RBO (rule based optimizer) plan not a cost-based plan – and the Note section confirms that observation. We can also see that the Remote SQL Information is echoing the /*+ RULE */ hint back in it’s query against t0. Since the query is operating rule-based the hash join mechanism is not available (it’s a costed path – it needs to know the size of the data that will be used in the build table), and that’s why the plan is using a sort/merge join.
Following the “incremental build” strategy for writing SQL all we have to do as the next step of producing the final code is put the two queries into separate views and join them:
explain plan for
select v1.*, v2.*
from (
select /*+ driving_site(t1) */
t1.object_name, t1.object_id
from t1@&m_target t1
where
t1.object_id in (
select t0.object_id
from t0
)
) v1,
(
select
/*+ rule driving_site(t2) */
t2.object_name, t2.object_id
from t2@&m_target2 t2
where
t2.object_id in (
select t0.object_id
from t0
)
) v2
where
v1.object_id = v2.object_id
;
select * from table(dbms_xplan.display);
And here’s the execution plan – which, I have to admit, gave me a bit of a surprise on two counts when I first saw it:
The two surprises were that (a) the entire plan was rule-based, and (b) the driving_site() selection has disappeared from the plan.
Of course as soon as I actually started thinking about what I’d written (instead of trusting the knee-jerk “just stick the two bits together”) the flaw in the strategy became obvious.
Either the whole query runs RBO or it runs CBO – you can’t split the planning.
In the words of The Highlander “There can be only one” (driving site that is) – only one of the database involved will decide how to decompose and distribute the query.
It’s an interesting detail that the /*+ rule */ hint seems to have pushed the whole query into the arms of the RBO despite being buried somewhere in the depths of the query rather than being in the top level query block – but we’ve seen that before in some old data dictionary views.
The complete disregard for the driving_site() hints is less interesting – there is, after all, a comment in the manuals somewhere to the effect that when two hints contradict each other they are both ignored. (But I did wonder why the Hint Report that should appear with 19.3 plans didn’t tell me that the hints had been observed but not used.)
The other problem (from the perspective of the OP) is that the two inline views have been merged so the join order no longer reflects the two isolated components we used to have. So let’s fiddle around a little bit to see how close we can get to what the OP wants. The first step would be to add the /*+ no_merge */ hint to both inline view, and eliminate one of the /*+ driving_site() */ hints to see what happens, and since we’re modern we’ll also get rid of the /*+ rule */ hint:
explain plan for
select v1.*, v2.*
from (
select /*+ qb_name(subq1) no_merge driving_site(t1) */
t1.object_name, t1.object_id
from t1@&m_target t1
where
t1.object_id in (
select t0.object_id
from t0
)
) v1,
(
select
/*+ qb_name(subq2) no_merge */
t2.object_name, t2.object_id
from t2@&m_target2 t2
where
t2.object_id in (
select t0.object_id
from t0
)
) v2
where
v1.object_id = v2.object_id
;
select * from table(dbms_xplan.display);
-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Inst |IN-OUT|
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT REMOTE| | 4342 | 669K| 72 (9)| 00:00:01 | | |
|* 1 | HASH JOIN | | 4342 | 669K| 72 (9)| 00:00:01 | | |
| 2 | VIEW | | 4342 | 334K| 14 (8)| 00:00:01 | | |
| 3 | REMOTE | | | | | | ! | R->S |
| 4 | VIEW | | 5168 | 398K| 57 (8)| 00:00:01 | | |
|* 5 | HASH JOIN SEMI | | 5168 | 287K| 57 (8)| 00:00:01 | | |
| 6 | TABLE ACCESS FULL | T1 | 5168 | 222K| 16 (7)| 00:00:01 | ORCLP~ | |
| 7 | REMOTE | T0 | 14058 | 178K| 40 (5)| 00:00:01 | ! | R->S |
-----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("A2"."OBJECT_ID"="A1"."OBJECT_ID")
5 - access("A3"."OBJECT_ID"="A6"."OBJECT_ID")
Remote SQL Information (identified by operation id):
----------------------------------------------------
3 - EXPLAIN PLAN INTO "PLAN_TABLE" FOR SELECT /*+ QB_NAME ("SUBQ2") NO_MERGE */
"A1"."OBJECT_NAME","A1"."OBJECT_ID" FROM (SELECT DISTINCT "A3"."OBJECT_ID"
"OBJECT_ID" FROM "T0" "A3") "A2","T2"@ORCLPDB2.LOCALDOMAIN@LOOPBACK "A1" WHERE
"A1"."OBJECT_ID"="A2"."OBJECT_ID" (accessing '!' )
7 - SELECT "OBJECT_ID" FROM "T0" "A6" (accessing '!' )
Note
-----
- fully remote statement
In this plan we can see that the /*+ driving_site() */ hint has been applied – the plan is presented from the point of view of orclpdb (the database holding t1). The order of the two inline views has apparently been reversed as we move from the statement to its plan – but that’s just a minor side effect of the hash join (picking the smaller result set as the build table).
Operations 5 – 7 tell us that t1 is treated as the local table and used for the build table in a hash semi-join, and then t0 is accessed by a call back to our database and its result set is used as the probe table.
From operation 3 (in the body of the plan, and in the Remote SQL Information) we see that orclpdb has handed off the entire t2 query block to a remote operation – which is ‘accessing “!”‘. But there’s a problem (in my opinion) in the SQL that it’s handing off – the text is NOT the text of our inline view; it’s already been through a heuristic transformation that has unnested the IN subquery of our original text into a “join distinct view” – if we had used a hint to force this transformation it would have been the /*+ unnest(UNNEST_INNERJ_DISTINCT_VIEW) */ variant.
SELECT /*+ NO_MERGE */
"A1"."OBJECT_NAME","A1"."OBJECT_ID"
FROM
(SELECT DISTINCT "A3"."OBJECT_ID" "OBJECT_ID" FROM "T0" "A3") "A2",
"T2"@ORCLPDB2.LOCALDOMAIN@LOOPBACK "A1"
WHERE
"A1"."OBJECT_ID"="A2"."OBJECT_ID"
I tried to change this by adding alternative versions of the /* unnest() */ hint to the original query, following the query block names indicated by the outlineinformation (not shown), but it looks as if the code path constructs the Remote SQL operates without considering the main query hints – perhaps the decomposition code is simply following the code path of the old heuristic “I’ll do it if it’s legal” unnest. The drawback to this is that if the original form of the text had been sent to the other site the optimizer that had to handle it could have used cost-based query transformation and may have come up with a better plan.
You may be wondering why I left the /*+ driving_site() */ hint in one of the inline views rather than inserting it in the main query block. The answer is simple – it didn’t seem to work (even in 19.3) when I put /*+ driving_site(t1@subq1) */ in the main query block.
tl;dr
The optimizer has to operate rule-based or cost-based, it can’t do a bit of both in the same query – so if you’ve got a /*+ RULE */ hint that takes effect anywhere in the query the entire query will be optimised under the rule-based optimizer.
There can be only one driving site for a query, and if you manage to get multiple driving_site() hints in a query that contradict each other the optimizer will ignore all of them.
When the optimizer decomposes a distributed query and produces non-trivial components to send to remote sites you may find that some of the queries constructed for the remote sites have been subject to transformations that you cannot influence by hinting.
Footnote
I mentioned factored subqueries and the /*+ materialize */ option in the opening notes. In plans where the attempt to specify the driving site failed (i.e. when the query ran locally) the factored subqueries did materialize. In any plans where the driving site was a remote site the factored subqueries were always inline. This may well be related to the documented (though not always implemented) restriction that temporary tables cannot take part in distributed transactions.
A recent question on the Oracle-L list server asked “Is there a way I can use an SQL_PATCH to add a driving_site() hint to a query?” to which the reply was: “Just do it, it should work.” Unfortunately this produced the response: “I’m on 11.2.0.4, I’ve tried it, it doesn’t seem to be working.” I do approve of the “doesn’t seem to be” – it’s much more encouraging than a flat assertion that “it doesn’t work”, and helps encourage further dialogue.
I’ve come across this problem before (though I couldn’t find any notes I’d written about it – so possibly they’re only on a client site, or maybe it was a different hint displaying the same symptom) and it’s possible that the solution is very easy. Here’s a little data set to test with – created on 11.2.0.4 and then tested on 19.3.0.0:
rem
rem Script: patch_driving_site.sql
rem Author: Jonathan Lewis
rem Dated: Apr 2021
rem Purpose:
rem
rem Last tested
rem 19.3.0.0
rem 11.2.0.4
rem
define m_target=orclpdb@loopback
execute sys.dbms_sqldiag.drop_sql_patch('driving_site');
create table t1
as
select
*
from
all_objects
where
rownum <= 10000
;
alter table t1 add constraint t1_pk primary key (object_id);
create table t2
as
select
*
from
all_objects
where
rownum <= 10000
;
begin
dbms_stats.gather_table_stats(
ownname => null,
tabname => 'T1',
method_opt => 'for all columns size 1'
);
dbms_stats.gather_table_stats(
ownname => null,
tabname => 'T2',
method_opt => 'for all columns size 1 for columns owner size 254'
);
end;
/
I’ve created two tables which I’m going to join, but I’ve created a loopback database link that I’ll use to make one of them appear to be at a remote database. The data comes from view all_objects, and includes just 7 rows out of 10,000 for owner=’OUTLN’, which is why I’ve created a histogram on the owner column – but only for one of the tables.
You’ll notice I’ve issued a call to the dbms_sqldiagpackage to drop an SQL patch called “driving_site” just to make sure I don’t confuse the issue (i.e. myself) if I re-run the test in a couple of days time (I’ve also got a similar call at the end of the script). To run this test you’ll have to grant execute privileges on this package to your test schema, and if you’re on 11g you’ll also have to grant execute privileges on the package dbms_sqldiag_internal. so that you can create the SQL patch.
Here’s my query, with the default execution plan I got on the run against 19.3.0.0:
select
t1.object_name,
t1.object_type,
t2.object_name,
t2.object_type
from
t1,
t2@&m_target t2
where
t2.object_id = t1.object_id
and t2.owner = 'OUTLN'
/
select * from table(dbms_xplan.display_cursor(format=>'outline alias'));
SQL_ID 4dssxbjvzzrc0, child number 0
-------------------------------------
select t1.object_name, t1.object_type, t2.object_name,
t2.object_type from t1, t2@orclpdb@loopback t2 where t2.object_id =
t1.object_id and t2.owner = 'OUTLN'
Plan hash value: 3828311863
------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Inst |IN-OUT|
------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 54 (100)| | | |
| 1 | NESTED LOOPS | | 1111 | 165K| 54 (8)| 00:00:01 | | |
| 2 | NESTED LOOPS | | | | | | | |
| 3 | REMOTE | T2 | 1111 | 130K| 26 (4)| 00:00:01 | ORCLP~ | R->S |
|* 4 | INDEX UNIQUE SCAN | T1_PK | | | | | | |
| 5 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 33 | 27 (8)| 00:00:01 | | |
------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
3 - SEL$1 / T2@SEL$1
4 - SEL$1 / T1@SEL$1
5 - SEL$1 / T1@SEL$1
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
INDEX(@"SEL$1" "T1"@"SEL$1" ("T1"."OBJECT_ID"))
NLJ_BATCHING(@"SEL$1" "T1"@"SEL$1")
USE_NL(@"SEL$1" "T1"@"SEL$1")
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('19.1.0')
DB_VERSION('19.1.0')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
FULL(@"SEL$1" "T2"@"SEL$1")
LEADING(@"SEL$1" "T2"@"SEL$1" "T1"@"SEL$1")
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("T2"."OBJECT_ID"="T1"."OBJECT_ID")
Remote SQL Information (identified by operation id):
----------------------------------------------------
3 - SELECT "OWNER","OBJECT_NAME","OBJECT_ID","OBJECT_TYPE" FROM "T2" "T2" WHERE
"OWNER"='OUTLN' (accessing 'ORCLPDB.LOCALDOMAIN@LOOPBACK' )
Note
-----
- this is an adaptive plan
The optimizer has decided to do a nested loop join, fetching an estimated 1,111 rows (there are 10,000 rows in total and 9 distinct values for owner – and the optimizer doesn’t examine remote histograms!) The costing may seem a little surprising – an incremental cost of 27 for 1,111 probes of the index and table does seem a little low, but I’m not going to comment on that in this note.
We are actually going to get only 7 rows in the tablescan, so the path is a good one; but the Note tells us it is an adaptive plan and if at run-time the number of rows had been too large (as it would probably be for some other value of owner) Oracle would have switched to a hash join as the query is running.
Let us pretend, however, that we know that we could get better performance if the remote database optimised and executed the query. If we add the hint /*+ driving_site(t2) */ to the query we get the following from the call to dbms_xplan.display_cursor():
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------
SQL_ID 86n3j9s9q9k47, child number 0
select /*+ driving_site(t2) */ t1.object_name, t1.object_type,
t2.object_name, t2.object_type from t1, t2@orclpdb@loopback t2 where
t2.object_id = t1.object_id and t2.owner = 'OUTLN'
NOTE: cannot fetch plan for SQL_ID: 86n3j9s9q9k47, CHILD_NUMBER: 0
Please verify value of SQL_ID and CHILD_NUMBER;
It could also be that the plan is no longer in cursor cache (check v$sql_plan)
The call can’t find the plan because it’s the remote database that generated it from a piece of text that the local database sent to it, and the plan “belongs to” that text, not to the original query. So that almost tells us that the driving_site() hint has done its job properly – but we can double-check by searching the remote database’s library cache for the SQL that it actually ran.
set linesize 132
column sql_text wrap word format a75
select sql_id, sql_text
from V$sql
where sql_text like '%OUTLN%'
;
SQL_ID SQL_TEXT
------------- ---------------------------------------------------------------------------
5hmjcxgt0jc8t SELECT
"A2"."OBJECT_NAME","A2"."OBJECT_TYPE","A1"."OBJECT_NAME","A1"."OBJECT_TYPE"
FROM "T1"@! "A2","T2" "A1" WHERE "A1"."OBJECT_ID"="A2"."OBJECT_ID" AND
"A1"."OWNER"='OUTLN'
select * from table(dbms_xplan.display_cursor('5hmjcxgt0jc8t'));
SQL_ID 5hmjcxgt0jc8t, child number 0
-------------------------------------
SELECT "A2"."OBJECT_NAME","A2"."OBJECT_TYPE","A1"."OBJECT_NAME","A1"."OB
JECT_TYPE" FROM "T1"@! "A2","T2" "A1" WHERE
"A1"."OBJECT_ID"="A2"."OBJECT_ID" AND "A1"."OWNER"='OUTLN'
Plan hash value: 3485226535
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Inst |IN-OUT|
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 33 (100)| | | |
| 1 | NESTED LOOPS | | 7 | 924 | 33 (4)| 00:00:01 | | |
|* 2 | TABLE ACCESS FULL| T2 | 7 | 280 | 26 (4)| 00:00:01 | | |
| 3 | REMOTE | T1 | 1 | 92 | 1 (0)| 00:00:01 | ! | R->S |
-------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("A1"."OWNER"='OUTLN')
Remote SQL Information (identified by operation id):
----------------------------------------------------
3 - SELECT "OBJECT_NAME","OBJECT_ID","OBJECT_TYPE" FROM "T1" "A2" WHERE
:1="OBJECT_ID" (accessing '!' )
As we can see from the plan – which executed from the remote instance – the (now-local) histogram comes into play with a correct cardinality estimate for t2 and we do a nested loop requesting one row at a time from the (now remote) t1 by object_id. So the driving_site() hint works when written inline, which means we can work on creating a patch to apply it from “outside”.
begin
dbms_output.put_line('19.3 patch name: ' || sys.dbms_sqldiag.create_sql_patch(
-- sys.dbms_sqldiag_internal.i_create_patch(
sql_text =>
q'{
select
t1.object_name,
t1.object_type,
t2.object_name,
t2.object_type
from
t1,
t2@&m_target t2
where
t2.object_id = t1.object_id
and t2.owner = 'OUTLN'
}',
hint_text => 'driving_site(t2)',
-- hint_text => 'driving_site(t2@sel$1)',
name => 'driving_site'
)) -- extra bracket for dbms_output.put_line
;
end;
/
There are a couple of funny format details in this code fragment. First, dbms_sqldiag in 19c uses a function to create an SQL patch while 11g uses a procedure in dbms_sqldiag_internal, so there’s a messy optional bit near the top of the code and the reminder to count closing brackets near the bottom.
Second – when I used an SQL patch to add the hint that had worked it didn’t do what it was suppoed to do (even though the Note for the execution plan reported: “SQL patch “driving_site” used for this statement”. That’s why you see two versions (one commented) of the hint_text parameter. To get the SQL patch working I had to use the fully qualified alias of the remote table which, as you can see in the Query Block / Object Alias information from the first plan I reported, is t2@sel$1.
With the corrected SQL patch in place the text sent to the remote database was exactly the same as it was when I used the inline (but slightly lucky) /*+ driving_site(t2) */ hint. Quite possibly I should have gone one step further and made the hint_text include the query block as well, vizl: driving_site(@sel$1 t2@sel$1) I suspect, though, that that might not always be necessary (or even correct) – at some point I’ll have to check what happens if the reported query block has appeared as a consequence of a transformation and no longer matches the original query block from the object alias.
Summary
I’ve often made the point that you have to be very thorough with hints and this note demonstrates a variation of that theme. There are cases where you can get away with being a little sloppy when specifying an object alias in an “inline” hint but have to be more precise when imposing it from outside the original source SQL.
Adding the “originating” query block to construct the “fully qualified” object alias is the first refinement that you should think of; adding in a “target” query block name may also be necessary for some hints. Both these pieces of information can be reported in the various calls to dbms_xplan by including the “alias” option in the format parameter as you build your test cases towards a complete solution.
Footnote
Another example of the need for “refinement” to the simple in-line hint – a tweet thread from a couple of years ago where the hint use_nl_with_index() needing a fully qualified alias and/or the query block name when moved into an SQL patch.
At the end of the previous post on index hints I mentioned that I had been prompted to complete a draft from a few years back because I’d been sent an email by Kaley Crum showing the optimizer ignoring an index_rs_asc() hint in a very simple query. Here, with some cosmetic changes, is the example he sent me.
rem
rem Script: index_rs_kaley.sql
rem Dated: Dec 2020
rem Author: Kaley Crum
rem
rem Last tested
rem 19.3.0.0
rem
create table range_scan_me(
one,
letter
)
compress
nologging
as
with rowgen_cte as (
select null
from dual
connect by level <= 11315
)
select
1 one,
case
when rownum <= 64e5 then 'A'
when rownum = 64e5 + 1 then 'B'
when rownum <= 128e5 then 'C'
end letter
from
rowgen_cte a
cross join
rowgen_cte b
where
rownum <= 128e5
;
create index one_letter_idx on range_scan_me(one, letter) nologging;
The table has 12.8 million rows. Of the two columns the first always holds the value 1, the second has one row holding the value ‘B’, and 6.4M rows each holding ‘A’ and ‘C’. On my laptop it took about 20 seconds to create the table and 26 seconds to create the index; using a total of roughly 376 MB (29,000 blocks for the index, 18,500 blocks for the (compressed) table).
Since this is running on 19,3 Oracle will have created basic statistics on the table and index as it created them. Significantly, though, the statistics created during data loading do note include histograms so the optimizer will not know that ‘B’ is a special case, all it knows is that there are three possible values for letter.
Time now to query the data:
et serveroutput off
alter session set statistics_level=all;
select
/*+ index_rs_asc(t1 (one, letter)) */
letter, one
from
range_scan_me t1
where one >= 1
and letter = 'B'
/
select * from table(dbms_xplan.display_cursor(format=>'hint_report allstats last'));
I’ve told the optimizer to use an index range scan, using the “description” method to specify the index I want it to use. The hint is definitely valid, and the index can definitely be used in this way to get the correct result. But here’s the execution plan:
------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads |
------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 8 | 4 |
|* 1 | INDEX SKIP SCAN | ONE_LETTER_IDX | 1 | 4266K| 1 |00:00:00.01 | 8 | 4 |
------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("ONE">=1 AND "LETTER"='B' AND "ONE" IS NOT NULL
filter("LETTER"='B')
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 1 (U - Unused (1))
---------------------------------------------------------------------------
1 - SEL$1 / T1@SEL$1
U - index_rs_asc(t1 (one, letter))
The plan gives us two surprises: first it ignores (and reports that it is ignoring) a perfectly valid hint. Secondly it claims to be using an index skip scan even though the common understanding of a skip scan is that it will be used when “the first column of the index doesn’t appear in the where clause”.
We can infer that the plan is truthful because it has taken only 8 buffer visits to get the result – that’s probably a probe down to the (1,’B’) index entry, then another probe to see if the last index leaf block has any entries in it where column one is greater than 1.
But there are a couple of little oddities about this “ignoring the index” line. First, if we hadn’t hinted the query at all it would have done a tablescan, so the “index” bit of the hint is being obeyed even if the “rs” bit isn’t. Then there’s this:
select
/*+ index_rs_desc(t1 (one, letter)) */
letter, one
from
range_scan_me t1
where one >= 1
and letter = 'B'
/
-------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
-------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 8 |
|* 1 | INDEX SKIP SCAN DESCENDING| ONE_LETTER_IDX | 1 | 4266K| 1 |00:00:00.01 | 8 |
-------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("ONE">=1 AND "LETTER"='B' AND "ONE" IS NOT NULL)
filter("LETTER"='B')
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 1 (U - Unused (1))
---------------------------------------------------------------------------
1 - SEL$1 / T1@SEL$1
U - index_rs_desc(t1 (one, letter))
If we change the index_rs_asc() to index_rs_desc(), the optimizer still ignores the “range scan” bit of the hint, but honours the “descending” bit – we get an index skip scan descending.
Of course this example is a very extreme case – nevertheless it is a valid example of the optimizer behaving in a way that doesn’t seem very user-friendly. If we add ‘outline’ to the format options for the call to dbms_xplan.display_cursor() we’ll find that the index_ss_asc() and index_ss_desc() hints have been substituted for our attempted index_rs_asc() and index_rs_desc().
So, if we really are confident that an index range scan would work a lot better than an index skip scan what could we do. We could try telling it to use an index (posibly even an index range scan ascending), but not to do an index skip scan. Let’s test that and include the Outline Information in the execution plan:
select
/*+ index(t1) no_index_ss(t1) */
letter, one
from
range_scan_me t1
where one >= 1
and letter = 'B'
;
select * from table(dbms_xplan.display_cursor(format=>'hint_report allstats last outline'));
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.78 | 14290 |
|* 1 | INDEX RANGE SCAN| ONE_LETTER_IDX | 1 | 4266K| 1 |00:00:00.78 | 14290 |
---------------------------------------------------------------------------------------------
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$1")
INDEX(@"SEL$1" "T1"@"SEL$1" ("RANGE_SCAN_ME"."ONE" "RANGE_SCAN_ME"."LETTER"))
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("ONE">=1 AND "LETTER"='B' AND "ONE" IS NOT NULL)
filter("LETTER"='B')
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 2
---------------------------------------------------------------------------
1 - SEL$1 / T1@SEL$1
- index(t1)
- no_index_ss(t1)
It worked – we can see the index range scan, and we can see in the Buffers column of the plan why an index range scan was a bad idea – it’s taken 14,290 buffer visits to get the right result. If you check the index size I mentioned further up the page (, and think about how I defined the data, you’ll realise that Oracle has started an index range scan at the leaf block holding (1,B’) – which is half way along the index – and then walked every leaf block from there to the end of the index in an attempt to find any index entries with column one greater than 1.
The other thing to notice here is that the hint in the Outline Information is given as:
This was the hint that appeared in the outline whether I used the index() hint or the index_rs_asc() hint in the query. Similarly, when I tried index_desc() or index_rs_desc() as the hint the outline reported index_desc() in both cases.
If I try adding just this hint to the query the plan goes back to a skip scan. It’s another case where the hints in the Outline Information (hence, possibly, an SQL Plan Baseline) don’t reproduce the plan that the outline claims to be describing.
Summary
Does Oracle ignore hints?
It looks as if the answer is still no, except it seems to think that a skip scan is just a special case of a range scan (and, from the previous article, a range scan is just a special case of a skip scan). So if you want to ensure that Oracle uses your preferred index strategy you may have to think about including various “no_index” hints to block the indexes you don’t want Oracle to use, and then no_index_ss() and no_index_ffs() to make sure it doesn’t use the wrong method for the index you do want to use. Even then you may find you don’t have quite enough options to block every index option that you’d like to block.
I’ve lost count of the number of times I’ve reminded people that hinting (correctly) is hard. Even the humble /*+ index() */ hint and its close relatives are open to misunderstanding and accidental misuse, leading to complaints that “Oracle is ignoring my hint”.
Strange though it may seem, I’m still not 100% certain of what some of the basic index hints are supposed to do, and even the “hint report” in the most recent versions of dbms_xplan.display_xxx() hasn’t told me everything I’d like to know. So if you think you know all about hints and indexing this blog note is for you.
I’ll start with a brief, and approximate, timeline for the basic index hints – starting from 8.0
For completeness I’ve included the more exotic index-related hints in the list (without a version), and I’ve even highlighted the rarely seen use_nl_with_index() hint to remind myself to raise a rhetorical question about it at the end of this piece.
In this list you’ll notice that the only hint originally available directed the optimizer to access a table by index, but in 8.1 that changed so that we could
tell the optimizer about indexes it should not use
specify whether the index access should use the index in ascending or descending order
use an index fast full scan.
In 9i Oracle then introduced the index skip scan, with the option to specify whether the skip scan should be in ascending or descending order. The index_ss hint seems to be no more than a synonym for the index_ss_asc hint (or should that be the other way round); ss far as I can tell the index_ss() hint will not produce a descending skip scan.
You’ll note that there’s no hint to block an index skip scan, until the hint no_index_ss() appears in 10g along with the no_index_ffs() hint to block the index fast full scan. Since 10g Oracle has got better at introducing both the “positive” and “negative” versions of a hint whenever it introduces any hints for new optimizer mechanisms.
Finally we get to 11g and if you search MOS you may still be able to find the bug note (4323868.8) that introduced the index_rs_asc() and index_rs_desc() hints for index range scan ascending and descending.
From MOS Doc 4323868.8: “This fix adds new hints to enforce that an index is selected only if a start/stop keys (predicates) are used: INDEX_RS_ASC INDEX_RS_DESC”
This was necessary because by this time the index() hint allowed the optimizer to decide for itself how to use an index and it was quite difficult to force it to use the strategy you really wanted.
It’s still a source of puzzlement to me that an explicit index() hint will sometimes be turned into an index_rs_asc() when you check the Outline Information from a call to dbms_xplan.display_xxx() the Optimizer wants to use to reproduce the plan, while there are other times that an explicit index_rs_asc() hint will be turned into a basic index() hint (which might not reproduce the original plan)!
The Warm-up
Here’s a little surprise that could only reveal itself in the 19c hint report – unless you were willing to read your way carefully through a 10053 (CBO) trace file in earlier versions of Oracle. It comes from a little investigation of the index_ffs() hint that I’ve kept repeating over the last 20 years.
rem
rem Script: c_indffs.sql
rem Dated: March 2001
rem Author: Jonathan Lewis
rem
create table t1
nologging
as
select
rownum id,
rpad(mod(rownum,50),10) small_vc,
rpad('x',50) padding
from
all_objects
where
rownum <= 3000
;
alter table t1 modify id not null;
create index t_i1 on t1(id);
create index t_i2 on t1(small_vc,id);
set autotrace traceonly explain
select
count(small_vc)
from t1
where
id > 2750
;
select
/*+ index(t1) */
count(small_vc)
from t1
where
id > 2750
;
select
/*+ index_ffs(t1) */
count(small_vc)
from t1
where
id > 2750
;
select
/*+ index_ffs(t1) no_index(t1) */
count(small_vc)
from t1
where
id > 2750
;
set autotrace off
I’ve created a table with two indexes, and then enabled autotrace to get the execution plans for 4 queries that vary only in their hinting. Here’s the plan (on 19.3, with my settings for system stats) for the first query:
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 15 | 3 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 15 | | |
|* 2 | INDEX FAST FULL SCAN| T_I2 | 250 | 3750 | 3 (0)| 00:00:01 |
------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("ID">2750)
It’s an index fast full scan on the t_i2 (two-column) index. If I add an index() hint to this query, will that allow Oracle to continue using the index fast full scan, or will it force Oracle into some other path. Here’s the plan for the query hinted with index(t1):
The optimizer has chosen an index range scan on the (single-column) t1 index. Since this path costs more than the index fast full scan it would appear that the index() hint does not allow the optimizer to consider an index fast full scan. So we might decide that an index_ffs() hint is appropriate to secure the plan we want – and here’s the plan we get with that hint:
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 15 | 3 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 15 | | |
|* 2 | INDEX FAST FULL SCAN| T_I2 | 250 | 3750 | 3 (0)| 00:00:01 |
------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("ID">2750)
As expected we get the index fast full scan we wanted. But we might want to add belts and braces – let’s include a no_index() hint to make sure that the optimizer doesn’t consider any other strategy for using an index. Since we’ve seen that the index() hint isn’t associated with the index fast full scan path it seems reasonable to assume that the no_index() is also not associated with the index fast full scan path. Here’s the plan we get from the final variant of my query with index_ffs(t1) no_index(t1):
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 15 | 3 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 15 | | |
|* 2 | INDEX FAST FULL SCAN| T_I2 | 250 | 3750 | 3 (0)| 00:00:01 |
------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("ID">2750)
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 2 (U - Unused (2))
---------------------------------------------------------------------------
2 - SEL$1 / T1@SEL$1
U - index_ffs(t1) / hint conflicts with another in sibling query block
U - no_index(t1) / hint conflicts with another in sibling query block
The query has produced the execution plan we wanted – but only by accident. The hint report (which, by default, is the version that reports only the erroneous or unused hints) tells us that both hints have been ignored because they each conflict with some other hint in a “sibling” query block. In this case they’re conflicting with each other.
So the plan we get was our original unhinted plan – which made it look as if we’d done exactly the right thing to ensure that we’d made the plan completely reproducible. Such (previously invisible) errors can easily lead to complaints about the optimizer ignoring hints.
The Main Event
The previous section was about an annoying little inconsistency in the way in which the “negative” version of a hint may not correspond exactly to the “postive” version. There’s a more worrying issue to address when you try to be more precise in your use of basic index hints.
We’ve seen that an index() hint could mean almost anything other than an index fast full scan, while a no_index() hint (probably) blocks all possible uses of an index, but would you expect an index_rs_asc() hint to produce a skip scan, or an index_ss_asc() hint to produce a range scan? Here’s another old script of mine to create some data and test some hints:
rem
rem Script: skip_scan_anomaly.sql
rem Author: Jonathan Lewis
rem Dated: Jan 2009
rem
create table t1
as
with generator as (
select --+ materialize
rownum id
from all_objects
where rownum <= 3000 -- > hint to avoid wordpress format issue
)
select
mod(rownum,300) addr_id300,
mod(rownum,200) addr_id200,
mod(rownum,100) addr_id100,
mod(rownum,50) addr_id050,
trunc(sysdate) + trunc(mod(rownum,2501)/3) effective_date,
lpad(rownum,10,'0') small_vc,
rpad('x',050) padding
-- rpad('x',100) padding
from
generator v1,
generator v2
where
rownum <= 250000 -- > hint to avoid wordpress format issue
;
create index t1_i1 on t1(effective_date);
create index t1_i300 on t1(addr_id300, effective_date);
create index t1_i200 on t1(addr_id200, effective_date);
create index t1_i100 on t1(addr_id100, effective_date);
create index t1_i050 on t1(addr_id050, effective_date);
I’ve created a table with rather more indexes than I’ll be using. The significant indexes are t1_i1(effective_date), and t1_i050(addr_id050, effective_date). The former will be available for range scans the latter for skip scans when I test queries with predicates only on effective_date.
Choice of execution path can be affected by the system stats, so I need to point out that I’ve set mine with the following code:
begin
dbms_stats.set_system_stats('MBRC',16);
dbms_stats.set_system_stats('MREADTIM',10);
dbms_stats.set_system_stats('SREADTIM',5);
dbms_stats.set_system_stats('CPUSPEED',500);
exception
when others then null;
end;
/
And I’ll start with a couple of “baseline” queries and execution plans:
explain plan for
select
small_vc
from t1
where effective_date > to_date('&m_start_date','dd-mon-yyyy')
and effective_date <= to_date('&m_end_date' ,'dd-mon-yyyy')
;
select * from table(dbms_xplan.display(format=>'hint_report'));
alter index t1_i1 invisible;
explain plan for
select
/*+ index(t1) */
small_vc
from t1
where effective_date > to_date('&m_start_date','dd-mon-yyyy')
and effective_date <= to_date('&m_end_date' ,'dd-mon-yyyy')
;
You’ll notice at line 11 I’ve made the t1_i1 index invisible, and it will stay that way for a couple more tests. Here are the first two execution plans:
Unhinted
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1500 | 28500 | 428 (9)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T1 | 1500 | 28500 | 428 (9)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("EFFECTIVE_DATE"<=TO_DATE(' 2021-02-26 00:00:00',
'syyyy-mm-dd hh24:mi:ss') AND "EFFECTIVE_DATE">TO_DATE(' 2021-02-22
00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
Hinted with index(t1)
-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1500 | 28500 | 1558 (1)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| T1 | 1500 | 28500 | 1558 (1)| 00:00:01 |
|* 2 | INDEX SKIP SCAN | T1_I050 | 1500 | | 52 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("EFFECTIVE_DATE">TO_DATE(' 2021-02-22 00:00:00', 'syyyy-mm-dd
hh24:mi:ss') AND "EFFECTIVE_DATE"<=TO_DATE(' 2021-02-26 00:00:00', 'syyyy-mm-dd
hh24:mi:ss'))
filter("EFFECTIVE_DATE"<=TO_DATE(' 2021-02-26 00:00:00', 'syyyy-mm-dd
hh24:mi:ss') AND "EFFECTIVE_DATE">TO_DATE(' 2021-02-22 00:00:00', 'syyyy-mm-dd
hh24:mi:ss'))
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 1
---------------------------------------------------------------------------
1 - SEL$1 / T1@SEL$1
- index(t1)
Unhinted I’ve managed to rig the data and system stats so that the first path is a full tablescan; then, when I add the generic index(t1) hint Oracle recognises and uses the hint in the best possible way, picking the lowest cost index skip scan.
A variation I won’t show here – if I change the hint to index_rs_asc(t1) the optimizer recognizes there is no (currently visible) index that could be used for an index range scan and does a full tablescan, reporting the hint as unused. It won’t try to substitute a skip scan for a range scan.
What happens if I now try the index_ss(t1) hint without specifying an index. Firstly with the t1_i1 index still invisible, then after making t1_i1 visible again:
explain plan for
select
/*+ index_ss(t1) */
small_vc
from t1
where effective_date > to_date('&m_start_date','dd-mon-yyyy')
and effective_date <= to_date('&m_end_date' ,'dd-mon-yyyy')
;
select * from table(dbms_xplan.display(format=>'hint_report'));
Here are the two execution plans, first when t1_i1(effective_date) is still invisible:
-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1500 | 28500 | 1558 (1)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| T1 | 1500 | 28500 | 1558 (1)| 00:00:01 |
|* 2 | INDEX SKIP SCAN | T1_I050 | 1500 | | 52 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("EFFECTIVE_DATE">TO_DATE(' 2021-02-22 00:00:00', 'syyyy-mm-dd
hh24:mi:ss') AND "EFFECTIVE_DATE"<=TO_DATE(' 2021-02-26 00:00:00', 'syyyy-mm-dd
hh24:mi:ss'))
filter("EFFECTIVE_DATE"<=TO_DATE(' 2021-02-26 00:00:00', 'syyyy-mm-dd
hh24:mi:ss') AND "EFFECTIVE_DATE">TO_DATE(' 2021-02-22 00:00:00', 'syyyy-mm-dd
hh24:mi:ss'))
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 1
---------------------------------------------------------------------------
1 - SEL$1 / T1@SEL$1
- index_ss(t1)
As you might expect the optimizer has picked the t1_i050 index for a skip scan. (There are 3 other candidates for the skip scan, but since the have more distinct values for their leading column they are all turn out to have a higher cost than t1_i050).
So let’s make the t1_i1 index visible and see what the plan looks like:
----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1500 | 28500 | 521 (1)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| T1 | 1500 | 28500 | 521 (1)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | T1_I1 | 1500 | | 6 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("EFFECTIVE_DATE">TO_DATE(' 2021-02-22 00:00:00', 'syyyy-mm-dd
hh24:mi:ss') AND "EFFECTIVE_DATE"<=TO_DATE(' 2021-02-26 00:00:00', 'syyyy-mm-dd
hh24:mi:ss'))
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 1 (U - Unused (1))
---------------------------------------------------------------------------
1 - SEL$1 / T1@SEL$1
U - index_ss(t1)
The optimizer picks an index range scan using the t1_i1 index, and reports the hint as unused! For years I told myself that an index skip scan was derived as a small collection of range scans, so an index range was technically a “degenerate” skip scan i.e. one where the “small collection” consisted of exactly one element. Oracle 19c finally told me I was wrong – the optimizer is ignoring the hint.
The fact that it’s a sloppy hint and you could have been more precise is irrelevant – if the optimizer won’t do a skip scan when you specify a range scan (but watch out for the next “index hints” instalment – see footnote) it shouldn’t do a range scan when you specify a skip scan (but that’s just a personal opinion).
We should check, of course, that a precisely targeted skip scan hint works before complaining too loudly – would index_ss(t1 t1_i050), or index_ss_t1 t1_i300) work when there’s a competing index that could produce a lower cost range scan? The answer is yes.
explain plan for
select
/*+ index_ss(t1 t1_i050) */
small_vc
from t1
where effective_date > to_date('&m_start_date','dd-mon-yyyy')
and effective_date <= to_date('&m_end_date' ,'dd-mon-yyyy')
;
select * from table(dbms_xplan.display(format=>'hint_report'));
-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1500 | 28500 | 1558 (1)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| T1 | 1500 | 28500 | 1558 (1)| 00:00:01 |
|* 2 | INDEX SKIP SCAN | T1_I050 | 1500 | | 52 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("EFFECTIVE_DATE">TO_DATE(' 2021-02-22 00:00:00', 'syyyy-mm-dd
hh24:mi:ss') AND "EFFECTIVE_DATE"<=TO_DATE(' 2021-02-26 00:00:00', 'syyyy-mm-dd
hh24:mi:ss'))
filter("EFFECTIVE_DATE"<=TO_DATE(' 2021-02-26 00:00:00', 'syyyy-mm-dd
hh24:mi:ss') AND "EFFECTIVE_DATE">TO_DATE(' 2021-02-22 00:00:00', 'syyyy-mm-dd
hh24:mi:ss'))
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 1
---------------------------------------------------------------------------
1 - SEL$1 / T1@SEL$1
- index_ss(t1 t1_i050)
If you specify a suitable index in the index_ss() hint then the optimizer will use it and won’t switch to the index range scan. You can, of course, specify the index by description rather than name, so the hint /*+ index_ss(t1 (addr_id050, effective_date)) */ or even a partial description like /*+ index_ss(t1 (addr_id050)) */ would have been equally valid and obeyed.
How much do you know?
I’ll finish off with a rhetorical question, which I’ll introduce with this description take from the 19c SQL Tuning Guide section 9.2.1.6:
The related hint USE_NL_WITH_INDEX(table index) hint instructs the optimizer to join the specified table to another row source with a nested loops join using the specified table as the inner table. The index is optional. If no index is specified, then the nested loops join uses an index with at least one join predicate as the index key.
An intuitive response to this hint would be to assume that most people expect nested loops to use index unique scans or range scans into the second table. So what would your initial expectation be about the validity of use_nl_with_index() if the only way the index could be used was with an index skip scan, or a full scan, or a fast full scan. What if there were two join predicates and there’s a path which could do a nested loop if it used two indexes to do an index join (index_join()) or an index bitmap conversion (index_combine()). Come to that, how confident are you that the hint will work if the index specified is a bitmap index?
Summary
It’s important to be as accurate and thorough as possible when using hints. Even when a hint is documented you may find that you can asked “what if” questions about the hint and find that the only way to get answers to your questions is to do several experiments.
If you’re going to put hints into production code, take at least a little time to say to yourself:
“I know what I want and expect this hint to do; are there any similar actions that it might also be allowed to trigger, and how could I check if I need to allow for them or block them?”
Footnote: This journey of rediscovery was prompted by an email from Kaley Crum who supplied me with an example of Oracle using an index skip scan when it had been hinted to do an index range scan.
This is a list of possible explanations of errors that you might see in the Hint Report section of an execution plan. It’s just a list of the strings extracted from a chunk of the 19.3 executable around the area where I found something I knew could be reported, so it may have some errors and omissions – but there are plenty of things there that might give you some idea why (in earlier versions of Oracle) you might have seen Oracle “ignoring” a hint:
internally generated hint is being cleared
hint conflicts with another in sibling query block
hint overridden by another in parent query block
conflicting optimizer mode hints
duplicate hint
all join methods are excluded by hints
index specified in the hint doesn't exist
index specified in hint cannot be parallelized
incorrect number of indexes for AND_EQUAL
partition view set up
FULL hint is same as INDEX_FFS for IOT
access path is not supported for IOT
hint on view cannot be pushed into view
hint is discarded during view merging
duplicate tables in multi-table hint
conditions failed for array vector read
same QB_NAME hints for different query blocks
rejected by IGNORE_OPTIM_EMBEDDED_HINTS
specified number must be positive integer
specified number must be positive number
specified number must be >= 0 and <= 1
hint is only valid for serial SQL
hint is only valid for slave SQL
hint is only valid for dyn. samp. query
hint is only valid for update join ix qry
opt_estimate() without value list
opt_estimate() with conflicting values spec
hint overridden by NO_QUERY_TRANSFORMATION
hinted query block name is too long
hinted bitmap tree wasn't fully resolved
bitmap tree specified was invalid
Result cache feature is not enabled
Hint is valid only for select queries
Hint is not valid for this query block
Hint cannot be honored
Pred reorder hint has semantic error
WITH_PLSQL used in a nested query
ORDER_SUBQ with less than two subqueries
conflicting OPT_PARAM hints
conflicting optimizer_feature_enable hints
because of _optimizer_ignore_parallel_hints
conflicting JSON_LENGTH hints
Update August 2021 – New items in 21.3
Hint id larger than number of union groups
ORDER_KEY_VECTOR_USE with less than two IDs
ORDER_SUBQ referenced query block name, which cannot be found
Same table referenced in both lists
There are times when an upgrade makes a big difference to performance because an enhancement to the optimizer code path changes the costing of a plan that was always possible, but badly costed. I noticed an example of this while testing the code in the email I mentioned in last month’s posting on the “Incremental Sort” that Postgres 13 can do. Here’s a model to create some data and demonstrate the principle – the code is a modified (reduced) version of the code published by Phil Florent describing the feature.
rem
rem Script: fetch_first_postgres.sql
rem author: Phil Florent
rem Dated: 6th Nov 2020
rem
rem Last tested
rem 19.3.0.0 Uses index descending unhinted at low cost
rem 18.3.0.0 Used index desc efficiently if hinted, but high cost
rem 12.2.0.1 Used index desc efficiently if hinted, but high cost
rem
create table lancers(dtl timestamp, idg integer not null, perf integer);
insert into lancers(dtl, idg, perf)
with serie(i) as (
select 25e4 from dual
UNION ALL
select i - 1 from serie where i > 1
)
select
current_timestamp - (i / 1440),
trunc(dbms_random.value * 1e5 + 1),
case
when dbms_random.value <= 0.001 then 50000 + trunc(dbms_random.value * 50000 + 1)
else trunc(dbms_random.value * 50000 + 1)
end
from serie
/
execute dbms_stats.gather_table_stats(user,'lancers',method_opt=>'for all columns size 1')
create index perf_i1 on lancers(perf, dtl);
alter table lancers modify (perf not null, dtl not null);
This is the basic statement I want to execute – but in some versions of Oracle it will have to be hinted to produce the execution plan I want to see.
select
idg, perf
from
lancers
order by
perf desc
fetch first 5 rows only
/
If you check the order by clause and the definition of the index perf_i1 you’ll see that Oracle could (in principle) walk the index in descending order, stopping after just 5 rows, to produce the result.
But here are the execution plans from 19.3.0.0, 18.3.0.0, and 12.2.0.1, with their plans pulled from memory and showing the rowsource execution statistics (hinted by gather_plan_statistics) to show you what happens – starting from the newest first:
19.3.0.0: (unhinted)
--------------------
SQL_ID 8nmavy42tzrhb, child number 0
-------------------------------------
select /*+ gather_plan_statistics -- index_desc(lancers(perf,
dtl)) */ idg, perf from lancers order by perf desc fetch first 5
rows only
Plan hash value: 843745288
----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| A-Rows | A-Time | Buffers |
----------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 8 (100)| 5 |00:00:00.01 | 9 |
|* 1 | VIEW | | 1 | 5 | 8 (0)| 5 |00:00:00.01 | 9 |
|* 2 | WINDOW NOSORT STOPKEY | | 1 | 5 | 8 (0)| 5 |00:00:00.01 | 9 |
| 3 | TABLE ACCESS BY INDEX ROWID| LANCERS | 1 | 250K| 8 (0)| 5 |00:00:00.01 | 9 |
| 4 | INDEX FULL SCAN DESCENDING| PERF_I1 | 1 | 5 | 3 (0)| 5 |00:00:00.01 | 4 |
----------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("from$_subquery$_002"."rowlimit_$$_rownumber"<=5)
2 - filter(ROW_NUMBER() OVER ( ORDER BY INTERNAL_FUNCTION("PERF") DESC )<=5)
You can see an index_desc() hint in the output, but it has been commented out. The key feature to note is that the optimizer has found the path I was hoping to see, and it’s a low-cost path, although there is one oddity in the plan – the E-rows (cardinality estimate) for the table access doesn’t allow for the stopkey and, since there are no predicates in the query, reports the 250K rows that exist in the table.
For 18.3.0.0 I had to include the hint, and you’ll see why:
18.3.0.0 (hinted with index_desc)
---------------------------------
SQL_ID fgxvcaz3sab4q, child number 0
-------------------------------------
select /*+ gather_plan_statistics index_desc(lancers(perf, dtl))
*/ idg, perf from lancers order by perf desc fetch first 5 rows
only
Plan hash value: 843745288
----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| A-Rows | A-Time | Buffers |
----------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 250K(100)| 5 |00:00:00.01 | 10 |
|* 1 | VIEW | | 1 | 5 | 250K (1)| 5 |00:00:00.01 | 10 |
|* 2 | WINDOW NOSORT STOPKEY | | 1 | 250K| 250K (1)| 5 |00:00:00.01 | 10 |
| 3 | TABLE ACCESS BY INDEX ROWID| LANCERS | 1 | 250K| 250K (1)| 6 |00:00:00.01 | 10 |
| 4 | INDEX FULL SCAN DESCENDING| PERF_I1 | 1 | 250K| 854 (3)| 6 |00:00:00.01 | 4 |
----------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("from$_subquery$_002"."rowlimit_$$_rownumber"<=5)
2 - filter(ROW_NUMBER() OVER ( ORDER BY INTERNAL_FUNCTION("PERF") DESC )<=5)
Again we see the plan is possible, but the optimizer’s cardinality estimate for the hinted index scan is 250K rows – the full size of the index, and it has allowed for that in the cost of the query. So the cost of this plan is high and in the absence of the hint the optimizer would have used a full tablescan with sort.
Finally we get down to 12.2.0.1 – and I’ve shown the hinted and unhinted plans.
As you can see, 12.2.0.1 and 18.3.0.0 behave exactly the same way when hinted – the path is acceptable, but the cost is high. Consequently when I remove the hint the optimizer switches to using a full tablescan with sort because it’s cost is lower (thanks, in part, to the pattern in the data) than the indexed access path.
Summary
Two thoughts to take away from this note.
First, there were two possible execution plans for the same query and the optimizer in versions below 19c was picking the one that was clearly a bad idea. The presence of alternatives, though, means that the patterns in the data, the index definition and statistics (especially the clustering_factor) the number of rows to fetch, and various other optimizer settings may mean that you find yourself in the unlucky position that the optimizer’s arithmetic is on the boundary between the two plans and it switches randomly between them from day to day.
Secondly, when you upgrade to 19c the optimizer seems to be more likely to pick the indexed access path for a query like this – and that will probably be a good thing, but in a few cases it might turn out to be a bad thing.
If you’ve ever used subquery factoring (“with” subqueries or common table expressions (CTEs) as they are often called) then you’re probably aware of the (undocumented) hints /*+ materialize */ , which forces Oracle to create a local temporary table to hold the result of the subquery for subsequent use, and /*+ inline */, which forces the optimizer to copy the text of the subquery into the body of the query before starting the optimisation phase.
There’s a small, but important, enhancement to these hints that appeared in Oracle 18. Like so many other hints in Oracle they can now have a query block name as a “parameter”, so you can use them at the top level of your query. Here’s some code to demonstrate:
rem
rem Script: inline_hint.sql
rem Author: Jonathan Lewis
rem Dated: Oct 2020
rem Purpose:
rem
rem Last tested
rem 19.3.0.0
rem 18.3.0.0
rem 12.2.0.1 -- hints don't have any effect
rem
create table t1
as
select *
from all_objects
where rownum <= 10000 -- > comment to avoid wordpress format issue
/
create index t1_i1 on t1(object_id);
create table t2
as
select *
from t1
/
create index t2_i1 on t2(object_id);
spool inline_hint.lst
explain plan for
with v1 as (
select
/*+
qb_name(cte)
*/
object_id, object_type, object_name
from t2 gtt1
where object_id is not null
)
select
/*+
qb_name(main)
inline(@cte)
*/
t1.object_id,
t1.object_name
from
t1
where
exists (
select
null
from
v1 v1a
where
v1a.object_id = t1.object_id
and v1a.object_type = 'TABLE'
)
and exists (
select
null
from
v1 v1b
where
v1b.object_id = t1.object_id
and v1b.object_name like 'WRI%'
)
and
t1.object_id between 100 and 200
/
select * from table(dbms_xplan.display(format=>'alias'));
explain plan for
with v1 as (
select
/*+
qb_name(cte)
*/
object_id, object_type, object_name
from t2 gtt1
where object_id is not null
)
select
/*+
qb_name(main)
materialize(@cte)
*/
t1.object_id,
t1.object_name
from
t1
where
exists (
select
null
from
v1 v1a
where
v1a.object_id = t1.object_id
and v1a.object_type = 'TABLE'
)
and
t1.object_id between 100 and 200
/
select * from table(dbms_xplan.display(format=>'alias'));
The first of these two queries uses the factored subquery twice so, by default, it will create a “cursor duration memory” temporary table to hold the results of the subquery and then use that temporary table twice in the execution plan.
Conversely the second query uses the factored subquery just once, so the optimizer’s default action will be to copy the text into the body of the main query and optimize the whole thing as a single query block.
To reverse the default behaviour in versions of Oracle up to 12.2.0.1 (though later patch sets may include the 18c enhancements) you could add the /*+ inline */ or /*+ materialize */ hints respectively to the factored subqueries; but my demonstration you can see that I’ve given the factored subquery a query block name and added the relevant hint to the main query block passing in the query block name of the factored subquery – hence /*+ inline(@cte) */ and /*+ materialize(@cte) */.
Here – from 19.3 – are the resulting execution plans (with some cosmetic editing) – first the plan with the inline() hint.
As you can see Oracle has copied the subquery text into the main body of the text and then optimized to produce a three-table join. One of the subqueries has been unnested into an aggregate view (operations 3,4,5), the other has been transformed into a semi-join.
In passing you’ll also notice that the optimizer has used transitive closure to add the range predicate on t1 to both occurrences of the t2 table.
And here’s the plan for the query with the single use of the subquery and materialize() hint:
In this plan the optimizer has created an in-memory temporary table and then used it in the existence subquery – which it has then transformed into a semi-join, so we have a query block with the name SEL$A3F38ADC; but we also see that the query block CTE still exists, labelling the operations that Oracle used to populate the temporary table.
It is an interesting (and irritating) detail that when we look at object aliases we see (operation 8) that Oracle has given the temporary table the alias of t1 – which is just a little confusing since I actually have a table called t1!
Next Steps
Being able to nominate a query block for the inline() and materialize() hints may be of great help in some cases (there’s a recent example on the Oracle Developer Forum (may need a MOS login) where it might make a huge difference to the performance of a particular query without requiring a rewrite of the SQL).
But there are a couple of details to investigate. First, I had a query block name built into my factored subquery – what happens if the author of the SQL didn’t include a query block name?
Before I’d added the inline() hint and query block names in the first example above this is what the plan looked like:
As you can see, the factored subquery (operations 2 and 3) has the query block name of sel$1 and the main query (operations 6 an 7 where the real t1 is used) has the query block name sel$2. So without giving the subquery a name I could have used the hint /*+ inline(@sel$1) */ in the main query block.
This takes us on to the second point that needs investigation. If you’ve looked at the example on the Oracle Developer Forum you will have seen that there’s an SQL statement that references a stored view and the factored subquery of interest is defined in the view. This means we might be able to edit the query that calls the view to include a hint referencing the query block inside the view – but then what do we do if we can’t edit the main query itself?
To be investigated (1) – would the inline() hint with nominated query block work if the factored subquery was inside a stored view that we were using in our query?
To be investigated(2) – if (1) works, could we achieve the same result by using an SQL Patch to attach the hint to the main query text without editing the main query?
Update (Oct 2020)
It turns out that I discovered this enhancement a few months ago while doing some experimentation with recursive subquery factoring.
Update Nov 2020
A blog note from Nenad Noveljic warns of a surprising ORA-07445 if you get too trigger-happy with the inline() and materialize() hints.
I’ve written a coupleof notesin the past about the problems of optimising queries with predicates of the form “or exists {subquery}”. A recent question on the Oracle Developer Community forum brought to my attention an improvement in this area in (very precisely) 12.2, as well as giving me a cute example of how the first cut of a new feature doesn’t always cover every detail, and creating a nice example of how the new technology enhances the old technology.
We start with some data and a simple query running under 12.2.0.1:
rem
rem Script: exists_with_or_4.sql
rem Author: Jonathan Lewis
rem Dated: Aug 2020
rem
rem Last tested
rem 19.3.0.0
rem 12.2.0.1
rem 12.1.0.2 -- feature not implemented
rem
create table cat_contact(
contact_method_id varchar2(1) not null,
contact_id number(8,0) not null,
small_vc varchar2(10),
padding varchar2(100)
);
alter table cat_contact add constraint cc_pk primary key(contact_id);
create index cc_i1 on cat_contact(contact_method_id);
insert into cat_contact
select
chr(64 + case when rownum <= 10 then rownum else 26 end),
rownum,
lpad(rownum,10),
rpad('x',100,'x')
from
all_objects
where
rownum <= 10000
;
select count(*) from cat_contact where contact_method_id in ('A','B','C');
create table cat_item(
contact_id number(8,0) not null,
item_category varchar2(1) not null,
small_vc varchar2(10),
padding varchar2(100),
constraint ci_ref_cc foreign key(contact_id) references cat_contact
)
;
alter table cat_item add constraint ci_pk primary key(contact_id, item_category);
create index ci_i1 on cat_item(item_category);
insert into cat_item
select
rownum,
chr(88 + case when rownum <= 10 then mod(rownum,2) else 2 end),
lpad(rownum,10),
rpad('x',100,'x')
from
all_objects
where
rownum <= 10000
;
select count(*) from cat_item where item_category in ('X','Y');
execute dbms_stats.gather_table_stats(user,'cat_contact')
execute dbms_stats.gather_table_stats(user,'cat_item')
I’ve created and populated two tables (the table and column names come from the ODC thread). There’s a foreign key relationship defined between cat_item and cat_contact, both tables have primary keys declared, with a couple of extra columns declared not null.
I’ve populated the two tables with a small amount of data and each table has one column rigged with very skewed data:
cat_contact.contact_method_id is mostly ‘Z’ with one row each of ‘A’ to ‘J’ ,
cat_item.item_category (the second column in the primary key) is mostly ‘Z’ with 5 rows each of ‘X’ and ‘Y’
After populating each table I’ve queried it in a way which means the subsequent stats gathering will create frequency histograms on these two columns and the optimizer will be able to take advantage of the skew in its arithmetic, which means it may choose to use the indexes I’ve created on those skewed columns if the right values appear in the queries.
So here’s the query we’re interested in:
SELECT /*+
qb_name(main)
*/
*
FROM cat_contact c
WHERE (
exists (
SELECT /*+ qb_name(subq) */
*
FROM cat_item i
WHERE i.contact_id = c.contact_id
AND i.item_category in ('X', 'Y')
)
OR c.contact_method_id IN ('A', 'B', 'C')
)
;
select * from table(dbms_xplan.display);
Here’s the default execution plan (in 12.2.0.1 with my settings for system stats and various other optimizer-related figures that MIGHT make a difference) pulled from memory after executing the query to return 10 rows.
-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 34 (100)| |
|* 1 | FILTER | | | | | |
| 2 | TABLE ACCESS FULL | CAT_CONTACT | 10000 | 1152K| 34 (6)| 00:00:01 |
| 3 | INLIST ITERATOR | | | | | |
|* 4 | INDEX UNIQUE SCAN| CI_PK | 1 | 6 | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter((INTERNAL_FUNCTION("C"."CONTACT_METHOD_ID") OR IS NOT NULL))
4 - access("I"."CONTACT_ID"=:B1 AND (("I"."ITEM_CATEGORY"='X' OR
"I"."ITEM_CATEGORY"='Y')))
For every row in the cat_contact table Oracle has checked whether or not the contact_method is an ‘A’, ‘B’, or ‘C’ and passed any such rows up to its parent, for all other rows it’s then executed the subquery to see if the row with the matching contact_id in contact_item has an ‘X’ or ‘Y’ as the item_category. It’s had to run the subquery 9,997 times (there were only three rows matching ‘A’,’B’,’C’) and the INLIST ITERATOR at operation 3 means that it’s probed the index nearly 20,000 timtes. This does not look efficient.
I’ve said in previous articles that when you need to optimize queries of this shape you need to rewrite them as UNION ALL queries to separate the two parts of the complex OR predicate and then make sure that you don’t report any items twice – which you do by making use of the lnnvl() function. So let’s do this – but let’s do it the lazy “new technology” way by upgrading to 19c and executing the query there; here’s the plan I got in 19.3.0.0:
The optimizer has used the new “cost-based OR-expansion” transformation to rewrite the query as a UNION ALL query. We can see an efficient access into cat_contact to identify the ‘A’,’B’,’C’ rows, and then we can see that the second branch of the union all handles the existence subquery but the optimizer has unnested the subquery to select the 10 rows from cat_item where the item_category is ‘X’ or ‘Y’ and used those rows in a nested loop to drive into the cat_contact table using the primary key. We can also see the use of the lnnvl() function in operation 13 that ensures we don’t accidentally report the ‘A’,’B’,’C’ rows again.
So let’s go back to 12.2.0.1 and see what happens if we just add the /*+ or_expand(@main) */ hint to the query. Here’s the resulting execution plan:
We get exactly the plan we want – with the same cost as the 19c cost, which happens to be less than half the cost of the default plan that we got from 12.2.0.1. So it looks like there may be case where you will need to hint OR-expansion because is might not appear by default.
Other Observations 1 – ordering
You may have noticed that my query has, unusually for me, put the existence subquery first and the simple filter predicate second in the where clause. I don’t like this pattern as (over time, and with different developers modifying queries) it’s too easy in more complex cases to “lose” the simple predicate; a one-liner can easily drift, change indents, get bracketed with another predicate that it shouldn’t be connected with and so on. I’ve actually seen production systems producing wrong results because little editing accidents like this (counting brackets is the classic error) have occured – so I’m going to rerun the test on 12.2.0.1 with the predicates in the order I would normally write them.
Here’s the “corrected” query with its execution plan:
SELECT /*+
qb_name(main)
or_expand(@main)
*/
*
FROM cat_contact c
WHERE (
c.contact_method_id IN ('A', 'B', 'C')
OR
exists (
SELECT /*+ qb_name(subq) */
*
FROM cat_item i
WHERE i.contact_id = c.contact_id
AND i.item_category in ('X', 'Y')
)
)
;
-------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 16 (100)| |
| 1 | VIEW | VW_ORE_231AD113 | 13 | 962 | 16 (7)| 00:00:01 |
| 2 | UNION-ALL | | | | | |
| 3 | NESTED LOOPS | | 10 | 1240 | 10 (10)| 00:00:01 |
| 4 | NESTED LOOPS | | 10 | 1240 | 10 (10)| 00:00:01 |
| 5 | SORT UNIQUE | | 10 | 60 | 4 (0)| 00:00:01 |
| 6 | INLIST ITERATOR | | | | | |
| 7 | TABLE ACCESS BY INDEX ROWID BATCHED| CAT_ITEM | 10 | 60 | 4 (0)| 00:00:01 |
|* 8 | INDEX RANGE SCAN | CI_I1 | 10 | | 3 (0)| 00:00:01 |
|* 9 | INDEX UNIQUE SCAN | CC_PK | 1 | | 0 (0)| |
| 10 | TABLE ACCESS BY INDEX ROWID | CAT_CONTACT | 1 | 118 | 1 (0)| 00:00:01 |
|* 11 | FILTER | | | | | |
| 12 | INLIST ITERATOR | | | | | |
| 13 | TABLE ACCESS BY INDEX ROWID BATCHED | CAT_CONTACT | 3 | 354 | 4 (0)| 00:00:01 |
|* 14 | INDEX RANGE SCAN | CC_I1 | 3 | | 3 (0)| 00:00:01 |
| 15 | INLIST ITERATOR | | | | | |
|* 16 | INDEX UNIQUE SCAN | CI_PK | 1 | 6 | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
8 - access(("I"."ITEM_CATEGORY"='X' OR "I"."ITEM_CATEGORY"='Y'))
9 - access("I"."CONTACT_ID"="C"."CONTACT_ID")
11 - filter(LNNVL( IS NOT NULL))
14 - access(("C"."CONTACT_METHOD_ID"='A' OR "C"."CONTACT_METHOD_ID"='B' OR
"C"."CONTACT_METHOD_ID"='C'))
16 - access("I"."CONTACT_ID"=:B1 AND (("I"."ITEM_CATEGORY"='X' OR "I"."ITEM_CATEGORY"='Y')))
The execution plan has jumped from 14 lines to 17 lines, the cost has gone up from 14 to 16, and both branches of the plan now report access to cat_contact and cat_item (though only through its primary key index in the second branch). What’s happened?
Oracle 12.2.0.1 has rewritten the query as a UNION ALL working from the bottom up – so in this case the first branch of the rewrite handles the original filter subquery, unnesting it to drive efficient from cat_item to cat_contact. This means the second branch of the rewrite has to find the ‘A’,’B’,’C’ rows in cat_contact and then check that the filter subquery hadn’t previously reported them – so the optimizer has applied the lnnvl() function to the filter subquery – which you can nearly see in the Predicate Information for operation 11.
To make it clearer, here’s what you get as the predicate information for that operation after calling explain plan and dbms_xplan.display()
11 - filter(LNNVL( EXISTS (SELECT /*+ QB_NAME ("SUBQ") */ 0 FROM "CAT_ITEM" "I" WHERE
("I"."ITEM_CATEGORY"='X' OR "I"."ITEM_CATEGORY"='Y') AND "I"."CONTACT_ID"=:B1)))
In 12.2 the order of predicates in your query seems to be important – unless told otherwise the optimizer is working from the bottom-up (then rewriting top-down). But there is hope (though not documented hope). I added the /*+ or_expand(@main) */ hint to the query to force OR-expansion. Checking the Outline Information of the plan I could see that this had been expanded to /*+ or_expand(@main (1) (2)) */. Taking a wild guess as the significance of the numbers and changing the hint to /*+ or_expand(@main (2) (1) */ I re-ran the test and back to the more efficient plan – with the filter subquery branch appearing second in the UNION ALL view and the lnnvl() applied to the simpler predicate.
So the OR-expansion code is not fully cost-based in 12.2.0.1, but you can modify the behaviour through hinting. First to force it to appear (which may not happen even if it seems to be the lower cost option), and secondly to control the ordering of the components of the UNION ALL. As with all things relating to hints, though, act with extreme caution: we do not have sufficient documentation explaining exactly how they work, and with some of them we don’t even know whether the code path is even complete yet.
Other Observations 2 – 12cR1
The or_expand() hint and cost-based OR-expansion appeared specifically in 12.2.0.1; prior to that we had a similar option in the use_concat() hint and concatenation – which also attempts to rewrite your query to produce a union all of disjoint data sets. But there are restrictions on what concatentation can do. I rarely remember what all the restrictions are, but there are two critical restrictions:
first, it will only appear by default if there is an indexed access path available to drive every branch of the rewrite
secondly, it will not apply further transformations to the separate branches that it produces
If we try adding the or_expand() hint to our query in 12.1.0.2 it will have no effect, so let’s add a suitable use_concat() hint and see what happens:
explain plan for
SELECT /*+
qb_name(main)
use_concat(@main 8 or_predicates(1))
-- use_concat(@main or_predicates(1))
*/
*
FROM cat_contact c
WHERE (
exists (
SELECT /*+ qb_name(subq) */
*
FROM cat_item i
WHERE i.contact_id = c.contact_id
AND i.item_category in ('X', 'Y')
)
OR
c.contact_method_id IN ('A', 'B', 'C')
)
;
select * from table(dbms_xplan.display);
-----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10000 | 1152K| 40 (3)| 00:00:01 |
| 1 | CONCATENATION | | | | | |
| 2 | INLIST ITERATOR | | | | | |
| 3 | TABLE ACCESS BY INDEX ROWID BATCHED| CAT_CONTACT | 3 | 354 | 4 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | CC_I1 | 3 | | 3 (0)| 00:00:01 |
|* 5 | FILTER | | | | | |
|* 6 | TABLE ACCESS FULL | CAT_CONTACT | 9997 | 1151K| 35 (6)| 00:00:01 |
| 7 | INLIST ITERATOR | | | | | |
|* 8 | INDEX UNIQUE SCAN | CI_PK | 1 | 6 | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("C"."CONTACT_METHOD_ID"='A' OR "C"."CONTACT_METHOD_ID"='B' OR
"C"."CONTACT_METHOD_ID"='C')
5 - filter( EXISTS (SELECT /*+ QB_NAME ("SUBQ") */ 0 FROM "CAT_ITEM" "I" WHERE
("I"."ITEM_CATEGORY"='X' OR "I"."ITEM_CATEGORY"='Y') AND "I"."CONTACT_ID"=:B1))
6 - filter(LNNVL("C"."CONTACT_METHOD_ID"='A') AND LNNVL("C"."CONTACT_METHOD_ID"='B') AND
LNNVL("C"."CONTACT_METHOD_ID"='C'))
8 - access("I"."CONTACT_ID"=:B1 AND ("I"."ITEM_CATEGORY"='X' OR "I"."ITEM_CATEGORY"='Y'))
26 rows selected.
As you can see by forcing concatentation I’ve got my “union all” view with lnnvl() applied in the second branch. But the second branch was the “select where exists()” branch and the optimizer has not been able (allowed?) to do the unnesting that would let it drive efficiently from the cat_item table to the cat_contact table. The effect of this is that the plan still ends up with a full tablescan of cat_contact running a filter subquery on virtually every row- so concatenation doesn’t save us anything.
The significance of the “8” in the hint, by the way is (I believe) that it tells the optimizer to use inlist iterators when possible. If I had omitted the “8” the plan would have had 4 branches – one each for ‘A’, ‘B’, and ‘C’ and the fourth for the filter subquery. I could also have added a hint /*+ use_concat(@subq or_predicates(1)) */ to replace operations 7 and 8 with a single index range scan with a filter predicate for the ‘X’/’Y’ check (and that might, in any case, be slightly more efficient than the iteration approach).
Footnote(s)
The “legacy” OR-expansion (“concatenation” a.k.a. LORE in the optimizer trace file) can be controlled through the hints use_concat(), and no_expand().
The new cost-based OR-expansion (now ORE in the optimizer trace file) can be controlled through the hints or_expand() and no_or_expand().
The new cost-based OR-expansion has some restrictions, for example it is explicitly blocked in a MERGE statement, even in 19c, as reported in this blog note by Nenad Noveljic. As the blog note shows, concatenation is still possible but you (may) have to disable cost based OR-expansion.
I scanned the executable for the phrase “ORE: bypassed” to see if there were any messages that would suggest other reasons why cost-based OR-expansion would not be used; unfortunately the only relevant string was “ORE: bypassed – %s” [update (see comment 5 below): after ignoring case there was a second option: “ORE: Bypassed for disjunct chain: %s.”] – in other words all the possible bypass messages would be filled in on demand. I found a list of messages that might be relevant; I’d be a little cautious about trusting it but if you don’t see the feature appearing when you’re expecting it then it might be worth checking whether one of these could apply.
Old OR expansion hints present
Semi join hint present
QB has FALSE predicate
QB marked for NO Execution
Full Outer join QB
Rownum found in disjunction
Anti/semi/outer join in disjunction
Opposite Range in disjunction
No Index or Partition driver found
Predicate chain has all constant predicates
Negated predicate found
Long bitmap inlist in OR predicate
PRIOR expression in OR predicate
All 1-row tables found
No valid predicate for OR expansion
Disjunctive subquery unnesting possible
Subquery unnesting possible
Subquery coalesced query block
Merge view query block
Finally – here’s another reference blog note comparing LORE with ORE from Mohamed Houri.
A question about mixing the (relatively new) “fetch first” syntax with “select for update” appeared a few days ago on the Oracle Developer Forum. The requirement was for a query something like:
select
*
from
t1
order by
n1
fetch
first 10 rows only
for update
;
The problem with this query is that it results in Oracle raising error ORA-02014: cannot select FOR UPDATE from view with DISTINCT, GROUP BY, etc. The error doesn’t seem to be particularly relevant, of course, until you remember that “fetch first” creates an inline view using the analytic row_number() under the covers.
One suggested solution was to use PL/SQL to open a cursor with a pure select then use a loop to lock each row in turn. This would need a little defensive programming, of course, since each individual “select for update” would be running at a different SCN from the driving loop, and there would be some risk of concurrency problems (locking, or competing data change) occuring.
There is a pure – thought contorted – SQL solution though where we take the driving SQL and put it into a subquery that generates the rowids of the rows we want to lock, as follows:
select
/*+
qb_name(main)
*/
*
from
t1
where
t1.rowid in (
select
/*+ qb_name(inline) unnest no_merge */
t1a.rowid
from
t1 t1a
order by
t1a.n1
fetch
first 10 rows only
)
for update
;
The execution plan for this query is critical – so once you can get it working it would be a good idea to create a baseline (or SQL Patch) and attach it to the query. It is most important that the execution plan should be the equivalent of the following:
Critically you need the VIEW operation to be the driving query of a nested loop join that does the “table access by user rowid” joinback. In my case the query has used a full tablescan to identify the small number of rowids needed – in a production system that would be the part of the statement that should first be optimised.
It’s an unfortunate feature of this query structure (made messier by the internal rewrite for the analytic function) that it’s not easy to generate a correct set of hints to force the plan until after you’ve already managed to get the plan. Here’s the outline information that shows the messiness of the hints I would have needed:
You’ll notice that my /*+ unnest */ hint is now modified – for inclusion at the start of the query – to /*+ unnest(@sel1) */ rather than the /*+ unnest(@inline) */ that you might have expected. That’s the side effect of the optimizer doing the “fetch first” rewrite before applying “missing” query block names. If I wanted to write a full hint set into the query itself (leaving the qb_name() hints in place but removing the unnest and merge I had originally) I would need the following:
I did make a bit of a fuss about the execution plan. I think it’s probably very important that everyone who runs this query gets exactly the same plan and the plan should be this nested loop. Although there’s a BUFFER SORT at operation 2 that is probably ensuring that every would get the same data in the same order regardless of the execution plan before locking any of it, I would be a little worried that different plans might somehow be allowed to lock the data in a different order, thus allowing for deadlocks.
How do you work out what hints you need to tweak an execution plan into the shape you want?
Here’s a “case study” that’s been playing out over a few weeks on the Oracle Developer Community (hereand here) and most recently ended up (in one of its versions) as a comment on one of my blog notes. It looks like a long note, but it’s a note about how to find the little bit of information you need from a large output – so it’s really a short note that has to include a long output.
Problem: a query is not running fast enough, and it runs a very large number of times in a single batch (the original trace/tkprof file reported 842,000 executions). Each individual execution, though, is very quick (as far as we know – the individual examples we have seen take a few hundredths of a second). Here’s one execution plan for the query with Query Block / Object Alias information and Outline Data pulled from memory with rowsource execution statistics enabled.
This is just one of a handful of variations that all look fairly similar and there was plenty that could be said about the query and the plan; I only want to look at one idea, though. The point came where the suggestion came to eliminate the the full tablescans at operations 25 and 26. Here’s the relevant section of the plan, stripped back a bit to make it narrower:
To isolate the above as a relevant, self-contained, part of the plan I’ve checked that operation 26 has no child operations, and I’ve scanned up the plan to find the parent of child 26 – which turns out to be operation 18, which is a hash join with a nested loop (operation 19) as its first child and operation 26 as its second chlid.
We want to change operations 25 and 26 from full tablescans to indexed accesses; that’s the only change we need make for operation 25 which is the second table of a nested loop join, but we’ll also want to change the hash join at operation 18 into a nested loop join. To make it easy to create the right hints we start by checking the Query Block / Object Alias information to identify exactly what we’re dealing with and “where” we’re dealing with it in operations 25 and 26.
Now we can look in the Outline Data section for the hints which will say “do full tablescans on acr@sel$3 and adt@sel$3 in query block sel$7E0D484F“; and we’ll need to find a hint that tells us to do a hash join with adt4@sel$3 – and this is what we find:
We were a little lucky with the use_hash() hint here, as the situation could have been made a little murkier if the table we were after had also been subject to swapping join inputs (the swap_join_inputs() hint).
So all we need to do now is change those hints which (getting rid of redundant quotes, and converting to lower case because I don’t like block capitals everywhere) gives us the following:
index( @sel$7e0d484f act@sel$3 {name/definition of index})
index( @sel$7e0d484f adt@sel$3 {name/definition of index})
use_nl(@sel$7e0d484f adt@sel$3)
You have to decide your strategy for getting these hints in place, of course. Just sticking the three hints into the query probably isn’t a stable solution. Editing the outline information to include these hints (replacing the previous 3) then copying the whole outline into the query is a little messy and may not be allowed at your site. Creating an SQL Patch (with recent versions of Oracle) or an SQL Plan Baseline is probably the most appropriate strategy (possibly hacked into an SQL Profile, but I don’t like doing that). That’s a topic for another blog note, though, which I don’t need to write.
Summary
If you have a complex plan that needs a littletweaking, it’s fairly easy to find out how to change the current Outline Data to get where you want to be if you start by looking at the Query Block / Object Alias section of the plan for the operations you want to change, and then search the Outline Data for the query blocks, aliases and operations you’ve identified.
A couple of days ago I discussed an execution plan that displayed some variation in the way it handled subqueries and even threw in a little deception by displaying an anti-join that was the result of transforming a “not exists” subquery and a semi-join that looked at first sight as if it were going to be the result of transforming an “exists” subquery.
As part of the dissection I reverse engineered the query into a set of tables that would allow me to reproduce the execution plan so that I could report the “final query blocks” (outline_leafs). As a brief appendix to that blog note I’m publishing here the script to create those tables and three plans that I went through to get to the plan I needed.
rem
rem Script: anti_semi.sql
rem Author: Jonathan Lewis
rem Dated: Apr 2020
rem
rem Last tested
rem 19.3.0.0
rem 12.1.0.2
rem
create table ip_spells
partition by range (admission_dttm) interval (numtoyminterval(1,'MONTH')) (
partition p_start values less than (to_date('01-Jan-2020'))
)
as
with g as (
select rownum id from dual
connect by level <= 1e4 -- > avoid wordpress format issue
)
select
rownum spell_id,
to_char(mod(rownum,75),'FM00') admission_method_code,
to_date('01-jan_2020') + rownum / 1000 admission_dttm,
to_char(mod(rownum,57),'FM00') administrative_category_code,
lpad(rownum,10) v1,
rpad('x',100) padding
from
g,g
where
rownum <= 365000
;
alter table ip_spells add constraint sp_pk primary key(spell_id);
create table ip_episodes
as
select
spell_id,
spell_id episode_id,
1+mod(rownum,6) episode_order,
to_char(mod(rownum,125)+ 500,'FM999') main_specialty_code,
v1,
padding
from
ip_spells
order by
dbms_random.value
;
alter table ip_episodes add constraint ep_pk primary key(episode_id);
alter table ip_episodes add constraint ep_fk_sp foreign key (spell_id) references ip_spells;
create index idx02_ip_episodes on ip_episodes(spell_id);
create table ip_diagnosis
as
select
episode_id,
chr(mod(rownum,25) + 65) ||
to_char(dbms_random.value(30,512),'FM999') diagnosis_code,
mod(rownum,5) diagnosis_sequence,
lpad(rownum,10) v1,
rpad('x',100) padding
from
(select rownum id from dual connect by level <= 5),
ip_episodes
;
alter table ip_diagnosis add constraint di_fk_ep foreign key(episode_id) references ip_episodes;
create index idx01_ip_diagnosis on ip_diagnosis(episode_id);
The original ip_spells table was range partitioned and there was a date-based predicate in the query that encouraged me to use the date column as the partitioning column. I also decided to generate data for one year at 1,000 rows per day from the start of a year, with interval partitioning of one month to get a small number of partitions.
The original plan suggested that the number of ip_episodes was similar to the number of ip_spells, so I just used a copy of the rows from ip_spells to create ip_epsisodes, and then gave it some appropriate primary and foreign key constraints and indexes.
Finally, the ip_diagnosis table looked as if it held an average of 5 rows per ip_episodes, so I generated it from ip_episodes by joining to a 5-row set generated by the usual “connect by” trick with dual.
I’ve only got a small data set, and most of the indexes are sequence based with excellent clustering_factors, so I wasn’t going to be surprised if my data and stats didn’t immediately produce the execution plan of the original query.
Here’s the original query (just as a reminder), and the first plan I got with no hinting (running 12.1.0.2):
select
*
from
ip_spells a
where
not exists (
select
1
from
ip_episodes e
inner join
ip_diagnosis d
on
d.episode_id = e.episode_id
where
a.spell_id = e.spell_id
and (
substr(d.diagnosis_code,1,1) = 'C'
or substr(d.diagnosis_code,1,3) between 'D37' and 'D48'
or substr(d.diagnosis_code,1,1) = 'V'
or d.diagnosis_code = 'Z511'
)
)
and exists (
select
1
from
ip_episodes e
left join
ip_diagnosis d
on
d.episode_id = e.episode_id
where
a.spell_id = e.spell_id
and (
e.episode_order = '1'
and e.main_specialty_code not in ('501','560','610')
and d.diagnosis_sequence = 1
and substr(d.diagnosis_code,1,1) <> 'O'
)
)
and substr(a.admission_method_code,1,1) = '2' -- 1% selectivity on substr()
and a.admission_dttm >= to_date('01-jan-2011', 'dd-mon-yyyy')
and a.administrative_category_code = '01' -- 1 / 57 by definition
;
Plan hash value: 1492475845
----------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 131 | 1299 (5)| 00:00:06 | | |
|* 1 | FILTER | | | | | | | |
| 2 | PARTITION RANGE ALL | | 64 | 8384 | 935 (6)| 00:00:04 | 1 |1048575|
|* 3 | TABLE ACCESS FULL | IP_SPELLS | 64 | 8384 | 935 (6)| 00:00:04 | 1 |1048575|
| 4 | NESTED LOOPS SEMI | | 1 | 20 | 11 (0)| 00:00:01 | | |
| 5 | TABLE ACCESS BY INDEX ROWID BATCHED| IP_EPISODES | 1 | 10 | 4 (0)| 00:00:01 | | |
|* 6 | INDEX RANGE SCAN | IDX02_IP_EPISODES | 1 | | 3 (0)| 00:00:01 | | |
|* 7 | TABLE ACCESS BY INDEX ROWID BATCHED| IP_DIAGNOSIS | 40931 | 399K| 7 (0)| 00:00:01 | | |
|* 8 | INDEX RANGE SCAN | IDX01_IP_DIAGNOSIS | 5 | | 2 (0)| 00:00:01 | | |
| 9 | NESTED LOOPS SEMI | | 1 | 30 | 11 (0)| 00:00:01 | | |
|* 10 | TABLE ACCESS BY INDEX ROWID BATCHED| IP_EPISODES | 1 | 17 | 4 (0)| 00:00:01 | | |
|* 11 | INDEX RANGE SCAN | IDX02_IP_EPISODES | 1 | | 3 (0)| 00:00:01 | | |
|* 12 | TABLE ACCESS BY INDEX ROWID BATCHED| IP_DIAGNOSIS | 18250 | 231K| 7 (0)| 00:00:01 | | |
|* 13 | INDEX RANGE SCAN | IDX01_IP_DIAGNOSIS | 5 | | 2 (0)| 00:00:01 | | |
----------------------------------------------------------------------------------------------------------------------------
As you can see, both subqueries ran as FILTERsubqueries. If I were to include the query block information you would see that operations 4 to 8 are the (transformed) “not exists” subquery, operations 9 to 13 are the transformed “exists” subquery, and operations 1 to 3 represent the main body of the query.
I needed to see the “not exists” subquery unnested and transformed into a hash anti-join, so my first attempt at hinting was to add an /*+ unnest */ hint to that subquery, producing the following plan:
You can see the unnested subquery in the name vw_sq_1 at operation 5; but we have a nested loop anti at operation 2 when I want a hash join anti, and we also see the option “pushed predicate” for the view at operation 5.
If I published the query block information in this case you would still see 3 “final” query blocks. Operations 11 to 15 would be the “exists” subquery; operation 5 to 10 would be the query block for the non-mergeable view that the optimizer produced by unnesting the “not exists” subquery; and operations 1 to 4 represent the overall parent query block.
This example highlights a detail which is a little easy to miss in earlier discussions of the query and its plans. Some operations in a plan look as if they could be associated with two query block names – the query block for which they are the top line, and the query block by which they are used.
Taking this plan as an example, operation 5 is clearly the starting point of the query block from operations 5 to 10, and operation 11 is clearly the starting point for the query block from operations 11 to 15. On the other hand when we collapse query blocks to understand the overall structure of how the query operates we get the following plan – in which we view “Rowsource 2” and “Rowsource 3” as simple data sources in the main query block – and it would be nice to see operations 5 and 11 with the same query block name as operations 1 to 4.
We still have a little work to do to get where we want to be: the /*+ unnest */ hint has got us part way to the plan we want – so (taking my cue from the pushed predicate option) to get from the nested loop anti join to the hash anti join I decided to try changing the hint in the “not exists” subquery to /*+ unnest no_push_pred */ – and this is the plan that appeared as a result:
It looks the right shape, it’s got the right mechanisms in place, and (very importantly – though not a 100% guarantee) it’s got the same plan_hash_value as the orginally published plan. So at this point I felt the final query block names it reported would reflect the ones that would have been used in the original plan.
In a production system, of course, you don’t just stick a couple of hints into a query and hope it will be enough to stabilise the plan. Here’s the full set of hints that appeared in the Outline Data when I added my two hints to the query:
When I put my two hints into the query text and reran the test under 19.3 the FILTERoperation disappeared and the “exists” subquery also unnested (to become vw_sq_2), turning into a nested loop semi-join. With the full set of 40 hints in place the plan from 12.1.0.2 re-appeared.
I did actually have an alternative strategy for the manual hint test. The plan with the nested loop anti join reported the following query block information:
This prompted me to tell Oracle to do a hash join in query block SEL$2B969259 between A@SEL$4 and VW_SQ_1@SEL$F49409E0 (in that order) by adding a simple set of hints to the start of the query while leaving (just) the /*+ unnest */ hint in the “not exists” subquery.
select
/*+
leading (@sel$2b969259 a@sel$4 vw_sq_1@sel$f49409e0)
use_hash(@sel$2b969259 vw_sq_1@sel$f49409e0)
no_swap_join_inputs(@sel$2b969259 vw_sq_1@sel$f49409e0)
*/
*
from
Again, this produced the required execution path.
It’s not always this easy to reproduce an execution plan by looking at a query text – sometimes the actual plan depends on knowing about things like unique constraints, foreign key constraints, and not null constraints. But if you can get your hands on the SQL to create the objects involved it’s usually possible to re-create the plan by hinting, which then allows you to discover how you change the hints to modify the plan.
I’ve made casual remarks in the past about how “ANSI”-style SQL introduces extra complications in labelling or identifying query blocks – which means it’s harder to hint correctly. This is a note to show how the optimizer first transforms “ANSI” SQL into “Oracle” syntax. I’m going to write a simple 4-table join in classic Oracle form and check the execution plan with its query block names and fully qualified table aliases; then I’ll translate to the ANSI equivalent and repeat the check for query block names and aliases , finally I’ll rewrite the query in classic Oracle syntax that reproduces the query block names and fully qualified table aliases that we got from the ANSI form.
We start by creating and indexing 4 tables (with a script that I’ve been using for various tests for several years, but the results I’ll show come from 19c):
rem
rem Script: ansi_hint_3.sql
rem Author: Jonathan Lewis
rem Dated: June 2014
rem
create table t1
as
select
trunc((rownum-1)/4) t1_n1,
trunc((rownum-1)/4) t1_n2,
case mod(rownum,20) when 0 then rownum else -1 end flagged1,
rpad(rownum,180) t1_v1
from all_objects
where rownum <= 3000 --> comment to avoid wordpress format issue
;
create table t2
as
select
mod(rownum,200) t2_n1,
mod(rownum,200) t2_n2,
case mod(rownum,20) when 0 then rownum else -1 end flagged2,
rpad(rownum,180) t2_v1
from all_objects
where rownum <= 3000 --> comment to avoid wordpress format issue
;
create table t3
as
select
trunc((rownum-1)/4) t3_n1,
trunc((rownum-1)/4) t3_n2,
case mod(rownum,20) when 0 then rownum else -1 end flagged3,
rpad(rownum,180) t3_v1
from all_objects
where rownum <= 3000 --> comment to avoid wordpress format issue
;
create table t4
as
select
trunc((rownum-1)/4) t4_n1,
trunc((rownum-1)/4) t4_n2,
case mod(rownum,20) when 0 then rownum else -1 end flagged4,
rpad(rownum,180) t4_v1
from all_objects
where rownum <= 3000 --> comment to avoid wordpress format issue
;
create index t1_i1 on t1(t1_n1);
create index t2_i1 on t2(t2_n1);
create index t3_i1 on t3(t3_n1);
create index t4_i1 on t4(t4_n1);
Then we check the execution plan for a simple statement with what looks like a single named query block:
Note in the Query Block Name / Object Alias information that all 4 tables were “sourced from”, or have aliases qualified by, “@MAIN”, and in the final plan all the tables are used in a query block called MAIN.
Check the Plan Hash Value – it gives you a strong clue that the execution plans are the same, and a close examination of the body of the plan and the Predicate information confirm that the two queries operate in exactly the same way at exactly the same cost. But there’s a significant difference in the query blocks and table aliases.
The Query Block Name / Alias Alias information tells us that query block “main” has disappeared and the query operates completely from a query block with the internally generated name SEL$43767242; moreover we can see that tables t1 and t2 appear to be sourced from a query block called sel$1, while t3 comes from sel$2 and t4 comes from sel$3.
Finally here’s a messy Oracle form to reproduce the ANSI query block names and table aliases:
Again a quick check of the Plan Hash Value confirms that the messier query is a match for the previous query with its ANSI transformation, and the plan body and Query Block Name / Object Alias information confirm the match throughout in the naming.
Any time you write ANSI syntax this layering of nested inline views is what happens to your query before any other transformation is applied – and sometimes (though very rarely in recent versions of Oracle) this can result in unexpected limitations in the way the optimizer subsequently transforms the query.
Apart from “accidents”, though, the big issue with the “ANSI rewrite” comes from the side effects of all the extra query blocks. In anything but the simplest cases you have to work a little harder to figure out the query block names you need to use if you want to apply hints to fix an optimizer problem – you can’t create your own meaningful names for every query block in the query you wrote. Fortunately this task is made a little easier if you check the execution plan of the query after adding the hint /*+ no_query_transformation */, as this tends to produce a plan that looks like a step by step “translation” of the way the query was written (apart from the ANSI transformation, of course). This might be enough to identify the base-level query blocks that the optimizer starts with when you use ANSI syntax.
Update Sep 2021
Having just discovered the qbregistry and qbregisrty_graph options for the format command in dbms_xplan.display_cursor() of 21c I couldn’t resist generating the registry_graph output of the ANSI join and running it through www.webgraphviz.com to show the following:
Several years ago (2011) I wrote a note describing how you could attach the Outline Information from one query to the SQL_ID of another query using the official Oracle mechanism of calling dbms_spm.load_plans_from_cursor_cache(). Shortly after publishing that note I drafted a follow-up note with an example demonstrating that even when the alternative outline was technically relevant the optimizer might still fail to use the SQL Plan Baseline. Unfortunately I didn’t quite finish the draft – until today.
The example I started with nearly 10 years ago behaved correctly against 11.1.0.7, but failed to reproduce the plan when I tested it against 11.2.0.3, and it still fails against 19.3.0.0. Here’s the test data and the query we’re going to attempt to manipulate:
rem
rem Script: fake_sql_baseline_4.sql
rem Author: Jonathan Lewis
rem Dated: Oct 2010
rem
create table emp1 (
dept_no number /* not null */,
sal number,
emp_no number,
padding varchar2(200),
constraint e1_pk primary key(emp_no)
)
;
create table emp2 (
dept_no number /* not null */,
sal number,
emp_no number,
padding varchar2(200),
constraint e2_pk primary key(emp_no)
)
;
insert into emp1
select
mod(rownum,6),
rownum,
rownum,
rpad('x',200)
from
all_objects
where
rownum <= 20000 -- > comment to avoid wordpress format issue
;
insert into emp2
select
mod(rownum,6),
rownum,
rownum,
rpad('x',200)
from
all_objects
where
rownum <= 20000 -- > comment to avoid wordpress format issue
;
begin
dbms_stats.gather_table_stats(
ownname => user,
tabname => 'EMP1',
cascade => true,
method_opt =>'for all columns size 1'
);
dbms_stats.gather_table_stats(
ownname => user,
tabname => 'EMP2',
cascade => true,
method_opt =>'for all columns size 1'
);
end;
/
select
/*+ target_query */
count(*)
from
emp1
where
emp1.dept_no not in (
select dept_no
from emp2
)
;
select * from table(dbms_xplan.display_cursor(null, null, 'outline'));
I haven’t included the code I run on my testbed to delete all existing SQL Plan Baselines before running this test, I’ll post that at the end of the article.
The query is very simple and will, of course, return no rows since emp1 and emp2 are identical and we’re looking for departments in emp1 that don’t appear in emp2. The “obvious” plan for the optimizer is to unnest the subquery into a distinct (i.e. aggregate) inline view then apply an anti-join. It’s possible that the optimizer will also decide to do complex view merging and postpone the aggregation. Here’s the execution plan from 19.3:
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 168 (100)| |
| 1 | SORT AGGREGATE | | 1 | 6 | | |
|* 2 | HASH JOIN ANTI NA | | 3333 | 19998 | 168 (5)| 00:00:01 |
| 3 | TABLE ACCESS FULL| EMP1 | 20000 | 60000 | 83 (4)| 00:00:01 |
| 4 | TABLE ACCESS FULL| EMP2 | 20000 | 60000 | 83 (4)| 00:00:01 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("EMP1"."DEPT_NO"="DEPT_NO")
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 1 (E - Syntax error (1))
---------------------------------------------------------------------------
0 - SEL$1
E - target_query
As expected the subquery unnested, we have the anti-join (in this case, since dept_no can be null, it’s a “Null Aware Anti Join”); and the optimizer has, indeed, decided to do the join before the aggregation.
Assume, now, that for reasons known only to me a merge (anti) join would be more effective than a hash (anti) join. To get the optimizer to do this I’m going to capture the query and associate it with a plan that uses a merge join. There are several minor variations on how we could do this, but I’m going to follow the steps I took in 2011 – but cut out a couple of the steps where I loaded redundant baselines into the SMB (SQLPlan Management Base). As a starting point I’ll just record the sql_id and plan_hash_value for the query (and the child_number just in case I want to use dbms_xplan.display_cursor() to report the in-memory execution plan):
column sql_id new_value m_sql_id_1
column plan_hash_value new_value m_plan_hash_value_1
column child_number new_value m_child_number_1
select
sql_id, plan_hash_value, child_number
from
v$sql
where
sql_text like '%target_query%'
and sql_text not like '%v$sql%'
and rownum = 1
;
Now I’ll hack the query to produce a plan that does the merge join. An easy first step is to look at the current outline and take advantage of the hints there. You’ll notice I included the ‘outline’ format in my call to dbms_xplan.display_cursor() above, even though I didn’t show you that part of the output – here it is now:
So I’m going to take the useful-looking hints, get rid of the use_hash() hint and, for good measure, turn it into a no_use_hash() hint. Here’s the resulting query, with its execution plan:
Note that I’ve included the text “alternative_query” at the end of the hint list as something to use when I’m searaching v$sql. Note also, that the “no_use_hash()” hint has disappeared and has been replaced by “use_merge()” hint.
The plan tells us that the optimizer is happy to use a “merge join anti NA”, so we can load this plan’s outline iinformation into the SMB by combining the sql_id and plan_hash_value for this query with (for older versions of Oracle, though you can now use the sql_id in recent versions) the text of the previous query so that we can store the new plan. with the old text:
column sql_id new_value m_sql_id_2
column plan_hash_value new_value m_plan_hash_value_2
column child_number new_value m_child_number_2
select
sql_id, plan_hash_value, child_number
from
v$sql
where
sql_text like '%alternate_query%'
and sql_text not like '%v$sql%'
and rownum = 1
;
declare
m_clob clob;
begin
select
sql_fulltext
into
m_clob
from
v$sql
where
sql_id = '&m_sql_id_1'
and child_number = &m_child_number_1
;
dbms_output.put_line(m_clob);
dbms_output.put_line(
'Number of plans loaded: ' ||
dbms_spm.load_plans_from_cursor_cache(
sql_id => '&m_sql_id_2',
plan_hash_value => &m_plan_hash_value_2,
sql_text => m_clob,
fixed => 'YES',
enabled => 'YES'
)
);
end;
/
At this point we have one SQL Plan Baseline in the SMB, and it says the old query should execute usng the new plan. So let’s give it a go:
set serveroutput off
alter system flush shared_pool;
alter session set events '10053 trace name context forever';
select
/*+ target_query */
count(*)
from
emp1
where
emp1.dept_no not in (
select dept_no
from emp2
)
/
alter session set events '10053 trace name context off';
select * from table(dbms_xplan.display_cursor(null, null, 'alias outline'));
I’ve enabled the 10053 (optimizer) trace so that I can report a critical few lines from it later on. Here’s the execution plan, omitting the Outline Information but including the Query Block /Alias information.
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 168 (100)| |
| 1 | SORT AGGREGATE | | 1 | 6 | | |
|* 2 | HASH JOIN ANTI NA | | 3333 | 19998 | 168 (5)| 00:00:01 |
| 3 | TABLE ACCESS FULL| EMP1 | 20000 | 60000 | 83 (4)| 00:00:01 |
| 4 | TABLE ACCESS FULL| EMP2 | 20000 | 60000 | 83 (4)| 00:00:01 |
----------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$5DA710D3
3 - SEL$5DA710D3 / EMP1@SEL$1
4 - SEL$5DA710D3 / EMP2@SEL$2
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("EMP1"."DEPT_NO"="DEPT_NO")
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 1 (E - Syntax error (1))
---------------------------------------------------------------------------
0 - SEL$1
E - target_query
Note
-----
- Failed to use SQL plan baseline for this statement
We haven’t used the SQL Plan Baseline – and in 19.3 we even have a note that the optimizer knew there was at least one baseline available that it failed to use! So what went wrong?
I have two diagnostics – first is the content of the baseline itself (warning – the SQL below will report ALL currently saved SQL Plan Baselines); I’ve just made sure that I have only one to report:
set linesize 90
select
pln.*
from
(select sql_handle, plan_name
from dba_sql_plan_baselines spb
order by
sql_handle, plan_name
) spb,
table(dbms_xplan.display_sql_plan_baseline(spb.sql_handle, spb.plan_name)) pln
;
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------
SQL handle: SQL_ce3099e9e3bdaf2f
SQL text: select /*+ target_query */ count(*) from emp1
where emp1.dept_no not in ( select dept_no
from emp2 )
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
Plan name: SQL_PLAN_cwc4tx7jvvbtg02bb0c12 Plan id: 45812754
Enabled: YES Fixed: YES Accepted: YES Origin: MANUAL-LOAD-FROM-CURSOR-CACHE
Plan rows: From dictionary
--------------------------------------------------------------------------------
Plan hash value: 1517539632
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 178 (100)| |
| 1 | SORT AGGREGATE | | 1 | 6 | | |
| 2 | MERGE JOIN ANTI NA | | 3333 | 19998 | 178 (11)| 00:00:01 |
| 3 | SORT JOIN | | 20000 | 60000 | 89 (11)| 00:00:01 |
| 4 | TABLE ACCESS FULL| EMP1 | 20000 | 60000 | 83 (4)| 00:00:01 |
|* 5 | SORT UNIQUE | | 20000 | 60000 | 89 (11)| 00:00:01 |
| 6 | TABLE ACCESS FULL| EMP2 | 20000 | 60000 | 83 (4)| 00:00:01 |
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("EMP1"."DEPT_NO"="DEPT_NO")
filter("EMP1"."DEPT_NO"="DEPT_NO")
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 1 (E - Syntax error (1))
---------------------------------------------------------------------------
0 - SEL$1
E - alternate_query
We have an SQL Plan baseline that is accepted, enabled, and fixed; and it’s supposed to produce a “merge join anti NA”, and it clearly “belongs” to our query. So it should have been used.
Then we have the 10053 trace file, in which we find the following:
SPM: planId in plan baseline = 45812754, planId of reproduced plan = 1410137244
------- START SPM Plan Dump -------
SPM: failed to reproduce the plan using the following info:
parse_schema name : TEST_USER
plan_baseline signature : 14857544400522555183
plan_baseline plan_id : 45812754
plan_baseline hintset :
hint num 1 len 27 text: IGNORE_OPTIM_EMBEDDED_HINTS
hint num 2 len 35 text: OPTIMIZER_FEATURES_ENABLE('19.1.0')
hint num 3 len 20 text: DB_VERSION('19.1.0')
hint num 4 len 8 text: ALL_ROWS
hint num 5 len 29 text: OUTLINE_LEAF(@"SEL$5DA710D3")
hint num 6 len 16 text: UNNEST(@"SEL$2")
hint num 7 len 17 text: OUTLINE(@"SEL$1")
hint num 8 len 17 text: OUTLINE(@"SEL$2")
hint num 9 len 36 text: FULL(@"SEL$5DA710D3" "EMP1"@"SEL$1")
hint num 10 len 36 text: FULL(@"SEL$5DA710D3" "EMP2"@"SEL$2")
hint num 11 len 54 text: LEADING(@"SEL$5DA710D3" "EMP1"@"SEL$1" "EMP2"@"SEL$2")
hint num 12 len 41 text: USE_MERGE(@"SEL$5DA710D3" "EMP2"@"SEL$2")
During optimization the optimizer has found that SQL Plan Baseline. We can see that the hints in the baseline are exactly the hints from the plan that we wanted – but the optimizer says it can’t reproduce the plan we wanted. In fact if you try adding these hints (omitting the first three) to the query itself in old versions of Oracle you’ll find that the merge join will appear as expected; but it won’t appear in versions newer than 11.1.0.7 and Oracle will use a hash join. If you want a merge join you’ll have to use the /*+ no_use_hash() */ hint and, as you can see, that won’t get into the SQL Plan Baseline.
Conclusion
This is just a simple example of how the optimizer may be able to produce a plan if hinted in one way, but the Outline Information consists of a different set of hints that won’t reproduce the plan they seem to describe. My no_use_hash() hint has been discarded and replaced with a use_merge() hint that fails to reproduce the merge join in circumstances that makes me think there’s a bug in the optimizer.
If you happen to be unlucky you may find that the plan you really need to see can’t be forced through a SQL Plan Baseline. In this example it may be necessary to use the SQL Patch mechanism to include the no_use_hash() hint in a set of hints you associate with the query.
