optimizer wont use indexes

[tuvham]

I have a very annoying problem with indexes that perhaps someone out there can help me with?

I have large fact table (3700 mb) with a lot of bitmap indexes and one pk index on it.
Its a totally plain table but with some chained rows (583349 of 17446252).
The primary key b-tree index has columns (in order) a,b,c.
A bitmap index has column a indexed.
The sql statement is: select b,a,d,c,f from x where a = 87;
Value "a" ranges from 1 to 150 and this particular statement returns 0 rows.
Optimizer mode is CHOOSE but oracle does a full table scan and ignores the index.
The table and all indexes is analyzed compute. It always work when i use index hints in the statement,
then the response time drops from 2-4 minutes to a second.

I´ve tried this:
1. Since we already got column a indexed in the pk index, why don´t oracle use that?
1a. tried to "select b,a from x where a = 87;". It works! Oracle uses the pk index.
1b. tried to "select a,b from x where a = 87;". It works! Oracle uses the pk index.
1c. tried to "select b,a,d from x where a = 87;". It works! Oracle uses the pk index.
1d. tried to "select b,a,d,e from x where a = 87;". It doesn´t work! Oracle ignores the pk index.

2. Adding histograms to column a since the distribution is very skewed.
This actually works! The optimizer uses the bitmap index on column a after adding histogram.
BUT we use bindvariables so then we cant use histograms.

3. Lets try optimizer_index_cost_adj and optimizer_index_caching
cost_adj set to 30, index_caching set to 95
What happens is that the optimizer uses the pk index but somehow it does a fts on the table anyway:
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=39495 Card=1586598 B
ytes=26972166)

1 0 TABLE ACCESS (BY INDEX ROWID) OF 'X' (Cost=3949
5 Card=1586598 Bytes=26972166)

2 1 INDEX (RANGE SCAN) OF 'PK_X' (UNIQUE) (Cost=8

6 db block gets
471931 consistent gets
465812 physical reads

4. Lets try to create a b-tree index on column a (with compute statistics in create statement)
The optimzer doesn´t use it!
4a. Computes statistics (compute) for the new index and the table. Doesn´t work, fts.
4b. Changes optimizer_index_cost_adj to 30. Works (uses the new index), but does read through the whole table anyway.
4c. Tries to hint the new index in the sql statement. It works perfectly, 3 consistent reads, no phys.
4d. Tries to hint the pk index in the sql statement. It works fine, 26 consistent reads, 3 phys.
4e. Creates histograms for column a. It works, 3 cons gets, no phys. But i cant use this because of bind variables.
4f. Drops the new index because it's really not needed, we´ve got indexing in the pk. It still works, 553 consistent gets, 13 phys reads.
4g. Creates the new index again, this time with comress. Doesn´t work! FTS.
4h. Tries to alter the sqlstatement (where a = 56), maybe oracle uses some old execution plan? Doesn´t work, fts.
4i. Tries optimizer_index_cost_adj again. Same as before, reads whole table.
4j. Tries to hint again. Works just fine, uses the new index, 21 cons reads, 5 phys.


SUMMARY: whenever i use hints and histograms everything is fine.
But I want the optimizer to do this work for me,
we don´t want to use hints and since we use bind variables, histograms don´t work.
Why does´nt the optimizer use the indexes?

[Zamial]

When was the last time you analyzed the table.


Plus the optimizer favours the CBO, I had a very similiar problem and hints solved the speed issue.

Analyzing the table was the overall solution though. After I analyzed the table Oracle actually selected to use the RBO. Without me specifying hints.


I did a complete table analzye including all indexes and computed the statistics.


Try it.

[tuvham]

I have tried that, see number 4.
I´ve talked with oracle now and they say: Use hints or histograms.
So i guess we´ll have to put a hint in there.

/Tuve

[tamilselvan]

Tuvham,

You did excellent work in tuning your query. I found many times CBO did not choose the fastest execution plan even though the table was analyzed and right indexes were available. Oracle has to rewrite its software particulary when working with CBO.

Do not assume CBO always choose the fastest path. It is better to use hint in the SQL statement rather than wasting time with CBO.