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

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