Re: tempspace usage

  • From: jame tong <jametong@xxxxxxxxx>
  • To: srospo@xxxxxxxxxxx
  • Date: Wed, 20 Apr 2005 11:37:14 +0800

some other actions will also need temporary tablespace. check it further.
hash join/ global temporary table/

some operations than will need sort also will possible make use of
temporary tablespace.
sort/group/distinct/union/merge join/... etc

On 4/20/05, Steve Rospo <srospo@xxxxxxxxxxx> wrote:
> A 10046 trace should show direct path reads/writes when the session start=
> sorting/hashing to disk.  Also check the SEGTYPE column to see if the
> space is being used for sort, hash or temporary tables/indexes.
> If the problem is the space doesn't disappear from V$SORT_USAGE across
> multiple statements, I'd bet it was temp tables/indexes because a sort or
> hash should disappear when the statement ends while the temp table
> persists until the end of the transaction or session depending on how its
> scope was declared.  The join to V$SQLAREA as has been suggested won't
> help in this case because the V$SORT_USAGE.SQLADDR doesn't point anywhere
> since no single SQL statement "owns" it.  I've never been able to
> effectively been able to map such a row back to a particular temp
> table/index just using the data in V$SORT_USAGE.
> If there's only one statement and it keeps running and running, allocatin=
> space the entire time, it's the culprit.  There's lots of reasons for
> this: Massive hash join, ORDER BY on a massive result set (like a
> cartesian product), really big GROUP BY, etc.
> S-
> On Tue, 19 Apr 2005, Greg Norris wrote:
> > Is there a way to determine (or trace) which individual statements are
> > causing tempspace to be allocated to a session?  I've got a databases
> > where half a dozen sessions seem to gradually allocate more and more
> > tempspace (as measured by v$sort_usage), and then never release it.=3D2=
> > Eventually they acquire almost all of the available space, which of
> > course causes a flurry of ORA-1652 errors from other sessions.
> >
> > At the moment, I'm examining a trace from one of these sessions.  So
> > far, I don't see anything which could cause this behaviour... a few
> > inserts, some relatively simple selects, nothing particularly complex.
> >  Any suggestions on how to troubleshoot this sort of issue?
> >
> > The DB in question is Oracle (32-bit), running on Solaris 8.
> --
> Stephen Rospo        Principal Software Architect
> Vallent Corporation (formerly Watchmark-Comnitel)
> Stephen.Rospo@xxxxxxxxxxx           (425)564-8145
> This email may contain confidential information. If you received this in
> error, please notify the sender immediately by return email and delete th=
> message and any attachments. Thank you.
> --

Other related posts: