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

You might also think of using a locally managed tablespace for speed after you get the size nailed down.

Jonathan Lewis wrote:

> The amount of space a query demands in TEMP
> is dependent on the volume of data to be processed
> and the access path used.
>
> Your first step should be to review the execution
> path for this query to decide if it is a sensible path.
> A fairly common cause of excess TEMP space
> usage is the query which uses several SORT/MERGE
> joins, a couple of HASH joins, or a few 'view instantiation'
> steps.
>
> See if there is a better execution path first, then
> come back with the path and some data volume
> figures if you haven't managed to reduce the
> problem.
>
> --
> Jonathan Lewis
> Yet another Oracle-related web site: http://www.jlcomp.demon.co.uk
>
> Practical Oracle 8i: Building Efficient Databases
> Publishers: Addison-Wesley
>
> Reviews at: http://www.jlcomp.demon.co.uk/book_rev.html
>
> u191833496_at_spawnkill.ip-mobilphone.net wrote in message ...
> >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 Fri Mar 30 2001 - 15:19:49 CST