Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Use of TEMP tablespace other than sorting
The temporary tablespace is also used for holding data for user copies of global temporary tables, temporary lobs, and intermediate portions of hash joins.
Look at v$sort_usage whilst the query is running to see how much space is being used for what type of operation.
A six-way hash join could burn a very large amount of temporary space - but 48 GB does seem rather extreme. How big are the relevant data subsets, and is Oracle perhaps making a very bad join order decision that comes close to producing a Cartesian join of two tables with no discards.
-- Regards Jonathan Lewis http://www.jlcomp.demon.co.uk Coming soon one-day tutorials: Cost Based Optimisation Trouble-shooting and Tuning Indexing Strategies (see http://www.jlcomp.demon.co.uk/tutorial.html ) ____UK_______March 19th ____USA_(FL)_May 2nd Next Seminar dates: (see http://www.jlcomp.demon.co.uk/seminar.html ) ____USA_(CA, TX)_August The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html "Rick Denoire" <100.17706_at_germanynet.de> wrote in message news:bdgq5vgo1m50r16l2a40ilp7u49hokcbam_at_4ax.com...Received on Thu Feb 27 2003 - 03:36:14 CST
> Using an SQL query with a join over six tables, the query breaks
with
> the message "ORA-1652: unable to extend temp segment by 512
> in tablespace TEMP". This query does not do any sort operation, at
> least not explicitly.
>
> After having expanded TEMP four times consecutively up to 48 GB, I
am
> still getting the same error. So I wonder what TEMP is used for
beside
> sort operations. The tablespace is locally managed, uses uniform
> extents of 4 MB each. The sort area size is set to this size as
well.
>
> Is there a way to monitor the use of space in TEMP by a particular
> session?
>
> Oracle 8.1.7 / Solaris 2.7
>
> Any hint would be appreciated.
>
> Rick Denoire