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:
>=20
> A 10046 trace should show direct path reads/writes when the session start=
s
> sorting/hashing to disk. Also check the SEGTYPE column to see if the
> space is being used for sort, hash or temporary tables/indexes.
>=20
> 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.
>=20
> If there's only one statement and it keeps running and running, allocatin=
g
> 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.
>=20
> S-
>=20
> On Tue, 19 Apr 2005, Greg Norris wrote:
>=20
> > 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=
0
> > 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 8.1.7.4.0 (32-bit), running on Solaris 8.
>=20
> --
> Stephen Rospo Principal Software Architect
> Vallent Corporation (formerly Watchmark-Comnitel)
> Stephen.Rospo@xxxxxxxxxxx (425)564-8145
>=20
> This email may contain confidential information. If you received this in
> error, please notify the sender immediately by return email and delete th=
is
> message and any attachments. Thank you.
>=20
>=20
> --
> //www.freelists.org/webpage/oracle-l
>
--
//www.freelists.org/webpage/oracle-l
Other related posts:
