Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Use of TEMP tablespace other than sorting

Re: Use of TEMP tablespace other than sorting

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Thu, 27 Feb 2003 09:36:14 -0000
Message-ID: <b3kmlp$mvj$1$8300dec7@news.demon.co.uk>

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

> 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
Received on Thu Feb 27 2003 - 03:36:14 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US