RE: Very Strange Query Access Plan
- From: "Alvaro Jose Fernandez" <alvaro.fernandez@xxxxxxxxx>
- To: <Thomas.Mercadante@xxxxxxxxxxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 2 Oct 2007 17:29:54 +0200
Hi Tom,
Yesterday, queries against this table used the index. Today, they do
not. There was no large data load overnight. We add about 10,000
records per day. This behavior has happened before where the index gets
ignored for a day and then used the next day.
I really need to use an index here. Queries via ssn are executed all
day long and take 10 minutes to complete. CPU is pegged at 99%.
What am I missing? Maybe histograms? Haven't used them before, so any
suggestions would be helpful.
Maybe you would not need to use histograms.
If you don't mind sending a 10053 trace excerpt for the query (maybe
using a simple test query using ssn as the only predicate), showing the
table & index statistics , and the cost assigned to each index, it would
help to see what's going on. It appears a not very appealing index for
the optimizer (625,000 out of 18 million and many nulled rows).
In the extreme case you can also workaround this situation making this
index's statistics more attractive to the optimizer, using
dbms_stats.set_index_stats . But it's worthy take a look before at an
10053 trace , though.
alvaro
Other related posts:
