Re: Update Statistics

"Rob Panosh" <rob_panosh_at_asdsoftware.com> wrote in message news:<3dade05c$1_at_news.splitrock.net>...
> Anybody,
>
> I have an applications that runs against an Oracle 8i database ( 8.1.7 ) and
> all of a sudden I noticed that my select statements came to a crawl. Once I
> ran compute statistics my SQL Select Statement performance went back to
> normal. Can anybody give me a explanation about statistics? Is this
> something I have to run every so often.
>
> Thanks,
> Rob Panosh

  Hi Rob,

     You need to analyze the tables and indexes after a significant portion of the data has changed ( typically 10%) from inserts, updates or deletes. Oracle relies on the statistics to determine how to run your statement.

    One thing you might consider, depending on the size and activity of the table is to set it to monitoring:

          alter table my_table monitoring;

   And schedule a job to periodically run a command like:

         dbms_stats.gather_schema_stats('my_schema', cascade=> true, 
                                         options => 'gather stale');

   The first will monitoring update, insert and delete activity on a table.   

   The second will analyze schema, gathering statistics only tables that have changed 10% or more. The 'cacade => true' portion will analyze the indexes for the table. Received on Thu Oct 17 2002 - 05:02:20 CEST