Re: Sessions using TEMP and sharing extents

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 -- Received on Mon Jul 09 2007 - 11:55:44 CDT