Re: How frequently: Analysing

76434.1353_at_compuserve.com wrote in message news:<3e53c8e3.21360464_at_news.hccnet.nl>...
> How often should I analyse the user tables in the system?
> Currently a job is doing this every morning which causes some log
> switching (5 x 10MB).
>
> The tables are slowly growing everyday but the structure or the number
> of tables and indexes is not changing.
> How frequently analysing is advised?
> Thanks

Generally speaking, you want to re-analyze a table when about 10% of the table has changed due to updates/inserts/deletes.

What I do is set the tables to monitoring, then have have a job:   

         dbms_stats.gather_schema_stats(my_schema, cascade => true,
                                        options => 'GATHER STALE');

 And have this run once a day. It only analyzes a table if it's marked stale (10% has changed). That way, my large tables get analyzed relatively infrequently (every 2-3 months), and my smaller ones more frequently.

 Remeber though, that it's not necessarily the structure of your tables but whether the number rows, data distrubition, etc... has changed. Received on Wed Feb 19 2003 - 19:18:31 CST