Symptom
- The apparently correct index, according to the developers, was not being used.
Cause
- Index selectivity calculation differed depending on execution plan.
Solution
- Create a baseline.
Investigation
A problem was reported with a particular SQL statement that was using the wrong index on production. UAT did not have the “correct” index so I could not gain any insights from this. The SQL statement was very long as was the execution plan, but the important section can be summarised as follows:
select *
from <table 1> esm
where esm.<id column> = hextoraw(:b1)
and esm.<field A> IN (
SELECT gro.<field B>
FROM <table 2> gro
WHERE gro.<id column> = HEXTORAW(:b2)
)
AND EXISTS (
SELECT 1
FROM <table 3> net ,
<table 4> ngp,
<view 1> sngp
WHERE net.<field C> = esm.<field D>
AND net.<id field> = ngp.<field with FK to NET id field>
AND ngp.<id field> = sngp.<field with FK to NGP id field>
AND sngp.<id column> = HEXTORAW(:b3)
)The “correct” index on <table 1> according to the developers was on (<id column>,<field A>) and was a global index, but the query was using an index on (<id column>,<field D>) and was a local index. Although some of the time the query ran in seconds it also took tens of minutes at other times. I obtained the bind variables from one of the SQL runs via SQL Monitor and replaced the bind variables with the literal values. This produced an explain plan using the “correct” index and ran in under a second. Clearly then the problem probably related to bind variables. The explain plan for the above query is:
--------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 1854 | 544 (1)| 00:00:01 | | |
|* 1 | HASH JOIN | | 1 | 1854 | 503 (1)| 00:00:01 | | |
|* 2 | TABLE ACCESS BY INDEX ROWID BATCHED | <table 2> | 2 | 54 | 4 (0)| 00:00:01 | | |
|* 3 | INDEX RANGE SCAN | <table 2 index> | 3 | | 3 (0)| 00:00:01 | | |
| 4 | PARTITION LIST SINGLE | | 758 | 1352K| 499 (1)| 00:00:01 | KEY | KEY |
| 5 | TABLE ACCESS BY LOCAL INDEX ROWID BATCHED| <table 1> | 758 | 1352K| 499 (1)| 00:00:01 | KEY | KEY |
|* 6 | INDEX RANGE SCAN | <incorrect index> | 758 | | 85 (0)| 00:00:01 | KEY | KEY |
| 7 | NESTED LOOPS SEMI | | 1 | 103 | 41 (0)| 00:00:01 | | |
| 8 | NESTED LOOPS | | 18 | 1242 | 5 (0)| 00:00:01 | | |
| 9 | TABLE ACCESS BY INDEX ROWID | <table 3> | 1 | 35 | 1 (0)| 00:00:01 | | |
|* 10 | INDEX UNIQUE SCAN | <table 3 index> | 1 | | 0 (0)| 00:00:01 | | |
| 11 | TABLE ACCESS BY INDEX ROWID BATCHED | <table from view 1> | 18 | 612 | 4 (0)| 00:00:01 | | |
|* 12 | INDEX RANGE SCAN | <view table index> | 18 | | 2 (0)| 00:00:01 | | |
|* 13 | TABLE ACCESS BY INDEX ROWID | <table 4> | 3187K| 103M| 2 (0)| 00:00:01 | | |
|* 14 | INDEX UNIQUE SCAN | <table 4 index> | 1 | | 1 (0)| 00:00:01 | | |
--------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("ESM"."<field A>"="GRO"."<field B>")
2 - filter("GRO"."<field B>" IS NOT NULL)
3 - access("GRO"."<id column>"=HEXTORAW(:B2))
6 - access("ESM"."<id column>"=HEXTORAW(:B1))
filter( EXISTS (SELECT 0 FROM "<view 1>" "SNGP","<table 4>"
"NGP","<table 3>" "NET" WHERE "NET"."<field C>"=:B1 AND "NGP"."<id column>"="SNGP"."<field with FK to NGP id field>" AND
"NET"."<id column>"="NGP"."<field with FK to NET id field>" AND "SNGP"."<id column>"=HEXTORAW(:B3)))
10 - access("NET"."<field C>"=:B1)
12 - access("SNGP"."<id column>"=HEXTORAW(:B3))
13 - filter("NET"."<id column>"="NGP"."<field with FK to NET id field>")
14 - access("NGP"."<id column>"="SNGP"."<field with FK to NGP id field>")If it is hinted to use the “correct” index, the explain plan is:
---------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
---------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 1854 | 855 (1)| 00:00:01 | | |
|* 1 | FILTER | | | | | | | |
| 2 | NESTED LOOPS | | 1 | 1854 | 814 (1)| 00:00:01 | | |
| 3 | NESTED LOOPS | | 1948 | 1854 | 814 (1)| 00:00:01 | | |
|* 4 | TABLE ACCESS BY INDEX ROWID BATCHED| <table 2> | 2 | 54 | 4 (0)| 00:00:01 | | |
|* 5 | INDEX RANGE SCAN | <table 2 index> | 3 | | 3 (0)| 00:00:01 | | |
|* 6 | INDEX RANGE SCAN | <correct index> | 974 | | 5 (0)| 00:00:01 | | |
| 7 | TABLE ACCESS BY GLOBAL INDEX ROWID | <table 1> | 1 | 1827 | 805 (1)| 00:00:01 | ROWID | ROWID |
| 8 | NESTED LOOPS SEMI | | 1 | 103 | 41 (0)| 00:00:01 | | |
| 9 | NESTED LOOPS | | 18 | 1242 | 5 (0)| 00:00:01 | | |
| 10 | TABLE ACCESS BY INDEX ROWID | <table 3> | 1 | 35 | 1 (0)| 00:00:01 | | |
|* 11 | INDEX UNIQUE SCAN | <table 3 index> | 1 | | 0 (0)| 00:00:01 | | |
| 12 | TABLE ACCESS BY INDEX ROWID BATCHED| <table from view 1> | 18 | 612 | 4 (0)| 00:00:01 | | |
|* 13 | INDEX RANGE SCAN | <view table index> | 18 | | 2 (0)| 00:00:01 | | |
|* 14 | TABLE ACCESS BY INDEX ROWID | <table 4> | 3187K| 103M| 2 (0)| 00:00:01 | | |
|* 15 | INDEX UNIQUE SCAN | <table 4 index> | 1 | | 1 (0)| 00:00:01 | | |
---------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter( EXISTS (SELECT 0 FROM "<table from view 1> _VW"
"SNGP","<table 4>" "NGP","<table 3>" "NET" WHERE "NET"."<field C>"=:B1 AND
"NGP"."<id column>"="SNGP"."<field with FK to NGP id field>" AND "NET"."<id column>"="NGP"."<field with FK to NET id field>" AND "SNGP"."<id column>"=HEXTORAW(:B3)))
4 - filter("GRO"."<field B>" IS NOT NULL)
5 - access("GRO"."<id column>"=HEXTORAW(:B2))
6 - access("ESM"."<id column>"=HEXTORAW(:B1) AND "ESM"."<field A>"="GRO"."<field B>")
11 - access("NET"."<field C>"=:B1)
13 - access("SNGP"."<id column>"=HEXTORAW(:B3))
14 - filter("NET"."<id column>"="NGP"."<field with FK to NET id field>")
15 - access("NGP"."<id column>"="SNGP"."<field with FK to NGP id field>")The explain plans costs are not too dissimilar but the execution times can be very different for some bind variables. A clue as to the reasons for this can be found in the explain plan when the bind variable for esm.<id column> (esm is the alias for <table 1>) is replaced with the appropriate literal from one of the long executions:
---------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
---------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 5016 | 2455 (1)| 00:00:01 | | |
|* 1 | FILTER | | | | | | | |
| 2 | NESTED LOOPS | | 105 | 171K| 814 (1)| 00:00:01 | | |
| 3 | NESTED LOOPS | | 1948 | 171K| 814 (1)| 00:00:01 | | |
|* 4 | TABLE ACCESS BY INDEX ROWID BATCHED| <table 2> | 2 | 54 | 4 (0)| 00:00:01 | | |
|* 5 | INDEX RANGE SCAN | <table 2 index> | 3 | | 3 (0)| 00:00:01 | | |
|* 6 | INDEX RANGE SCAN | <correct index> | 974 | | 5 (0)| 00:00:01 | | |
| 7 | TABLE ACCESS BY GLOBAL INDEX ROWID | <table 1> | 59 | 97055 | 805 (1)| 00:00:01 | 348 | 348 |
| 8 | NESTED LOOPS SEMI | | 1 | 103 | 41 (0)| 00:00:01 | | |
| 9 | NESTED LOOPS | | 18 | 1242 | 5 (0)| 00:00:01 | | |
| 10 | TABLE ACCESS BY INDEX ROWID | <table 3> | 1 | 35 | 1 (0)| 00:00:01 | | |
|* 11 | INDEX UNIQUE SCAN | <table 3 index> | 1 | | 0 (0)| 00:00:01 | | |
| 12 | TABLE ACCESS BY INDEX ROWID BATCHED| <table from view 1> | 18 | 612 | 4 (0)| 00:00:01 | | |
|* 13 | INDEX RANGE SCAN | <view table index> | 18 | | 2 (0)| 00:00:01 | | |
|* 14 | TABLE ACCESS BY INDEX ROWID | <table 4> | 3187K| 103M| 2 (0)| 00:00:01 | | |
|* 15 | INDEX UNIQUE SCAN | <table 4 index> | 1 | | 1 (0)| 00:00:01 | | |
---------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter( EXISTS (SELECT 0 FROM "<table from view 1> _VW"
"SNGP","<table 4>" "NGP","<table 3>" "NET" WHERE "NET"."<field C>"=:B1 AND
"NGP"."<id column>"="SNGP"."<field with FK to NGP id field>" AND "NET"."<id column>"="NGP"."<field with FK to NET id field>" AND "SNGP"."<id column>"=HEXTORAW(:B4)))
4 - filter("GRO"."<field B>" IS NOT NULL)
5 - access("GRO"."<id column>"=HEXTORAW(:B2))
6 - access("ESM"."<id column>"=HEXTORAW('<hex string>') AND "ESM"."<field A>"="GRO"."<field B>")
11 - access("NET"."<field C>"=:B1)
13 - access("SNGP"."<id column>"=HEXTORAW(:B4))
14 - filter("NET"."<id column>"="NGP"."<field with FK to NET id field>")
15 - access("NGP"."<id column>"="SNGP"."<field with FK to NGP id field>")
In this case 59 rows instead of 1 are being returned from each loop around <table 1> and a specific partition is referenced. A quick look at the relevant statistics showed why there was such a significant difference:
select num_rows
from dba_tables
where table_name = '<table 1>';
NUM_ROWS
----------
229136956
select num_distinct
from dba_tab_col_statistics
where table_name = '<table 1>' and
column_name = '<id column>';
NUM_DISTINCT
------------
15112
select NUM_DISTINCT, NUM_NULLS, SAMPLE_SIZE
from dba_part_col_statistics
where table_name = '<table 1>'
and column_name = '<id column>'
and partition_name = (
select partition_name
from dba_tab_partitions
where partition_position = 348
and table_name = '<table 1>' );
NUM_DISTINCT NUM_NULLS SAMPLE_SIZE
------------ ---------- -----------
1 0 9184979The partition statistics are quite different from the global statistics. The table is partitioned by LIST on <id column> and there are 508 partitions, 507 with a single <id column> value and 1 for all the rest:
select NUM_DISTINCT, count(*)
from dba_part_col_statistics
where table_name = '<table 1>'
and column_name = '<id column>'
group by NUM_DISTINCT;
NUM_DISTINCT COUNT(*)
------------ ----------
1 507
14636 1Clearly then accessing one of the 507 single <id column> value partitions is going to produce a very different number of rows from accessing the one partition containing 14636 different <id column> values. The relevant steps in the execution plans will therefore be as follows.
Version 1:
- Get the two rows from GRO where the <id column> equals the bind variable.
- Access the correct index of ESM where <id column> equals the bind variable and <field A> matches <field B> from the GRO rows.
- Access the ESM table to get the rows.
- Filter the returned rows using the EXISTS.
Version 2:
- Get the two rows from GRO where the <id column> equals the bind variable and build a hash table.
- Access the incorrect index of ESM where <id column> equals the bind variable.
- Filter the returned rows from ESM using the EXISTS.
- Access the ESM table to get the rows.
- Probe the hash table with the filtered rows based on
ESM.<field A> matches GRO.<field B>.
Given the small number of rows returned by the first step in both versions according to the optimiser and the rather high selectivity of ESM.<field A> it seems obvious that version 1 is going to be faster as we apply the ESM.<field A> matches GRO.<field B> filter immediately. There may be little different between the two if you are accessing the partition with 14636 different values for the <id column>, but if you are accessing one of the partitions with only one value, the difference will be significant. Why then would you ever pick version 2?
This question has a rather complicated answer and comes down to the algorithms used to estimate the returned rows from the two indexes and the fact that the optimiser considers the highest cost element to be the accessing of the ESM table.
As a bind variable is being used it will be peeked at on the first execution and an execution plan generated based on whether it is accessing one of the 507 single <id column> partitions or the one multiple <id column> partition. Both the relevant indexes have a leading column of <id column> and the statistics at the partition and table level are shown below:
*Note that for ease of reading I have replaced the low and high values with their VARCHAR2 values and then obfuscated them somewhat.
select partition_name, column_name, num_distinct, num_nulls, sample_size, low_value, high_value
from dba_part_col_statistics
where table_name = '<table 1>'
and column_name in ( '<field D>','<field A>' )
and partition_name in (
select partition_name
from dba_tab_partitions
where partition_position in ( 508, 348 )
and table_name = '<table 1>' )
order by 1,2;
PARTITION_NAME COLUMN_NAME NUM_DISTINCT NUM_NULLS SAMPLE_SIZE LOW_VALUE* HIGH_VALUE*
------------------------------ -------------------- ------------ ---------- ----------- ------------------------------ ------------------------------
<partition 348> <field A> 9428 2468 9182511 <alphanumeric starting 3000> <alphanumeric starting lll>
<partition 348> <field D> 40 0 9184979 <name string 1> <name string 2>
<partition 508> <field A> 3015 394 3525906 <alphanumeric starting 0000> <alphanumeric starting ZV>
<partition 508> <field D> 66 0 3526300 <name string 1> <name string 2>
select column_name, num_distinct, num_nulls, sample_size, low_value, high_value
from dba_tab_col_statistics
where table_name = '<table 1>'
and column_name in ('<field D>','<field A>' )
order by 1;
COLUMN_NAME NUM_DISTINCT NUM_NULLS SAMPLE_SIZE LOW_VALUE* HIGH_VALUE*
-------------------- ------------ ---------- ----------- ------------------------------ ------------------------------
<field A> 35416 42665 229094291 <alphanumeric starting 0000> <alphanumeric starting lll>
<field D> 68 0 229136956 <name string 1> <name string 2>There may be peeking going on, but this would not seem to be the reason, or the whole reason, for picking one index over another as whether you were looking at a specific partition, identified through peeking, or global stats the selectivity of each index would appear to be similar. However, this is complicated by one index being global and one local. Obviously the <id column> of the ESM table does differ for the different partitions, but then both indexes have this as a leading column. Why therefore is one index chosen over the other? Or, probably more correctly, why would you chose the execution plan that utilises the incorrect index over the execution plan that utilises the correct index given that for the same number of rows the execution plan using the correct index would always seem to be better?
To answer this question we need to look at the explain plans and use hints to see the effects of the different indexes. The relevant section of the execution plan that was used is show below:
| 152 | HASH JOIN | | 1 | 1854 | 503 (1)| 00:00:01 |
| 153 | TABLE ACCESS BY INDEX ROWID BATCHED | <table 2> | 2 | 54 | 4 (0)| 00:00:01 |
| 154 | INDEX RANGE SCAN | <table 2 index> | 3 | | 3 (0)| 00:00:01 |
| 155 | PARTITION LIST SINGLE | | 64 | 114K| 499 (1)| 00:00:01 |
| 156 | TABLE ACCESS BY LOCAL INDEX ROWID BATCHED | <table 1> | 64 | 114K| 499 (1)| 00:00:01 |
| 157 | INDEX RANGE SCAN | <incorrect index> | 758 | | 85 (0)| 00:00:01 |
| 158 | NESTED LOOPS | | 1 | 103 | 41 (0)| 00:00:01 |
| 159 | NESTED LOOPS | | 18 | 103 | 41 (0)| 00:00:01 |
| 160 | NESTED LOOPS | | 18 | 1242 | 5 (0)| 00:00:01 |
| 161 | TABLE ACCESS BY INDEX ROWID | <table 3> | 1 | 35 | 1 (0)| 00:00:01 |
| 162 | INDEX UNIQUE SCAN | <table 3 index> | 1 | | 0 (0)| |
| 163 | TABLE ACCESS BY INDEX ROWID BATCHED | <table from view 1> | 18 | 612 | 4 (0)| 00:00:01 |
| 164 | INDEX RANGE SCAN | <view table index> | 18 | | 2 (0)| 00:00:01 |
| 165 | INDEX UNIQUE SCAN | <table 4 index> | 1 | | 1 (0)| 00:00:01 |
| 166 | TABLE ACCESS BY INDEX ROWID | <table 4> | 1 | 34 | 2 (0)| 00:00:01 | This is the actual execution plan of the original query and so it is slightly different from the explain plans of the summarised query used thus far. The figure of 758 rows returned from the index comes I believe from the statistics of the table, specifically:
select (sample_size/num_distinct)*0.05
from dba_tab_col_statistics
where table_name = '<table 1>'
and column_name = '<id column>';
(SAMPLE_SIZE/NUM_DISTINCT)*0.05
-------------------------------
758.129156The 0.05 multiplier is because Oracle thinks 5% of the rows returned by the index will match the EXIST clause. I am not entirely sure why Oracle is assuming 5%, but 5% is for instance what Oracle assumes for an unbounded range predicate. Given bind variable peeking, I am not sure why the global statistics are being used rather than partition level statistics, but it does appear that SQL plan baselines do something like this and generate a plan from them. Perhaps bind variable peeking was not using the partition level statistics in this case. In a similar situation the “correct” index is assumed to return 974 rows as has been shown previously. These values comes from the index statistics as both columns involved are in the index:
select num_rows/distinct_keys
from dba_indexes
where index_name = '<correct index>';
NUM_ROWS/DISTINCT_KEYS
----------------------
974.056096If we then use these values in the two version of the execution plan we get the following:
Version 1:
- Get the two rows from GRO where the <id column> equals the bind variable. (2 rows)
- Access the correct index of ESM where <id column> equals the bind variable and <field A> matches <field B> from the GRO rows. (974 * 2 = 1948 rows)
- Access the ESM table to get the rows. ( accessed 1948 times )
- Filter the returned rows using the EXISTS.
Version 2:
- Get the two rows from GRO where the <id column> equals the bind variable and build a hash table. (2 rows)
- Access the incorrect index of ESM where <id column> equals the bind variable.
- Filter the returned rows from ESM using the EXISTS. (758 rows)
- Access the ESM table to get the rows. (accessed 758 times)
- Probe the hash table with the filtered rows.
As, according to the optimiser, the most expensive step is the accessing of the ESM table, the execution plan that accesses this the least is most likely to be the cheapest. Due to this and the different algorithms used to calculate how many rows are returned from the index, version 2 appears to be cheaper and so is chosen. From the poor performance encountered, this was clearly not a good choice, but this is why the “incorrect” index was being used.