Re: Verify Outdated Optimizer Stats

From: Michel Cadot <micadot{at}altern{dot}org>
Date: Tue, 17 Feb 2009 06:43:46 +0100
Message-ID: <499a4e91$0$7892$426a34cc_at_news.free.fr>

"Ram" <krishna000_at_gmail.com> a écrit dans le message de news: f05bf69c-f0a2-4bc3-9cd0-2c72f8345447_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 .
|
|
| Regards
|
| RAM.
|
|

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;
/

Regards
Michel Received on Mon Feb 16 2009 - 23:43:46 CST