Re: Temporary segments in temporary tablespaces
- From: Dan Peacock <danp@xxxxxxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Fri, 13 Aug 2010 14:52:19 -0400
And it turns out it was/is a bug:
Cause
The problem as being hit is:
Bug 5723140 </ep/faces/secure/km/BugSearch.jspx?bugId=5723140> Temp LOB
space not released after commit
The fix is included in 10.2.0.4.0 patchset but NOT activated by default
Solution
a new event introduced (event 60025) where when set if there are no
active temp lobs in the session (ie: both cache temp lob and no-cache
temp lobs used are zero) then the temp segment itself will also be freed
releasing the space for other sessions to use. Note that this change is
disabled by default.
|alter session set events '60025 trace name context forever';|
On 08/13/2010 11:24 AM, Powell, Mark wrote:
Dan, take a look to see what usage has been assigned to the temporary
tablespace extents assiged to the application in question. That is, the
extents may be for sort, temporary LOB segments, and hash joins. The nature of
the extent usage would help determine if the application has failed to close
cursors or is using object collections and needs to free the space up.
See v$sort_usage and look up dbms_session.free_unused_user_memory for releasing
pl/sql array space.
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On
Behalf Of Dan Peacock
Sent: Friday, August 13, 2010 8:31 AM
To: oracle-l@xxxxxxxxxxxxx
Subject: Temporary segments in temporary tablespaces
Good Morning List!
I've been working on a "problem" with my developers that I'm stumped by and I'm hoping the
collective can shed some light on it for me. We have an application that comes in through some OCI libraries
and opens a connection but doesn't close it as the overhead of making the connection is too steep for the
scanner application that uses the library. The consequence of this is, as time wears on it seems that the
temporary sort segments accumulate for the connected user to the point where there's nothing left for
"real" work and applications start crashing with "unable to extend TEMP" (other
applications use these same libraries and can run some pretty hefty queries). My understanding was that SMON
would come through and clean up temporary segments that were no longer needed but we did some testing and
don't think this is the case for an open connection.
Here's what we did:
1) We have a SQL statement that we know puts stuff out into the TEMP tablespace
due to sorting. Approximately 24mb of sort space is used by this statement.
2) We ran the statement, issued a commit to start a new "transaction".
3) Ran the statement again and are now consuming 48mb of TEMP space.
4) We waited for over an hour and the amount of space never went down.
5) Thinking it was just allocated to us and would be reused we tried it again
and are now sitting at 72mb of TEMP space
So, my question is, short of closing and reopening the connection, what can we
do to force these segments to release and get reused. We've already resized
the PGA and that helped some, but it only delays the inevitable.
Any insights would be greatly appreciated.
Dan Peacock
Auto-wares, Inc.
Secretary of the West Michigan Oracle Users Group
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
