There are a number of unexpected issues with the optimizer’s treatment of the index fast full scan, the access path where Oracle ignores the structure of the B-tree and uses multiblock reads to do a brute-force segment scan as if the index were a “skinny table” with a few blocks of irrelevant garbage (i.e. the branch blocks) that could be ignored.
A recent comment made a few days on a blog about the optimizer’s “index-join” access path reminded me that I had a few notes to finish and publish that might help some people address performance issues relating to a couple of little-known fixes for optimizer oddities in this area. Today’s “fix” is one that appeared in 10g but never became the default optimizer behaviour (possibly because it’s one of those “not a bug” types of bug with no “always correct” strategy).
The problem revolves around the optimizer_index_cost_adj parameter (which is another reason why the fix might have been kept out of the standard code base). For index-only access paths the parameter will apply to the index range scan, full scan, or skip scan, but not to the index fast full scan. Here’s a model to demonstrate this:
rem
rem Script: oica_iffs.sql
rem Author: Jonathan Lewis
rem Dated: Nov 2012
rem
rem Last tested
rem 23.3.0.0
rem
create table t1
as
with generator as (
select --+ materialize
rownum id
from dual
connect by
level <= 1e4 -- > comment to avoid wordpress format issue
)
select
lpad(trunc(dbms_random.value(0,10000)),10) v1,
rpad('x',20) padding
from
generator v1,
generator v2
where
rownum <= 1e6 -- > comment to avoid wordpress format issue
;
create index t1_i1 on t1(v1);
begin
dbms_stats.gather_table_stats(
ownname => user,
tabname =>'T1',
cascade => true,
method_opt => 'for all columns size 1'
);
end;
.
select blocks from user_tables where table_name = 'T1';
select leaf_blocks from user_indexes where index_name = 'T1_I1';
I’ve created a fairly narrow table with 1M rows, and created an index on that table on the v1 column. In my test there were 5,219 blocks in the table and 3,068 blocks in the index.
I’m now going to run a query to count the rows in the table where v1 is not null and report its execution plan. The first run will be unhinted, the second run will include a hint that forces an index full scan, and the third run will include a hint to force an index fast full scan.
prompt ==============
prompt Unhinted query
prompt ==============
select
count(*)
from
t1
where
v1 is not null
;
prompt ==========
prompt Index hint
prompt ==========
select
/*+ index(t1) */
count(*)
from
t1
where
v1 is not null
;
prompt ==============
prompt index_ffs hint
prompt ==============
select
/*+ index_ffs(t1) */
count(*)
from
t1
where
v1 is not null
;
Here are the three execution plans. As you might have predicted the default plan (for my Oracle 19.3) is the index fast full scan:
==============
Unhinted query
==============
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 11 | 450 (15)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 11 | | |
|* 2 | INDEX FAST FULL SCAN| T1_I1 | 1000K| 10M| 450 (15)| 00:00:01 |
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("V1" IS NOT NULL)
==========
Index hint
==========
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 11 | 3159 (3)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 11 | | |
|* 2 | INDEX FULL SCAN| T1_I1 | 1000K| 10M| 3159 (3)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("V1" IS NOT NULL)
==============
index_ffs hint
==============
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 11 | 450 (15)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 11 | | |
|* 2 | INDEX FAST FULL SCAN| T1_I1 | 1000K| 10M| 450 (15)| 00:00:01 |
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("V1" IS NOT NULL)
Since the size of the index is less than the size of the table the cost of the index fast full scan will be lower than the cost of a tablescan; and since an index fast full scan does multiblock reads while the index full scan is assumed to use single block reads the cost of the index fast full scan will be lower than the cost of the index full scan.
Now we set the optimizer_index_cost_adj to 1 (as happens in a number of “legacy” commercial products) and repeat the experiment. Here are the three plans (reduced to just the body of the plan):
================== OICA = 1, Unhinted ================== -------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 11 | 32 (4)| 00:00:01 | | 1 | SORT AGGREGATE | | 1 | 11 | | | |* 2 | INDEX FULL SCAN| T1_I1 | 1000K| 10M| 32 (4)| 00:00:01 | -------------------------------------------------------------------------- ==================== OICA = 1, index hint ==================== -------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 11 | 32 (4)| 00:00:01 | | 1 | SORT AGGREGATE | | 1 | 11 | | | |* 2 | INDEX FULL SCAN| T1_I1 | 1000K| 10M| 32 (4)| 00:00:01 | -------------------------------------------------------------------------- ======================== OICA = 1, index_ffs hint ======================== ------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 11 | 450 (15)| 00:00:01 | | 1 | SORT AGGREGATE | | 1 | 11 | | | |* 2 | INDEX FAST FULL SCAN| T1_I1 | 1000K| 10M| 450 (15)| 00:00:01 | -------------------------------------------------------------------------------
The default plan is now the index full scan, not the index fast full scan; and when we look at the two hinted plans we can see why. The full scan cost has been reduced to 1% of the original cost (as dictated by the setting for optimizer_index_cost_adj); the fast full scan cost has not been adjusted.
So is this a good thing or a bad thing? Different people may answer that question differently. There are clearly likely to be cases where changing the setting for the optimizer_index_cost_adj (which you shouldn’t have been doing anyway for the last 20 years or so) will result in some plans switching from fast full scans to full scans with unfortunate consequences;
On the other hand when you do a fast full scan it may do direct path reads, while the full scan will do cached reads, and the cache may be holding a lot of the index leaf blocks already, so some people might prefer the current behaviour;
On the other, other hand an Exadata system can use storage indexes when doing direct path segment scans (even when the segment is an index) so an Exadata fast full scan might be far more desirable than a partially cached full scan.
So no generic right answer, only specific answers for particular sets of circumstances.
Solutions
This issue was addressed some time in the 10g timeline- my notes say the earliest successful tests I did of the fix were on 10.2.0.4, and that it wasn’t available in 9.2.0.8. There are two possibilities – a fix control, and an event.
- Fix control: 4483286 – described as “Discount FFS cost using optimizer_index_cost_adj”
- Event: 38085 at level one. I’ve never seen this event in the oraus.msg file, but it is referenced by bug 4483286
Of the two, setting the fix control is probably the safer option since the event is (apparently) non-existent and there’s always the possibility that the number will be hi-jacked in a future release to do something completely different. It’s also worth noting that the fix control can be applied to an individual statement through the /*+ opt_param() */ hint (there’s an example of the syntax towards the end of another article of mine).
A little demonstration:
-- alter session set events '38085 trace name context forever, level 1';
alter session set "_fix_control"='4483286:1';
alter session set optimizer_index_cost_adj = 1;
set autotrace traceonly explain
prompt ==============
prompt Unhinted query
prompt ==============
select
count(*)
from
t1
where
v1 is not null
;
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 11 | 4 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 11 | | |
|* 2 | INDEX FAST FULL SCAN| T1_I1 | 1000K| 10M| 4 (0)| 00:00:01 |
-------------------------------------------------------------------------------
With the event or _fix_control set the cost of the index fast full scan drops to 1%, and the path reappears without being hinted.
Oracle Scratchpad 
Comments and related questions are welcome.