Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Temp tablespace vs. tablespace ... temp

Re: Temp tablespace vs. tablespace ... temp

From: Howard J. Rogers <dba_at_hjrdba.com>
Date: Sun, 12 May 2002 08:49:56 +1000
Message-ID: <abk783$ash$1@lust.ihug.co.nz>


The old way of doing it results in a "logging", dictionary managed tablespace by default. The other nasty with it is that every block within the dbf gets formatted at create time.

The new way results in a nologging, locally managed tablespace. On any platform other than NT (unfortunately), the data file(s) associated with this sort of tablespace are also "sparse files", meaning that the blocks within the dbf are not formatted until they are actually needed. What that means is that a 16Gb temporary tablespace takes about half a second to create. Try that with the old sort of tablespace, and you're sitting there for many, many minutes at least. (The last time I demonstrated this point, I neglected to realise that NT wouldn't know a sparse file if it came up and bit on the backside. So my 'look how little time this takes!' introduction was followed by a rather embarrasing 10 minute pause..... I like to demonstrate the point by creating a 4Gb temporary tablespace!!).

Which helps explain why the new sort was invented. It's always been Oracle's advice that temporary tablespace didn't really need backing up, nor recovery. Just offline drop and re-create. But with the old sort of tablespace, that advice resulted in quite a bit of user inactivity: until the large temporary tablespace was re-created, big sorts were out of the question. With the new sort of tablespace, the advice becomes practical reality (indeed, you can't actually recover tempfile tablespaces), because the re-creation step takes moments, however big the tablespace might be.

The fact that tempfile tablespace is locally managed means acquisition of extents within the tablespace should go slightly quicker than in the old datafile sort. Sort performance should therefore go up slightly. That's further helped by the fact that tempfile tablespaces don't log. Given that a number of 8i new features (such as global temporary tables) make use of temporary tablespace, that performance enhancement can become significant for your application. Being locally managed also means that your temporary tablespace won't fragment. There's also an overhead saving: with datafile temporary tablespace, the extent size was supposed to be sort_area_size or multiples thereof, plus 1 block. With tempfile tablespace, it's just sort_area_size or multiples thereof, and forget about the extra block.

Then there's the fact that tempfile tablespaces can be written to when the database is opened read-only, but datafile ones can't. Given again the fact that opening a database read only was an 8i new feature, it was clearly important to make that new feature fully functional by enabling you to do large sorts in a read-only database.

The short answer is that if you've got 8i, there's absolutely no reason to use the old datafile type of temporary tablespace. The enhancements in functionality that tempfiles brought are modest, but nevertheless significant. Enough, at least, to make the tempfile version the only one to adopt.

Regards
HJR "Ed Stevens" <spamdump_at_nospam.noway.nohow> wrote in message news:3cdbdec9.68328941_at_ausnews.austin.ibm.com...
> Platform: Ora 8.1.7 on NT
>
> Would someone care to enlighten me on the differences in CREATE TEMPORARY
> TABLESPACE and CREATE TABLESPACE ..... TEMPORARY.
>
> The explanation in the docs is pretty terse. As I understand it, CREATE
> TEMPORARY TABLESPACE allocates a temporary file that gets dropped and
recreated
> on demand, though I don't see what creates that demand. CREATE TABLESPACE
.....
> TEMPORARY creates a permanant tablespace to hold transient objects like
segments
> created to perform disk sorts. I also see that local management cannot be
used
> with the TEMPORARY option of CREATE TABLESPACE.
> --
> Ed Stevens
> (Opinions expressed do not necessarily represent those of my employer.)
Received on Sat May 11 2002 - 17:49:56 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US