Re: Sessions using TEMP and sharing extents

On Jul 9, 12:55 pm, Mark D Powell <Mark.Pow..._at_eds.com> wrote:
> On Jul 9, 12:17 pm, stephen O'D <stephen.odonn..._at_gmail.com> wrote:
>
>
>
>
>
> > Guys,
>
> > On Oracle 9.2.0.7 after some application changes that make heavy use
> > of clobs, we have noticed our Temp tablespace grow considerably.
>
> > Querying v$sort_usage and v$tempseg_usage shows lots of sessions
> > holding 1 extent for LOB_DATA and 1 for a LOB_INDEX.
>
> > When v$sort_usage tells you a session is holding 1 extent on Temp for
> > some work, does that mean that the whole extent is not available for
> > other sessions or can two sessions share the same extent?
>
> > I ask this, because our TEMP tablespace is currently setup as manual,
> > dictionary managed with initial and next extent size of 100MB
> > (reported by querying dba_tablespaces). Our CLOBS are no where near
> > this big as each session is grabbing and holding 100MB, and only using
> > a tiny amount of it if the extents cannot be shared. My first thought
> > is that we should either:
>
> > 1. Create a new temp tablespace for this use with a 1MB extent size
> > 2. Reduce the extent size for temp for the entire database.
>
> > Would this be a good idea? Any advice would be a great help ...
>
> > Thanks,
>
> > Stephen.
>
> LOL
>
> My question is who is the "idiot" that desided that 100M is a good
> size for temp segments?
>
> How big is the average sort on your system anyway?
>
> I think a smaller default size is worth a try.
>
> HTH -- Mark D Powell --- Hide quoted text -
>
> - Show quoted text -

PS

Why is this temporary tablespace dictionary managed? I hope it is at least "create tablespace temp temporary" rather than "create tablespace temp" which defaults to permanent.

You should recreate it as create temporary tablespace temp tempfile '...'

HTH -- Mark D Powell -- Received on Mon Jul 09 2007 - 11:59:44 CDT