so setting this work: OPTIMIZER_INDEX_COST_ADJ - now what ?
Oracle 9.2.0.2.0 on TRU64 Unix 5.1a
I have a query that I've been trying to tune. It joins 2 tables (one of them range partitioned), and those 2 tables have the approriate indicies.
The explain plan shows that it is doing a full scan of both the tables, so in DEV, I rebuild the indicies and gathered the stats on tables/local partitions/indicies/global stats. The query improved drastically, and the explain plan showed that it was using the indicies, and life was great ...
...UNTIL, I did the same to PROD (meaning rebuild indicies/gather stats - same way), but the query is still doing full scans of both the tables ! So I try to HINT an index, but CBO does not use it. So I try playing with OPTIMIZER_INDEX_COST_ADJ, the value was 100. When I alter my session to change it to 20, the CBO starts using the indicies for both the tables, and the response time goes from 1 min 16 secs to 23 secs - great.
So now what, I don't want to alter the system, cause who know how everything else will be affected ? But I can't ask the developers to do an alter session for every time they run the query either !
Any suggestions ?
Re: so setting this work: OPTIMIZER_INDEX_COST_ADJ - now what ?
Quote:
Originally posted by khussain
So I try to HINT an index, but CBO does not use it. So I try playing with OPTIMIZER_INDEX_COST_ADJ, the value was 100. When I alter my session to change it to 20, the CBO starts using the indicies for both the tables, and the response time goes from 1 min 16 secs to 23 secs - great.
First of all having high value for OPTIMIZER_INDEX_COST_ADJ will not favour Index usage !!..
Well its not advisible to decrease the Val before thorough testing..
But you can try Rule hint to see if the plan is good..before you jump into testing for changing the Val of OPTIMIZER_INDEX_COST_ADJ...
Abhay.
Re: Re: so setting this work: OPTIMIZER_INDEX_COST_ADJ - now what ?
Quote:
Originally posted by abhaysk
But you can try Rule hint to see if the plan is good..before you jump into testing for changing the Val of OPTIMIZER_INDEX_COST_ADJ...