I have this query:
SELECT /*+ index(a MY_INDEX_N1) */ COUNT(*) FROM MY_TABLE a WHERE
a.VARCHAR_COLUMN_1 = :B2 AND a.NUMBER_COLUMN_2 = :B1
The NUMBER_COLUMN_2 is NOT NULL in the table.
The index in the hint “MY_INDEX_N1” doesn’t exist. (Looks like it was dropped
by the application vendor in a patch and no one noticed). And yes, we know we
shouldn’t use hints like this. No one realized the developer did this until a
recent problem. We’ll fix that… it’s not my question though.
Anyway, there are two other indexes on the table:
MY_INDEX_N17 ( NUMBER_COLUMN_2, VARCHAR_COLUMN1, SOME_OTHER_NUMBER_COLUMN98
)MY_INDEX_N10 ( NUMBER_COLUMN_2, OTHER_VARCHAR_COLUMN99 )
This query recently became a performance problem and what I think happened is
this:
* The query was submitted with bind variable :B1 NULL. (I know this part for
sure)* Oracle thought “oh, the condition NUMBER_COLUMN_2 = NULL will never be
true, it does not matter what index I choose. I will choose MY_INDEX_N10
because it is smaller”.* It blows through the first 10-15 calls to this query
where :B1 is null and then get into the 5-10 _thousand_ records to process
where it is not null. Now, suddenly, that access plan is a dog. So, questions:
* Why wouldn’t Oracle choose the slightly bigger MY_INDEX_N17 to hedge it’s
bet, since that index’s 2nd leading column is also used in the query?* Why
wouldn’t Oracle (quickly? Eventually?) realize that this query is bind
sensitive and reparse it? I see in AWR that it ran with this bad plan 68 times
over the course of about 16 hours). Thanks in advance for any insights from the
experts who dwell here!
Matt
