optimizer_index_cost_adj and hints ...

Hi all !
I've encountered a very strange problem on a very simple query...

Here is the query :
select * from bigtable where col1='XXX'

The col1 columns is indexed (with Ã* bitmap index because of the low cardinality of the column).

Both the table and index are freshly analyzed.

test1 :
When I try to explain plan, I realize that the CBO make an access full to bigtable table and it takes 1 minute to execute.

test2 :
When I add a INDEX hint (select /*+ INDEX (bigtable idx_col1) */ ...), the index is used and it takes less than 1 second to execute !

test3 :
When I set the optimizer_index_cost_adj to a low value than the default one (from 60 to 1), the explain plan shows that the index is well used, BUT the query takes one minute to execute ...

1) With exactly the same execution plan (test2 and 3) but different cost, we obtain different response time. How could it be possible if the plans are the same ?
2) Why the CBO does not choose the good plan itself (test2), whereas it's a very simple query ...

Thanks a lot in advance for your replies ...

Which version of Oracle you are using? There are some bugs in 8i (8.1.7) regarding using bitmap indexes. Check metalink for details. There are some events you can use to find out more about optimizer plan selection.

It is the 8.1.7 release ... on Solaris 8.

Any help ?

Hi,

What is the data type off col1?

Is this table analyzed with dbms_stats?

What is the optimizer_mode for the session?

tycho

The datatype of column col1 is char(2).
The table is analyzed with ANALYZE statement, not dbms_stats package.
The optimizer mode is CHOOSE for the session and for the instance.

Quote:

---

Originally posted by htanga
2) Why the CBO does not choose the good plan itself (test2), whereas it's a very simple query ...

---

I think the problem is low cardinality, which tends to mean that a condition on that column is not very selective. If (to take an extreme case) 1% have col1='XXX' and 99% have col1='YYY' (which is a bit odd if it is CHAR(2)!) then the select for 'XXX' works best by index, 'YYY' by full table scan (probably).
(I have no answer for Q1)

Quote:

---

Originally posted by DaPi
I think the problem is low cardinality, which tends to mean that a condition on that column is not very selective. If (to take an extreme case) 1% have col1='XXX' and 99% have col1='YYY' (which is a bit odd if it is CHAR(2)!) then the select for 'XXX' works best by index, 'YYY' by full table scan (probably).
(I have no answer for Q1)

---

Unfortunatly the low cardinality is aparently not the problem ...
The predicate select about 5% of the table's rows.

(The source is Guy Harrison's book, interpreted by me)
Unless you have generated a "Histogram" on the relevent column (most people don't bother), the only statistic the CBO has to work with is the number of distinct keys. In my example there are two, so unless the CBO knows the distribution of values from the histogram, it must guess at a 50:50 distibution. If you are retrieving 50% of the rows (usually) FTS is best.

"They" say that FTS wins when you are retrieving more than 10-20% of the table - this suggests to me that without histograms the CBO will behave as you see it, if the number of distinct keys does not exceed (say) 10.

This is typical with Yes/No flags - I have 300 employees buried in a table of 100'000 persons - I index on the flag and use an employee view with a hint to ensure the index is used in this case.

Hi,

It looks like an optimizer problem.
can you do 'alter session set optimizer_mode = first_rows;'
and execute the query.
First_rows optimization is an index junky.

I have once seen problems because the parallel degree was set to default and pq was used instead of an index.

select table_name, degree
from dba_tables
where table_name = '&name'
/

If the degree <> 1 this could be an answer to your questions.

tycho

This is very clear !!!
Thanks a lot four helpfull answer ...
I analyze table with "for columns col1 size 4" and the CBO use the index.
But does someone has an answer for the first question ?

"With exactly the same execution plan (test2 and 3) but different cost, we obtain different response time. How could it be possible if the plans are the same ?"

Thanks in advance ...

because you generated histograms so it uses index now

when you change optimizer_* parameters you change the way CBO calculate the costs that´s why you see different costs however resposne time shouldnt be different if plan is the same

Quote:

---

Originally posted by htanga
"With exactly the same execution plan (test2 and 3) but different cost, we obtain different response time. How could it be possible if the plans are the same ?"

---

htanga, could you "set autotrace on exp stat" and post the analysis (not the data) for the two cases so that we can look at it.

Why and How do we get different cost values for a single statement.
There are 2 important parameters most DBAs underutilzed.
One is OPTIMIZER_INDEX_CACHING and the other OPTIMIZER_INDEX_COST_ADJ.

The default value for OPTIMIZER_INDEX_CACHING is 0, that indicates to the CBO that 0 % of database blocks accessed using indexed access can be expected to be found in the Bffer Cache of the SGA. This implies that all index access will require a physical read from the I/O subsystem for every logical read from the buffer cache.

The default value of 100 ( for OPTIMIZER_INDEX_COST_ADJ) indicates to the CBO that indexed access is 100 % costly (i.e equally costly) as Full Table Scan access.

These default values are wildly unsuitable and unrealistic.

I would suggest change OPTIMIZER_INDEX_CACHING value to 90 and OPTIMIZER_INDEX_COST_ADJ to 10 or 20.

These 2 parameters can be changed at session level.

Try to play with these 2 parameters and run explain plan for a SQL statement.

With the reduced value for OPTIMIZER_INDEX_COST_ADJ, now you can see your optimizer will choose correct index. More over, you would see the total cost for the SQL will go down.

Oracle calculates the cost based on the following formual:
CALCULATED_LOGICAL_READS * (1 - (OPTIMIZER_INDEX_CACHING/100)) = CALCULATED_PHY_READS = COST.

So with default value 0 for OPTIMIZER_INDEX_CACHING, the above formual does not make any sense. Hence, set it to 90 or 95.