Re: TEMP tablespace file suddenly increasing in size

quarkman <quarkman_at_myrealbox.com> wrote in message news:<oprtr886hxzkogxn_at_haydn>...
> On Tue, 12 Aug 2003 09:48:39 +0200, Randi Wølner <randiwolner_at_hotmail.com>
> wrote:
>
> > Oracle 8.1.7.4 on AIX:
> >
> > We have the TEMP tablespace in an autoextensible file. The file has
> > "suddenly" started to grow. This might have to do with new code (views /
> > triggers / packages), and there has also been some bad performance at the
> > same time that the increasing size of the TEMP datafile was discovered.
> >
> > How can we find what code is making the TEMP-tablespace grow?
> >
> > If we change the datafile option to AUTOEXTEND=NO - what kind of trouble
> > might that cause?
> >
> > Randi W.
> >
>
> Autoextension of anything (except SYSTEM, where it ought to be compulsory)
> is a convenience that no DBA doing a proper job (ie, of pre-emptive
> management) should ever take advantage of. Precisely because the auto-
> extension happens right in the middle of someone wanting space, and having
> to go off and get it, its a dead ringer for poor performance.
>
> With autoextensioin turned off, you also get poor performance. Very poor.
> As in, the thing needing the space which it can no longer get automatically
> gives up the ghost and announces to you that it's 'unable to extend by xx
> in tablespace TEMP'. The transaction or query then blows up (and gets
> rolled back if need be. Though in 9i, you can arrange for it to suspend
> itself until you sort out the disk space issue, rather than immediately
> blowing up). Hence the need to pro-actively manage the thing, and make sure
> sufficient space is provided up-front.
>
> To see who is using temporary space (and you need to query this precisely
> when the write to TEMP is taking place), query v$sort_usage. Join that up
> with v$session and v$process, and you can find out precisely what session
> and user is doing the deed. And you can then hunt around in v$sqlarea to
> find out what sql statement they issued to cause the problem (or pick the
> phone up and ask them).
>
> Regards
> HJR
"Autoextension of anything (except SYSTEM, where it ought to be compulsory) is a convenience that no DBA doing a proper job (ie, of pre-emptive management) should ever take advantage of"

I agree. See the following article for how to see who is using sort space: How do I find out who is currently using the space in the temporary tablespace ? http://www.jlcomp.demon.co.uk/faq/sort_user.html

HTH -- Mark D Powell -- Received on Tue Aug 12 2003 - 08:00:54 CDT