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

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!
>
> When recreating indexes i use a special version of initxx.ora with
> sort_area_size set to 64000000. This helps a lot.
>
> I figured, that sinze the analyze command uses the temp-area, it could
> perhaps benefit from a large sort_area_size.
>
> Any knowledge/experience concerning this matter would be a great help.
>
> LONGER VERSION:
> I need to analyze about 2GB of data (+ 2GB of indexes) using the command
> analyze table xxx estimate statistics sample 10 percent.
>
> I would like to do it tonight, but my window for the operation is only 3
> hours. I tried analyzing one of my large tables with a sample of just 1
> percent. It took 4 minutes, so I hope I can find a way to speed things
> up.
>
> TIA
>
> Regards
> Jens Wartenberg
> -------------------------------------------------------------
> WARTENBERG | People
> Industri Data - Denmark | that make a difference
> - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
> IBM RS/6000 - IBM AIX - Oracle | For industrial needs
> -------------------------------------------------------------
>
>

hello mr. WARTENBERG :

A "short" answer to a SHORT VERSION:

1. Use the "unrecoverable" option while creating indexes : it bypasses redo logging.
2. Using Special Init files for special activities is a desirable action - if database can be shut down. In any case, Remember the SORT_AREA_SIZE is allocated "per user" (PGA), and that SORT_DIRECT_ACCESS might help sorting as well.
3. Last thing - while planing for sort activity - be sure that initial & next sotrage parameters for the temporary tablespace are twice as big as SORT_AREA_SIZE - again, a rule of thumb!

So long,

Muli Koppel Received on Thu Mar 13 1997 - 00:00:00 CET