Re: Verify Outdated Optimizer Stats

From: Robert Klemme <shortcutter_at_googlemail.com>
Date: Tue, 17 Feb 2009 18:41:05 +0100
Message-ID: <700b5iFm7roqU3_at_mid.individual.net>



On 17.02.2009 07:05, Ram wrote:
> On Feb 17, 10:43 am, "Michel Cadot" <micadot{at}altern{dot}org> wrote:

>> "Ram" <krishna..._at_gmail.com> a écrit dans le message de news: f05bf69c-f0a2-4bc3-9cd0-2c72f8345..._at_t39g2000prh.googlegroups.com...
>> | Hi All,
>> |
>> | I hope every one would have come across that users complaining about
>> | the response of database.Usually the first thought comes to the DBA
>> | is to Gather Stats and verify the performance.Performance will be far
>> | better after collecting stats and this is expected behavior.
>> |
>> | Before users start complaining about the performance of database is
>> | very slow , Can't we verify the statistics for out database is that
>> | outdated. Can any one throw some light on this .

>> For instance:
>>
>> set serveroutput on size 10000 format wrapped
>> declare
>> l_objList dbms_stats.objectTab;
>> begin
>> dbms_stats.gather_schema_stats
>> ( ownname => user,
>> options => 'LIST AUTO',
>> objlist => l_objList );
>> for i in 1..l_objList.count loop
>> dbms_output.put_line (l_objList(i).objName||' - '||l_objList(i).objType);
>> end loop;
>> end;
>> /

> Oracle Considers if there are more than 10% of modifications on the > segment to Re-Analyze right ??

Not sure, also this changes with version. 11g seems to have some improvements in this area.

Another approach would be to gather new stats without overwriting them and then comparing current and new stats.

Kind regards

        robert Received on Tue Feb 17 2009 - 11:41:05 CST

Original text of this message