On Jul 10, 1:50 pm, "fitzjarr..._at_cox.net" <fitzjarr..._at_cox.net> wrote:
> 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
Thanks for that - a good answer that explains all!
Received on Tue Jul 10 2007 - 16:14:40 CDT
