Home » RDBMS Server » Performance Tuning » Cost based optimizer
Cost based optimizer [message #221404] Mon, 26 February 2007 10:33 Go to next message
lotusdeva
Messages: 198
Registered: March 2005
Senior Member
All --

I have several queries that perform "not so good". I have tried to optimize them, but ran into the following problem. We are on 9i and use rule-based optimizer. It appears that the tables involved are not analyzed, hence forcing cost-based optimization (i.e. by the means of /*+ ALL_ROWS */ hint, for example) is pointless? As Oracle will try to guess the best execution path. But it really needs the proper statistics in order to do that? It seems that when I do add this hint, at some parts of the day the performance is better, and at some is worth. My question is - is this unpredictible behavior due to the fact that we dont have our tables analyzed? Thank you!
Re: Cost based optimizer [message #221407 is a reply to message #221404] Mon, 26 February 2007 10:37 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Without statistics, CBO cannot do anything.
Hint is just a hint. CBO may or may not use it.
Re: Cost based optimizer [message #221409 is a reply to message #221407] Mon, 26 February 2007 10:42 Go to previous messageGo to next message
lotusdeva
Messages: 198
Registered: March 2005
Senior Member
Right. But I wonder why at some points of the day this hint would actually make the performance worse? Or the hint should not make a difference (if there is no statistics) - i.e. the performance should be the same without it? Thank you!
Re: Cost based optimizer [message #221521 is a reply to message #221404] Tue, 27 February 2007 05:18 Go to previous messageGo to next message
michael_bialik
Messages: 611
Registered: July 2006
Senior Member
AFAIK using hints invokes Cost Based Optimizer. However without stats CBO uses a number of assumptions (usually incorrect ones) about data statistics.

HTH. Michael
Re: Cost based optimizer [message #226528 is a reply to message #221404] Sat, 24 March 2007 12:24 Go to previous message
nmacdannald
Messages: 460
Registered: July 2005
Location: Stockton, California - US...
Senior Member
I am not sure 'first_rows' helps that much, I have tried it several times. Especially if have a order by clause.
There is no getting out of analyzing (estimate status on 10%) whenever significant inserts of deletes have happened. You can schedule it for a quite time.
Previous Topic: Query execution time is fluctuating lot
Next Topic: Oracle10g Performance question
Goto Forum:
  


Current Time: Tue Dec 06 04:48:37 CST 2016

Total time taken to generate the page: 0.08709 seconds