| Cost based optimizer [message #221404] |
Mon, 26 February 2007 10:33  |
lotusdeva
Messages: 201
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 #221409 is a reply to message #221407] |
Mon, 26 February 2007 10:42  |
lotusdeva
Messages: 201
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 #226528 is a reply to message #221404] |
Sat, 24 March 2007 12:24 |
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.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
