Re: Sessions using TEMP and sharing extents

On Jul 9, 5:59 pm, Mark D Powell <Mark.Pow..._at_eds.com> wrote:
> 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 --

Thankfully that idiot wasn't me!

Its definitely a temporary tablespace, as it came up as such in DBA_tablespaces.

The average sort size is way less than 100MB, so my initial reaction was why so big, but I wanted to make sure that sessions couldn't share temp extents. After thinking about it for a while I realized that a normal non temp extent can be used by only one object, so why should be temp be any different! Received on Mon Jul 09 2007 - 13:47:15 CDT