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

From: Jens Wartenberg <wartenberg_id_at_ibm.net>
Date: 1997/03/16
Message-ID: <332C46FE.7786_at_ibm.net>#1/1


Thanks for your reply.

I agree to your comments (regarding indexes, not analyze), though never having heard of #3. In my environments the inital/next parameters are a lot larger than sort_area_size.

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
---------------------------------------------------------------------

Ki 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!
> >
> > 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 Sun Mar 16 1997 - 00:00:00 CET

Original text of this message