Re: How to estimate the size of temp segment required?

"Roelof K. Salomons" <roelofs_at_ic.uva.nl> wrote in message news:99cklq$5qq$1_at_mail.ic.uva.nl...
> First of all:
>
> Is your temp tablespace really a temporary one ?
> Check the CONTENTS of it in DBA_TABLESPACES
>
> If it isn't make it temporary.
>
> If it is then check your SORT init.ora parameters. Perhaps you
> can try out something there.
>
> Furthermore, if some tables are large and your applications do
> FULL TABLE SCANS on them, make your INITIAL parameter in
> your temp tablespace about the size of these tables.
> For NEXT I would suggest 1M.

This must be one of the wackier recommendations I've ever seen.

"If the application does full table scans" has no bearing on the size of the temporary segment, unless the results of that full table scans is then subject to a sort of any kind. Full table scans per se do NOT use the temporary tablespace at all. A 'select * from bigtable ORDER BY blah' would do so, because it has to sort the data, not because it is doing a full table scan.

Secondly, the size of the extents in temporary tablespace should be equal to each other, and should be a multiple of the SORT_AREA_SIZE paramemeter, plus 1 block for housekeeping. This is because it is only when the PGA gets filled that we ever bother to swap down to the temp tablespace in the first place -hence, the only thing that ever gets written down there comes in PGA-sized chunks. Any other size for extents will result in vast quantities of wasted space in the tablespace.

As to how to size the temp segment, the answer is easy: you don't. *You* don't create temporary segments, Oracle creates it for you the second the first sort swaps to disk. You merely limit the size Oracle will make it by limiting the size of the temporary TABLESPACE in its entirety. The size of the tablespace should be considered the size of the segment. And how do you size the tablespace? By running v$sort_usage at various times during a normal working day, and seeing what the biggest set of consecutive sorts ever get to. The double it for safety's sake (disks are cheap), and go for it.

Regards
HJR
>
> In other words: creating new datafiles isn't always the answer.
>
> Roelof
>
> <u191833496_at_spawnkill.ip-mobilphone.net> schreef in bericht
> news:l.985234641.1655517578_at_[203.193.9.2]...
> > Hello,
> >
> > I have a complicated and clumsy :( query that causes ORA-01652:
> > unable to extend temp segment by 12137 in tablespace TEMP.
> > I know this can be resolved by adding datafile to the temp tablespace.
> > The problem is that I have added serveral times but still see the
> > problem. Is there any way to estimate the requirement on the temp
> > tablespace, except by setting to the tablespace to autoextend and
> > see how large it grows? I know the structures of the tables
> > and the number of rows in the tables. Or is there any method of
> > minimizing the requirement of temp tablespace? I have tried to
> > read the oracle docs but could not find the information :(
> >
> >
> > Anthony Tsang
> >
> >
> >
> >
> > --
> > Sent by thtsang from my-deja part of com
> > This is a spam protected message. Please answer with reference header.
> > Posted via http://www.usenet-replayer.com/cgi/content/new
>
>
>
>
Received on Thu Mar 22 2001 - 21:07:08 CET