so setting this work: OPTIMIZER_INDEX_COST_ADJ - now what ?
Oracle 18.104.22.168.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 ?
Click Here to Expand Forum to Full Width