Sessions using TEMP and sharing extents

From: stephen O'D <stephen.odonnell_at_gmail.com>
Date: Mon, 09 Jul 2007 09:17:08 -0700
Message-ID: <1183997828.802057.243080@c77g2000hse.googlegroups.com>

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:

Create a new temp tablespace for this use with a 1MB extent size
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. Received on Mon Jul 09 2007 - 11:17:08 CDT