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 ?