Re: CBO optimizer

"qwerty" <qwerty_at_yahoo.it> wrote in message news:coqasc$buf$1_at_lacerta.tiscalinet.it...
>>>
>>> Are there other solutions to this problem???, can you give me
>>> suggestions ???
>>
>> I have a question, a suggestion and a comment.
>>
>> The question is, do your tables really change in number of rows
>> drastically downwards as well as upwards? As a sweeping generalisation
>> large tables tend to stay large and increase in size and small tables
>> tend to stay small. The behaviour you describe would in my experience be
>> a little unusual.
>
> yes my tables change upwards,and then downwards... and then upwards..then
> downwards...

ok.

>
>>
>> The suggestion is, you know that you can set stats using dbms_stats? If
>> so, then your second suggestion seems likely to make sense. calculate the
>> stats when data is 'worst' for performance and then stick with that. Get
>> your worst performing time to perform adequately, and the chances are
>> (though oddities do arise) that lighter load times will be not as good as
>> they could be , but still OK as well.
>>
>> The comment. I don't see anywhere that Oracle suggest 'calculating
>> statistics often'.
>
> Yes, you are right, but oracle suggest to calculate statistcs (i use
> dbms_stats) when the data has been changed and the previous one... so if
> the data changes frequently..... (this is a my deduction...)

To rephrase my suggestion - it seems highly plausible (we don't have any specific so you are limiting yourself to general advice) that your problems are more likely to arise when you stats say the tables are small, when in fact they are large. If this is wrong please correct me. If so then don't collect the stats for small tables - set them for large ones. Sure your times when you have small objects might be inefficient, but if they are efficient 'enough' don't worry.

-- 
Niall Litchfield
Oracle DBA
http://www.niall.litchfield.dial.pipex.com