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 ?

11-12-2003, 01:17 PM

marist89

Ah, the classic question. Apply the change to your TEST/QA instance and do a complete system test to see if the change affects your other queries. (Probably will, on the positive side).

11-13-2003, 06:41 AM

abhaysk

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.

11-13-2003, 10:16 AM

slimdave

I do't get the connection between rule and optimizer_index_cost_adj. Explain?

20 is a reasonable value -- the default of 100 is way too high. How about changing it by 5 every day? 100, 95, 90, 85, 80 ... ;)

11-13-2003, 10:57 AM

abhaysk

Quote:

Originally posted by slimdave I do't get the connection between rule and optimizer_index_cost_adj. Explain?

When did i tell there does any connection? :confused: :confused:

11-13-2003, 12:02 PM

slimdave

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...

11-14-2003, 02:22 AM

abhaysk

Does it even sound if I am trying to build a connection between them? :confused: :confused:

Well had i type like

* before you jump into testing for changing the Val of OPTIMIZER_INDEX_COST_ADJ... ( and see how CBO will perform )

* you can try Rule hint to see if the plan is good

then it wouldnt have led to confusion, I guess :p

11-14-2003, 02:26 AM

slimdave

OK, so your not making a connection -- I don't understand what the RBO has got to do with this. What has the optimizer plan under the RBO got to do with this CBO issue?

11-14-2003, 02:36 AM

adewri

Quote:

Originally posted by abhaysk * you can try Rule hint to see if the plan is good

The question is why ? When you are using CBO they why use a RULE hint ?

11-14-2003, 04:47 AM

abhaysk

Ok, if the Q is Why RBO Over CBO, Then IMHO in some situations no matter what ever you do CBO does not choose optimal Access Path where as RBO sometimes does !!

Well, wrt original post ( as the poster said ) that CBO is not able to see Index Usage as efficient means with OPTIMIZER_INDEX_COST_ADJ as 100, so i was just trying to make a ponit that instead of rigorus test in ur DEV to see the effects of changing the Val to say 50 or so, & only because this Qry is not performing well..