FAQ
Good question. I'll try asking the developers but I suspect I'll get a
blank stare. They send their SQL into the processing engine and stuff
comes back out. The guy who wrote the OCI interface may not be much
help either. I'll check to see how current cursors fluctuates during
the day and see if this persistent connection isn't closing things right.

Dan
On 08/13/2010 10:22 AM, Jeffrey Beckstrom wrote:
Might it be that you are leaving the cursor open and thus Oracle is
not cleaning it up?
Jeffrey Beckstrom
Database Administrator
Greater Cleveland Regional Transit Authority
1240 W. 6th Street
Cleveland, Ohio 44113
Dan Peacock 8/13/10 8:31 AM >>>
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
--
http://www.freelists.org/webpage/oracle-l
--
http://www.freelists.org/webpage/oracle-l

Search Discussions

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouporacle-l @
categoriesoracle
postedAug 13, '10 at 2:56p
activeAug 13, '10 at 2:56p
posts1
users1
websiteoracle.com

1 user in discussion

Dan Peacock: 1 post

People

Translate

site design / logo © 2022 Grokbase