-
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 ?
-
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).
Jeff Hunter
-
Re: so setting this work: OPTIMIZER_INDEX_COST_ADJ - now what ?
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.
funky...
"I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."
"Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"
-
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 ...
-
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?
funky...
"I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."
"Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"
-
Re: Re: so setting this work: OPTIMIZER_INDEX_COST_ADJ - now what ?
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...
-
-
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?
-
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 ?
Amar
"There is a difference between knowing the path and walking the path."
-
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..
Why not try RULE and see? ( Does is matter )?
Abhay.
funky...
"I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."
"Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|