Re: Sessions using TEMP and sharing extents

On Jul 9, 2:47 pm, stephen O'D <stephen.odonn..._at_gmail.com> wrote:
> 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!- Hide quoted text -
>
> - Show quoted text -

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.

I want to repeat my suggestion that you convert to using a true temporary tablespace. The performance benefit can be noticeable on a busy system.

HTH -- Mark D Powell -- Received on Mon Jul 09 2007 - 17:02:36 CDT