Re: ANALYZE TABLE Questions

In article <CHqM33.770_at_idm.com>, cdm_at_idm (Charles D. Marcotte) writes:
>
> We are going to use statistical optimization in Oracle 7,
> how often should we analyze our tables??
> (analyze table table_name estimate statistics)
>
> Once a week, a month, every night ??
>
> I know it depends on the growth/shrinkage rate of the table, but are there
> any good general rules to follow ?
>
> Should we re-analyze tables when they grow/shrink by 10% ? 20% ? 50% ?

We haven't started this yet but we are planning to do a FULL analysis on all tables below an as yet unspecified size at least once a month and for very large tables we will be doing a full analysis once every 6 months.

Don't mix "statistical sample" runs and "full" runs on the same table. I have noticed major differences between these two methods on one of our tables. The major differences appear when doing statistical samples on indexes.  

>
> Once a table is analyzed, will the optimizer makes decisions ONLY on the
> frozen statistics ? What happens if the actual table triples in size,
> or an index is dropped ?

The optimiser works with whatever it has recorded unless you run analyse again. If the table tripples in size then the optimiser doesn't know about it. The stats are stored with the object so if you drop an index then it and its stats disappear. If you drop and recreate an index or add a new index then you will need to run ANALYSE against it to get stats.

>
> If I analyze a table right after its creation (zero rows), and never analyze
> it again, will this screw up future query optimizations using this table?
> If I analyze it with just one row, will this also screw up the optimization ?

If it has no rows then it will have no stats ... rule based optimisation. Yes.

>
> Can a table's statistics be exported and imported ???

Hmmm. Haven't tried this yet. Anyone know?

>
> What are the decisions and rules that the statistical optimimizer uses ??
> If you can't give out the details, how 'bout some general rules.
>
> Any help would be greatly appreciated!
>
> Chuck (cdm_at_idm.com)
>
> Below is an explanation of why we are using statitsical optimization:
>
> We have found that we need to use the statistical optimizer to get proper
> nested loop plans with many of our queries using distibuted joins.
> With the rule based optimizer, we would always get merge/sort joins in
> distributed queries. When the remote table is over 70 Megs, a merge/sort
> join will try to copy a huge chunk of the remote table into the local table
> space. It just takes forever... We call situations like these 'Black Holes'.
> We have found many, many queries in our product taht work fine in a single
> database that turn into 'Black Holes' when we distribute the tables across
> 2 or more systems.

One thing I have noticed is that if you do a full analysis of a table and its indexes then you should have enough temporary space for that user. The size tends to equal the sum of the table size plus the size of each index. To get around this on our larger tables we have created an extra tablespace on an empty disk(s) and pointed the user's "temporary tablespace" there, run the analyse, and then drop the tablespaces.

-- 
Bruce...        pihlab_at_cbr.hhcs.gov.au

"If you swallow a live frog first thing in the morning ...
 Nothing worse will happen to either of you for the rest of the day."

*******************************************************************
* Bruce Pihlamae  --  Database Administration                     *
* Commonwealth Department of                                      * 
*          Health, Housing, Local Government & Community Services *
* Canberra, Australia                             (W) 06-289-7056 *
*******************************************************************
* These are my own thoughts and opinions, few that I have.        *
*******************************************************************