Re: TEMP tablespace file suddenly increasing in size

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 Received on Tue Aug 12 2003 - 03:16:08 CDT