RE: temp tablespace blow up
- From: "Alvaro Jose Fernandez" <alvaro.fernandez@xxxxxxxxx>
- To: <lyallbarbour@xxxxxxxxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 15 Aug 2007 13:58:56 +0200
Lyall,
Extents from temporary sort segments are allocated to one sessions at a time
only, and are not deallocated until that session frees them. They are not
shared between sessions.
I think it would be reasonable to set extent size = 1 Mbyte , and set
PCTINCREASE=0 and INITIAL=NEXT (this way freed extents will match the next
request extent size, and no need for SMON to consolidate freed extents, I
think). Intermintently, run a
select sid, serial#, vs.osuser, extents, contents, blocks from v$session vs,
v$sort_usage su where vs.saddr = su.session_addr ;
to learn session's usage.
In 8.1 there are much more options (EXTENT MANAGEMENT LOCAL/uniform extent
size).
¿You cannot upgrade?
regards
--
If the temp tablespace is going to grow quickly, it's because of some (or
somessss) query that need to run through a lot of rows, right? Wouldn't you
want as few extents in the temp tablespace as possible if this is going to
happy? Why not make the TEMP tablespace one big extent?
Lyall
Other related posts:
