Re: [Q] analyze-command, speeding up, sort_area_size?
Date: 1997/03/13
Message-ID: <33288F8C.6500_at_teldta.com>#1/1
> Jens Wartenberg <wartenberg_id_at_ibm.net> wrote in article
> <3326B348.367A_at_ibm.net>...
> > Hi,
> > SHORT VERSION:
> > Is there a way to speed up the analyze command!
> >
>
>
> A "short" answer to a SHORT VERSION:
>
> 1. Use the "unrecoverable" option while creating indexes : it bypasses redo
> logging.
>
> Muli Koppel
Creating indexes, and the "unrecoverable" option has nothing to do with
speeding up the analyze of a table or index. What will speed it up is
reducing the number of rows it needs to look at to create the
statistics, which is done by changing the estimate statistics clause.
Changing the sort_area_size will probably help a little, depending on
the size of the objects being analyzed and what your sort_area_size is
normally set to. If you have multiple cpu's, you may want to run
several analyze commands simultaneously. I generally run #cpu's + 1
analyze jobs at the same time. This may depend on whether there is
anything else happening in the database, or if you have it all to
yourself.
By the way, a sample of 1 (one) percent is probably too low to get any
meaningful statistics from, and may actually hurt performance because
the optimizer uses these inaccurate statistics to base its query plan
on.
- John Bechtel