Re: Sessions using TEMP and sharing extents

Comments embedded.
On Jul 10, 7:09 am, stephen O'D <stephen.odonn..._at_gmail.com> wrote:
> > Under create tablespace temp temporary and create temporary tablespace
> > temp the single sort segment per node is shared but extents allocated
> > within the segment are used by one session at a time. Under
> > traditional dictionary management each session allocates its own
> > private extents.
>
> Does this mean that with a locally managed Temp tablespace there is
> effectively a bunch of extents grouped into a single segment that is
> used for sorting by all sessions on the database, but with dictionary
> managed each session just grabs an extent as it needs it. Either way,
> 10 sessions sorting at the same time needs 10 extents.
>

No, it doesn't. With a true, locally managed temporary tablespace the extents are uniform (presuming you configure your local extent management to be uniform, which it should be). Also no permanent objects can occupy space in a true tempoary tablespace. Additionally a true temporary tablespace uses tempfiles, not datafiles, so it can't modify data outside of the temporary tablespace and, as it's using tempfiles, generates no redo for temporary object creation. Your current temporary tablespace, using datafiles, will generate redo for each temporary object created. This is additional overhead you don't need with temporary objects.

> I am guessing that when a session needs a temp extent to hold LOB data
> it will just grab one from temp in either locally managed or
> dictionary managed.
>

Yes, but that permanent segment you have configured generates redo; using tempfiles and a true temporary tablespace won't.

> I checked DBA_Tablespaces again, and in the contents column, oracle
> reports 'Temporary' so it knows its a temp tablespace - does this not
> mean its a true temp tablespace?

No, it doesn't, that only lists the type of objects this tablespace contains.

> From reading Kyte's Expert Oracle,
> temp is supposed to be NOLOGGING, but on our system its reported as
> LOGGING - this doesn't sound correct does it?

This is why you don't have a true temporary tablespace; you're using DATAFILES,. not TEMPFILES, as I explained earlier. Check DBA_DATA_FILES and I imagine you'll find your temporary tablespace listed. It shouldn't be. The files for a true temporary tablespace will be listed in DBA_TEMP_FILES. Your temporary tablespace should be created in this manner:

create temporary tablespace temp tempfile '<file spec here>' size <size here> extent management local uniform size 1M;

A 'fleshed out' example would be:

CREATE TEMPORARY TABLESPACE temp TEMPFILE '/oracle/data/temp01.dbf'

     SIZE 500M REUSE
     EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;

This will create a NOLOGGING temporary tablespace, which will also improve your performance considerably, I think.

David Fitzjarrell Received on Tue Jul 10 2007 - 07:50:35 CDT