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

From: John Bechtel <john.bechtel_at_teldta.com>
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
Received on Thu Mar 13 1997 - 00:00:00 CET

Original text of this message