Re: [Q] analyze-command, speeding up, sort_area_size?

From: Jens Wartenberg <wartenberg_id_at_ibm.net>
Date: 1997/03/16
Message-ID: <332C4840.3F38_at_ibm.net>#1/1


Thanks for your reply.

I agree to your comments, though I have no experience with multiple CPU's. Concerning the sample size, the 1 percent was for measuring. The job was made with a 10% sample and a large sort_area_size (64MB instead of 2 MB for production), with the database all mine. The increasing of the sort_area_size had a large effect. Having experienced how fast it worked with these conditions, I will use 20 percent sample next time, which is what I have been recommended.

Regards

Jens Wartenberg
Email: wartenberg_id_at_ibm.net


WARTENBERG                      |        People
Industri Data  -  Denmark       |        that make a difference
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
                                |
IBM RS/6000 - IBM AIX - Oracle  |        For industrial needs
---------------------------------------------------------------------


John Bechtel wrote:
>
> > 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
Received on Sun Mar 16 1997 - 00:00:00 CET

Original text of this message