Why my Temporary tablespace is always 99% full ?

From: Norman Dunbar <Norman.Dunbar_at_lfs.co.uk>
Date: Wed, 7 May 2003 08:24:41 +0100
Message-ID: <E2F6A70FE45242488C865C3BC1245DA703A4990F@lnewton.leeds.lfs.co.uk>

Morning Tingl,

OK, I'm prepared to believe that in your case it has not affected performance at all/all that much - after all, I don't know your system.

It *could* be that your systems make little use of the temp tablespace anyway and does all sorting in memory for example, and if so, this is a good thing. However, on my system(s) here we have large sorts that do spill over onto disc sometimes, and having only one space management lock in the entire database *will* cause severe performance problems when temp segments need to be allocated and deallocated because of a permanent temp tablespace.

I'm talking too about tables which have hundreds of millions of rows in them - not SCOTT.EMP stuff - trust me, when your temporary tablespace is permanent, you *will* have performance problems as soon as you try to use it.

Cheers,
Norm.

Norman Dunbar
Database/Unix administrator
Lynx Financial Systems Ltd.
mailto:Norman.Dunbar_at_LFS.co.uk

Tel: 0113 289 6265
Fax: 0113 289 3146
URL: http://www.Lynx-FS.com


-------------------------------------

Received on Wed May 07 2003 - 02:24:41 CDT