Yes, this does get you some space back but in my case it just bought us some
extra time before we got more 4031 errors
For me the one PROBLEM was cursor_spaced_for_time was set to true.
I also had a lot of leaks happening:
1) DBMS_OUTPUT was put into alot of code (even in a large cursor loop) and
the buffer size was not adjusted causing alot of leaks
you can do this but it has to be set up correctly.
2) leaks from a heterogenous link to sql server and updates across a dblink
- most of this cured by fixing cursor_spaced_for_time.
3) .Net with C# not using dispose for garbage collection. (dispose also
cures cursor problems). .Net in web services seems
to consume ALOT of resources.
3) also check your session_cached_cursor variable. might be set too high.
You might want to do a search on 4031 in the archives and follow all the
help I got.
I still have some sporatic leak issues with the database. But it is very
hard trap the sql to the leak.
Check also to see if cursors are being closed.
Kathy
-- --Original Message-- --
From: Wolfson Larry - lwolfs [mailto:lawrence.wolfson@(protected)]
Sent: Wednesday, July 14, 2004 10:41 AM
To: 'oracle-l@(protected) '
Subject: RE: ORA-04031 (See ORA-04031.ora-code.com)
Paula,
Below are some comments from an earlier post on 4031.
You may find that you don 't really need the
shared_pool_reserved_size.
Run the script below to see what kind of activity there really is
and decide if it 's worth it.
Could you let us know what you find and also what are your
shared_pool_reserved_size and shared_pool_size parameters.
Thanks
Larry
Kathy,
Ummmmmm, I think you missed my point. What I thought I said was we
found the shared_pool_RESERVE wasn 't getting used at all or hardly at all.
So we changed it from the default to 1M.
shared_pool_reserved_size string 1024000
shared_pool_size string 514400000
I don 't know how long your DB has been up but it looks like you had
85 failures and you still had 25M you never used.
The problem is Only allocations larger than
SHARED_POOL_RESERVED_POOL_MIN_ALLOC can allocate space from the reserved
list if a chunk of memory of sufficient size is not found on the shared
pool 's free lists.
Your query below says you had 25M free for something really big to
stick in the SharedPoolReserve but 4132 was smaller then the
SHARED_POOL_RESERVED_POOL_MIN_ALLOC so you got a 4031 when you had 25M free.
I didn 't say anything about changing the SHARED_POOL size.
It looks like you found the problem with .net.
What I 'm suggesting is you have 25M you might want to put to better
use somewhere else.
