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
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.
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 exactly these hints to the query itself you’ll still find that the merge join won’t appear and Oracle will use a hash join.
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.
The ODC SQL and PL/SQL forum came up with an example a couple of days ago that highlighted an annoying limitation in the optimizer’s handling of table functions. The requirement was for a piece of SQL that would generate “installments” information from a table of contract agreements and insert into another table any installments that were not yet recorded there.
The mechanism to turn a single row of contract data into a set of installments was a (optionally pipelined) table function that involved some business logic that (presumably) dealt with the timing and size of the installments. The final SQL to create the data that needed to be inserted was reported as follows (though it had clearly been somewhat modified):
select
y.*,
trunc(sysdate,'mm'),
user
from
table_a a
join
table(
my_function(
a.loan_acct_nbr,
a.start_dt,
a.maturity_dt,
a.num_of_terms
)
) y
on
a.loan_acct_nbr = y.loan_acct_nbr
where
a.yr_mnth_cd = to_char(add_months(sysdate,-1),'yyyymm') -- last month
and a.loan_typ = 'ABC'
and not exists (
select 1
from
final_load_table l
where
l.loan_acct_nbr = y.loan_acct_nbr
and l.yr_mnth_cd = y.yr_mnth_cd
)
;
In this query, table_a is the set of contracts, final_load_table is the set of installments, and my_function() is the pipelined function returning a table of installments derived from the start date, maturity date, and number of installments for a contract. The query needs a “not exists” subquery to eliminate any installments that are already known to the database. Once this query is operating efficiently it could be used either to drive a PL/SQL loop or (generally the better strategy) to do a simple “insert as select”.
We were told that the function would return around 60 rows per contract; that the starting size of the final_load_table would be about 60M rows, and the size of the result set from the initial join would be about 60M or 70M rows (which suggests about 1M rows acquired from table_a).
The owner of this problem seemed to be saying that the query had returned no data after 4 or 5 hours – which suggests (a) the optimizer chose a bad plan and/or (b) the PL/SQL function is working very inefficiently and/or (c) the join had generated a vast amount of data but the effect of the subquery was to discard all of it .
Clearly we needed to see an execution plan (preferably from SQL Monitor) and be given have some idea of how much of the 60M/70M rows predicted for the join would be discarded because it already existed.
The owner did send us an execution plan – which included a very obvious threat and suggested a couple of optimizer errors – but supplied it as a picture rather than a flat text.
You’ll notice, of course, that the tables and columns have changed from the original statement. More significantly, though, there are serious problems with the numbers – the estimated row count for the basic join is only 5,326 rather than 50 Million which, at first sight, is probably why the optimizer has decided that a filter subquery (operation 1) to do an index-only probe (operation 5) is a good way of handling the subquery. Perhaps if the estimates had been a little more accurate (e.g. through the addition of a couple of column groups or, if necessary, by opt_estimate() or cardinality() hints) the subquery would have been unnested and turned into a hash anti-join.
I suggested a quick test of a suitable cardinality() hint – but ran up a little model to check that I’d got the hint right – and found that I had but it wasn’t going to help. So I decided to write up the model (and a possible solution for the owner of the problem) in this blog note.
Here’s the code to create the set of objects I’m going to work with. The naming follows the naming in the original statement of the problem suggested by the owner:
rem
rem Script: table_function_plan.sql
rem Author: Jonathan Lewis
rem Dated: Jan 2020
rem Purpose:
rem
rem Last tested
rem 19.3.0.0
rem 12.2.0.1
rem
create table table_a (
loan_acct_nbr number,
yr_mnth_cd varchar2(6),
start_dt date,
maturity_dt date ,
number_of_terms number,
constraint ta_pk primary key(loan_acct_nbr, yr_mnth_cd)
)
;
execute dbms_random.seed(0)
insert /*+ append */
into table_a
with generator as (
select rownum id
from dual
connect by
level <= 4000 -- > comment to avoid wordpress issue
)
select
trunc(dbms_random.value(1e9, 2e9)),
to_char(sysdate-(365-mod(rownum,365)),'yyyymm'),
sysdate-(365-mod(rownum,365)),
sysdate+(1500+mod(rownum,365)),
60
from
generator v1,
generator v2
where
rownum <= 1e4 -- > comment to avoid WordPress format issue
;
create table final_load_table_l(
loan_acct_nbr number,
yr_mnth_cd varchar2(6),
v1 varchar2(10),
padding varchar2(200),
constraint lt_pk primary key(loan_acct_nbr, yr_mnth_cd)
)
;
insert /*+ append */ into final_load_table_l
with generator as (
select rownum id
from dual
connect by
level <= 4000 -- > comment to avoid wordpress issue
)
select
trunc(dbms_random.value(1e9, 2e9)),
to_char(sysdate-(365-mod(rownum,365)),'yyyymm'),
lpad(rownum,10),
lpad('x',200,'x')
from
generator v1,
generator v2
where
rownum <= 1e4 -- > comment to avoid WordPress format issue
;
begin
dbms_stats.gather_table_stats(
ownname => null,
tabname => 'table_a',
method_opt => 'for all columns size 1'
);
dbms_stats.gather_table_stats(
ownname => null,
tabname => 'final_load_table_l',
method_opt => 'for all columns size 1'
);
end;
/
create type my_row_type as object (
loan_acct_nbr number,
yr_mnth_cd varchar2(6),
v1 varchar2(10),
padding varchar2(200)
);
/
create type my_table_type as table of my_row_type;
/
create or replace function my_function (
i_loan_acct_nbr in number,
i_yr_mnth_cd in varchar2,
i_start_dt in date,
i_maturity_dt in date,
i_number_of_terms in number
) return my_table_type pipelined
as
begin
for i in 1..i_number_of_terms loop
pipe row (
my_row_type(
i_loan_acct_nbr,
to_char(i_start_dt+32*i,'yyyymm'),
i,
lpad('x',200,'x')
)
);
end loop;
return;
end;
/
I was planning to create some large tables – hence the option to generate 16M rows from my generatorCTEs – but I discovered the critical problem almost as soon as I had some data and code in place, so I didn’t need to go large.
I’ve had to create an object type and table type in order to create a pipelined function that returns the table type by piping rows of the object type. The data I’ve created, and the way the function generates data probably doesn’t bear much resemblance to the real system of course, but I don’t think it needs to be very close to make the crucial point.
Here’s the text of the select statement the OP wants to run, with the execution plan I got from my data set after running the query and pulling the plan from memory:
alter session set statistics_level = all;
select
/*+ find this 1 */
y.*
from
table_a a,
table(my_function(
a.loan_acct_nbr,
a.yr_mnth_cd,
a.start_dt,
a.maturity_dt,
a.number_of_terms
)) y
where
a.yr_mnth_cd = '202001'
and not exists (
select
/*+ unnest */
null
from final_load_table_l l
where l.loan_acct_nbr = y.loan_acct_nbr
and l.yr_mnth_cd = y.yr_mnth_cd
)
;
--------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| A-Rows | A-Time | Buffers |
--------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 5059K(100)| 14580 |00:00:00.15 | 16330 |
|* 1 | FILTER | | 1 | | | 14580 |00:00:00.15 | 16330 |
| 2 | NESTED LOOPS | | 1 | 6283K| 10421 (8)| 14580 |00:00:00.11 | 335 |
|* 3 | TABLE ACCESS FULL | TABLE_A | 1 | 769 | 10 (10)| 243 |00:00:00.01 | 297 |
| 4 | COLLECTION ITERATOR PICKLER FETCH| MY_FUNCTION | 243 | 8168 | 14 (8)| 14580 |00:00:00.10 | 38 |
|* 5 | INDEX UNIQUE SCAN | LT_PK | 14580 | 1 | 1 (0)| 0 |00:00:00.02 | 15995 |
--------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter( IS NULL)
3 - filter("A"."YR_MNTH_CD"='202001')
5 - access("L"."LOAN_ACCT_NBR"=:B1 AND "L"."YR_MNTH_CD"=:B2)
I’ve put in a hint to tell the optimizer to unnestthe subquery – and it didn’t. Oracle does not ignore hints (unless they’re illegal, or out of context, or the optimizer never got to them, or you’ve found a bug) so why did Oracle appear to ignore this hint? There’s a really nice feature in 19.3 execution plans – you can request a hint report for a plan, and here’s the hint report for this query (ignore the bits about “find this” being an error):
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 3 (U - Unused (1), E - Syntax error (2))
---------------------------------------------------------------------------
0 - SEL$1
E - find
E - this
5 - SEL$3
U - unnest / Invalid correlated predicates
I put in an /*+ unnest */ hint to unnest the subquery, and I’ve been told that the predicates are not valid. The only thing about them that could be invalid is that they come from a pipelined function that has returned an object type. The pipelined function does not behave exactly like a table. But this gives me a clue about forcing the unnest to happen – hide the pipelined function inside a non-mergeable view.
I’ve wrapped the basic join between table_a and the table function in an inline view called (unimaginatively) v, and I’ve added a /*+ no_merge */ hint to that inline view. So the main query becomes a select from a non-mergeable view with a “not exists” subquery applied to a couple of view columns. And Oracle thinks that that’s okay – and my unnest hint encouraged the optimizer to use a hash anti-join.
So here’s an option for the owner of the problem – but with a few outstanding questions: will a rewrite of their query in this form do the same, will the performance of the select be good enough, and will the subsequent “insert as select” keep the same driving plan.
There’s one significant detail to worry about: the build table in this hash (anti-)join is going to be constructed from 50M (load_acct_bfr, yr_mnth_cd) pairs – which means the workarea requirement is likely to be about 1.2GB for an optimial (i.e. in-memory) hash join; otherwise the join may spill to disc and do a lot of I/O – probably as a one-pass hash join.
(Did you notice,by the way, that the word storage appeared at operation 3 in the original plan? That suggests a nice big Exadata box; however, there’s no storage predicate in the Predicate Information section for that operation and you would have thought that lease_type = ‘US_SSP’ would be pushed to storage, so maybe this is a ZFS Pillar backing a less exotic server.)
Conclusion
Some (if not all) types of correlated subqueries behave badly if the correlation predicates involve table functions. But you may be able to work around the issue by hiding part of the query, including the table function, inside a non-mergeable inline view before applying the subquery to the view.
Footnote
When I realised that the table function was causing a problem unnesting I remembered that I had written about a similar problem a few years ago – after searching for a little while I discovered a draft note that I had started in September 2015 but had not quite finished; so I’ll be finishing it off and publishing it some time in the next few days. (Update: now published)
The problem with finding something new and fiddling with it and checking to see how you can best use it to advantage is that you sometimes manage to “break” it very quickly. In yesterday’s blog note I introduced the /*+ push_having_to_gby(@qbname) */ hint and explained why it was a useful little enhancement. I also showed a funny little glitch with a missing predicate in the execution plan.
Today I thought I’d do something a little more complex with the example I produced yesterday, and I’ve ended up with a little note that’s not actually about the hint, it’s about something that appeared in my initial testing of the hint, and then broke when I pushed it a little further. Here’s a script to create data for the new test:
rem
rem Script: push_having_2.sql
rem Author: Jonathan Lewis
rem Dated: Dec 2019
rem Purpose:
rem
rem Last tested
rem 19.3.0.0
rem
create table t1
nologging
as
with generator as (
select
rownum id
from dual
connect by
level <= 1e4 -- > comment to avoid WordPress format issue
)
select
rownum id,
lpad(rownum,10,'0') v1,
lpad('x',50,'x') padding
from
generator v1,
generator v2
where
rownum <= 1e6 -- > comment to avoid WordPress format issue
;
insert into t1 values (2, lpad(2,10,'0'), lpad('x',50,'x'));
commit;
alter table t1 modify id not null;
create index t1_i1 on t1(id) nologging;
create table t2 as select * from t1;
create index t2_i1 on t2(id) nologging;
I’ve created two tables here, one a clone of the other, with one id value out of 1 million having two rows. As we saw yesterday it’s quite simple to write some SQL that uses an index full scan on the t1_i1 index to check for duplicate id values without doing a massive sort or hash aggregation:
set serveroutput off
alter session set statistics_level = all;
select
/*+
qb_name(driver)
index(@driver t1@driver)
*/
id
from
t1
where id is not null
group by
id
having
count(1) > 1
;
select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads |
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.87 | 2229 | 2228 |
| 1 | SORT GROUP BY NOSORT| | 1 | 50000 | 1 |00:00:00.87 | 2229 | 2228 |
| 2 | INDEX FULL SCAN | T1_I1 | 1 | 1000K| 1000K|00:00:00.40 | 2229 | 2228 |
-------------------------------------------------------------------------------------------------
As we saw yesterday this plan simply walks the index in order keeping track of a “running count” and doesn’t allocate a large PGA to sort a million rows of data, but there’s no asterisk by any operation telling us that there’s a predicate being checked, and no Predicate Information section to report the “count(1) > 1” predicate that we know exists (and is used, since the query produces the right answer).
Having ascertained that there is one duplicated id in the table, let’s join to the (clone) t2 table to list the rows for that id – and lets use the initial query as an inline view:
As you can see from this plan, I didn’t get the “sort group by nosort” that I wanted – even though the inline view was not merged. In fact, you’ll notice the /*+ no_use_hash_aggregation() */ hint I had to include to get a sort group by rather than a hash group by. The logic behind resolving this query block changed significantly when it went into a more complex query.
Having tried adding several other hints (blocking nlj_prefetch, nlj_batching, batched index access, setting cardinality to 1, first_rows(1) optimisation) I finally came down to using a materialized CTE (common table expression / “with” subquery):
with v1 as (
select
/*+
qb_name(driver)
index(@driver t1@driver)
materialize
*/
id
from
t1
where
id is not null
group by
id
having
count(1) > 1
)
select
/*+
qb_name(main)
*/
t2.v1
from
v1,
t2
where
t2.id = v1.id
;
select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
---------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
---------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 2 |00:00:00.86 | 2236 |
| 1 | TEMP TABLE TRANSFORMATION | | 1 | | 2 |00:00:00.86 | 2236 |
| 2 | LOAD AS SELECT (CURSOR DURATION MEMORY)| SYS_TEMP_0FD9D66F8_E3B235A | 1 | | 0 |00:00:00.86 | 2229 |
| 3 | SORT GROUP BY NOSORT | | 1 | 50000 | 1 |00:00:00.86 | 2228 |
| 4 | INDEX FULL SCAN | T1_I1 | 1 | 1000K| 1000K|00:00:00.39 | 2228 |
| 5 | NESTED LOOPS | | 1 | 50000 | 2 |00:00:00.01 | 6 |
| 6 | NESTED LOOPS | | 1 | | 2 |00:00:00.01 | 4 |
| 7 | VIEW | | 1 | 50000 | 1 |00:00:00.01 | 0 |
| 8 | TABLE ACCESS FULL | SYS_TEMP_0FD9D66F8_E3B235A | 1 | 50000 | 1 |00:00:00.01 | 0 |
|* 9 | INDEX RANGE SCAN | T2_I1 | 1 | | 2 |00:00:00.01 | 4 |
| 10 | TABLE ACCESS BY INDEX ROWID | T2 | 2 | 1 | 2 |00:00:00.01 | 2 |
---------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
9 - access("T2"."ID"="V1"."ID")
You’ll notice that the hinting is back to the bare minimum – with only the addition of the /*+ materialize */ hint in the CTE. You’ll also notice that the “count(1) > 1” predicate is still missing. But critically we do have the index full scan leading into a sort group by nosort and no huge memory allocation.
The price we have to pay is that we do direct path writes to the temporary tablespace to materialize the CTE and db file scattered reads to read the data back. But since this example is aimed at a large data set returning a small result set this may be a highly appropriate trade off.
It’s possible that a detailed examination of the 10053 trace file would give us a clue about why Oracle can find the sort group by nosort when the query block is a materialized CTE but not when it’s an inline view – but I’m happy to leave that investigation to someone else and just leave this here as a warning that sometimes (even in 19c) there’s a difference between a non-merged view path and a materizlied subquery path.
Here’s a thought that falls somewhere between philosophical and pragmatic. It came up while I was playing around with a problem from the Oracle database forum that was asking about options for rewriting a query with a certain type of predicate. This note isn’t really about that question but the OP supplied a convenient script to demonstrate their requirement and I’ve hi-jacked most of the code for my own purposes so that I can ask the question:
Should the presence of an intermediate view name generated by the optimizer in the course of cost-based query transformation cause two plans, which are otherwise identical and do exactly the same thing, to have different plan hash values ?
To demonstrate the issue let’s start with a simple script to create some data and generate an execution plan.
rem
rem Script: or_expand_plans.sql
rem Author: Jonathan Lewis
rem Dated Oct 2019
rem
rem Last tested
rem 19.3.0.0
rem 12.2.0.1
rem
rem Notes:
rem Data creation copied from ODC
rem https://community.oracle.com/thread/4297365
rem
create table t as
select 1 as id, to_date('2019-10-11', 'YYYY-MM-DD') as lastupdated, 'Toronto' as place from dual
union all
select 2 as id, to_date('2019-10-12', 'YYYY-MM-DD') as lastupdated, 'Toronto' as place from dual
union all
select 3 as id, to_date('2019-10-15', 'YYYY-MM-DD') as lastupdated, 'Toronto' as place from dual
union all
select 4 as id, to_date('2019-10-21', 'YYYY-MM-DD') as lastupdated, 'Toronto' as place from dual
;
create table t_others as
select 1 as id, to_date('2019-10-12', 'YYYY-MM-DD') as lastupdated, 'Blue' as color, 'Zone 7' as zoneid from dual
union all
select 2 as id, to_date('2019-10-21', 'YYYY-MM-DD') as lastupdated, 'Red' as color, 'Zone 7' as zoneid from dual
union all
select 3 as id, to_date('2019-10-16', 'YYYY-MM-DD') as lastupdated, 'White' as color, 'Zone 7' as zoneid from dual
union all
select 4 as id, to_date('2019-10-17', 'YYYY-MM-DD') as lastupdated, 'Green' as color, 'Zone 7' as zoneid from dual
;
create table t_further_info as
select 1 as id, to_date('2019-10-12', 'YYYY-MM-DD') as lastupdated, 'A' as typeinfo from dual
union all
select 2 as id, to_date('2019-10-14', 'YYYY-MM-DD') as lastupdated, 'C' as typeinfo from dual
union all
select 3 as id, to_date('2019-10-21', 'YYYY-MM-DD') as lastupdated, 'D' as typeinfo from dual
union all
select 4 as id, to_date('2019-10-21', 'YYYY-MM-DD') as lastupdated, 'E' as typeinfo from dual
;
prompt ====================
prompt "Traditional" syntax
prompt ====================
explain plan for
select
/*+ or_expand */
*
from
t,
t_others pt,
t_further_info fi
/*
where (
t.lastupdated >= to_date('2019-10-21', 'YYYY-MM-DD')
or pt.lastupdated >= to_date('2019-10-21', 'YYYY-MM-DD')
or fi.lastupdated >= to_date('2019-10-21', 'YYYY-MM-DD')
)
*/
where to_date('2019-10-21', 'YYYY-MM-DD') <= any(t.lastupdated, pt.lastupdated, fi.lastupdated)
and pt.id = t.id
and fi.id = t.id
;
select * from table(dbms_xplan.display(null,null,'outline'));
You’ll see that I have a simple three-table join with the nasty little detail that I have a “non-join” predicates that may require Oracle to check across all three tables before it can decide whether or not a row should be discarded. I’ve shown two variants on a theme – they both have exactly the same effect but the ANY() presentation is just a little bit neater and more compact.
Essentially Oracle can use one of two strategies for this type of query/predicate; the first is to join all three tables and wait until the final join rowsource appears and then apply the check, or it can split the query into a union all of three separate queries where each query drives off a different table selecting only the rows from that table that match “its” part of the predicate.
In the latter case the second and third branches of the union all have to be modified to ensure that they discard any rows already returned by preceding parts of the union all; this can mean lots of new predicates appearing that use the lnnvl() function. (Of course there are variants between these two extremes, but the general principle is unchanged.)
The presence of the (incomplete) /*+ or_expand */ hint in my query is there to tell the optimizer that it should attempt to transform the query into the three-part union all. This, by the way, is a 12c feature, though older versions of Oracle could get similar effects in some cases from the /*+ use_concat */ hint. Here’s the plan, with outline, I got from 12.2.0.1:
This is a wonderful demonstration of how brilliant the optimizer can be. The query has gone through several transformations and two of them have very high visibility. First, you can see the name VW_ORE_67EF6547 at operation 1. This is a view name that Oracle generates to express (cost-based) “OR-Expansion” so the optimizer has clearly obeyed my hint. As a consequence of OR-expansion we can also see several examples of the lnnvl() function appearing in the Predicate Information section of the output.; we can also see the hint re-appearing in the completed form of OR_EXPAND(@”SEL$1″ (1) (2) (3)) in the Outline Data.
However, we don’t have the union all of three pieces that we might have expected; we have a union all of two pieces and the second piece is a hash join between the table t_further_info and a view called VW_JF_SET$FB5125FC. This view is the result of “join factorization”. The optimizer has taken the 2nd and 3rd sections of our union all view and decided that it would be cost-effective to “factor out” a common table, so this:
select from t_others, t, t_further_info ... where t_others.date_predicate ...
union all
select from t, t_others, t_further_info ... where t.date_predicate ...
changes to this:
select from
(
select from t_others, t ... where t_others.date_predicate ...
union all
select from t, t_others ... where t.date_predicate ...
),
t_further_info
where ...
Having said all that, I now have to change the code because the original query was written using “ANSI” style joins – like this:
explain plan for
select
/*+ or_expand */
*
from
t
inner join
t_others pt
on pt.id = t.id
inner join
t_further_info fi
on fi.id = t.id
where
(
t.lastupdated >= to_date('2019-10-21', 'YYYY-MM-DD')
or pt.lastupdated >= to_date('2019-10-21', 'YYYY-MM-DD')
or fi.lastupdated >= to_date('2019-10-21', 'YYYY-MM-DD')
)
;
select * from table(dbms_xplan.display(null,null,'outline'));
In the ANSI example I happen to have used the explicit “OR” list for the date predicates but that’s not really signficant . Here’s the plan produced by this query – and the first thing I’d like you to note is the Plan hash value:
Is the plan for the “ANSI” version of the query the same as the plan for the “traditional” version? How carefully have you checked – after simply noting that the two Plan hash values were different.
The plans are the same – in that the mechanics are exactly the same and that ought to be the criterion on which we should judge them. But the hash values are different because of the change from traditional to ANSI syntax. The traditional form of the query starts life with a single query block while the ANSI form is considered to be two query blocks, so the initial ANSI query goes through three stages:
1) select from t1 join t2 join t3
2) select /*+ qb_name(sel$2) */ from (select /* qb_name(sel$1) */ from t1, t2), t3
3) select /*+ qb_name(sel$9E43CB6E) */ from t1, t2, t3
So the query is rapidly transformed to the equivalent traditional syntax but we now have a query block name of SEL$9E43CB6E instead of SEL$1 that the traditional query (in the absence of a /*+ qb_name() */ hint would have had. This is why you see the difference in the two or_expand() hints in the Outline Data section. One reads: OR_EXPAND(@SEL$1 (1) (2) (3)), the other reads OR_EXPAND(@”SEL$9E43CB6E” (1) (2) (3)), and all the subseqent query block name differences follow on from this initial transformation. (Note: the value “sel$9e43cb6e” is derived from the input query block names of sel$1 and sel$2 that the new query block is derived from)
You may decide that this mismatch isn’t really terribly important. If you’ve modified the code to switch to ANSI style joins then you may be prepared to put in a little extra effort to check the plan in detail to see that it hasn’t changed; but it would be a lot nicer if the hash value wasn’t dependent on generated view names. You may recall that at one time Oracle had problems with plans that used materialized CTEs (“with” subqueries) because the plan hash value was affected by object names like sys_temp_0fd9d6791_dfc12da. The same principle ought, I think, to apply here.
If you don’t mind the ANSI/tradiational switch though, you might find that you’re less happy when you upgrade to 19c, because the same effect appears there too, only it’s worse. Not only do “identical” traditional and ANSI plans have different hash values, they don’t match the values from 12c because the generated name for the join factorization views (VW_JF) change in the upgrade. So if you’re depending on SQL Plan Baselines to reproduce 12c plans on 19c when you upgrade you may find cases where you know the stored baseline is giving you the same plan but Oracle thinks it isn’t and refuses to use it.
tl;dr
Plans which are functionally identical can have different plan hash values because the plans were reached through a different series of tranformations. In particular if you rewrite queries from “traditional” Oracle syntax to “ANSI” syntax you will find cases where the plan doesn’t change but the plan hash value does thanks to a change in the names of views generated by some transformations.
More significantly, if you upgrade from 12c to 19c there are case where the names of views generated by transformations may change, which could cause the optimizer to discard some of your carefully constructed SQL Plan Baselines as “not reproducible”.
Footnote
For reference, if I add the hint /*+ opt_param(‘_optimizer_join_factorization’ ‘false’) */ to the “traditional query then I get the following plan which shows more clearly the three branches that the original query has been split into – each section starting with a different choice for the driving table:
Although the “traditional” and “ANSI” plans still show a difference in their Plan hash values when join factorization is blocked, the absence of the join factorization view means that the plan hash values are now consistent between 12c to 19c (the output above came from 19.3.0.0 as you can see in the Outline information).
Filed under: CBO,Hints,Oracle — Jonathan Lewis @ 10:10 am BST Oct 4,2019
This is just a list of the notes I’ve written about the opt_estimate() hint.
opt_estimate – using the hint to affect index calculations: index_scan and index_filter
opt_estimate 2 – applying the hint to nested loop joins, options: nlj_index_scan and nlj_index_filter
opt_estimate 3 – a couple of little-known options for the hint, “group_by” and “having”.
opt_estimate 4 – applying the hint at the query block level: particularly useful for CTEs (“with subquery”) and non-mergeable views.
opt_estimate 5 – a story of failure: trying to use opt_estimate to push predicates into a union all view.
I have a couple more drafts on the topic awaiting completion, but if you know of any other articles that would be a good addition to the list feel free to reference them in the comments.
And here’s a start on other articles about the opt_estimate() and related hints
table_stats() – A note from Roger McNicol on the correct syntax and use of the table_stats() hint.
