shared pool and ORA-04031

Hi,
We are having shared pool issues and ORA-04031 errors. Database version : 9.2.0.6. We are looking at use of bind variables. Here is what I understand :
When memory is needed oracle allocates from shared pool. If it does not find it then it removes unused sql. LRU
Question : Apart from packages/procedures PINNED/KEPT in memory what else cannot be removed when memory is needed?
I am not sure about session memory as decided by param session_cached_cursors can be removed.

About pinning objects : It was for oracle7 and it was supposed to improve with 8i/9i since memory in pieces can be allocated. Something like if total memory needed is 40K then it could get 4 chunks of 10K that are not contiguous and then satisfy the request. Correct me if I am wrong. So the question is in that case why do we keep getting this error in 8i and even on 9i. Must be some explanation. I think there is a minimum allocation it does. For example if I give a short sql like : 'select * from dual' which does not need much memory then I think it still allocates a bigger minimum amount. How much is this minimum amount of memory. Is it 1K. Is it determined by some params.
Since memory used by old sql which is not used can be reused. LRU algorithm or whatever : And at any given time a small number of sql in memory is being actively used : So it can always remove the old sql and use the memory : so less chance of ORA-4031 unless there are lot of active processes. However we have seen serious ORA-4031 errors even in case of very less active processes. Sometimes needing bounce.
Other than PINNED objects and session_cached_cursors I think shared pool sql can be removed and yet we get this ORA-4031 error.We do see a lot of inactive processes like over 400 inactive and very less active like less than 30. So are the inactive ones holding on to session_cached_cursors. No, there is no way to reduce the inactive processes.
Thanks for your help.

its a simple answer, use bind variables

I think you should put that to Oracle support. It could happen some memory leak problems to cause that. The fact that bouncing the database solves the problem for a while, supports that assumtion.
If you do not have configured LARGE POOL, consider configuring it.

Rehards
Boris

Why large pool if MTS is not used?

session_cached_cursors AFIAK uses memory in your session, i.e PGA, it stores pointers to shared pool

ORA-04031 means memory fragmentation, if you dont use binds you are allocating, deallocating all the time, you are fragmentating your shared pool

Large pool is used also for RMAN channels, PX slaves etc.
If you use none of them, nor MTS then one can run without large pool, but if you do use some of these features, that could cause the error mentioned

davey23uk, bore, pando
Thanks for your help. Yes, we plan to put in bind variables. However that will take time to correct and implement. So till then I was just trying to understand why it is happening. I am following up with oracle support too. Technically all unused part of shared pool excluding PINNNED/KEPT packages can be overwritten and allocated. So this error should not happen. I know it may not be as simple. So I want to know if memory allocated by each session as defined by session_cached_cursors is also not replaceble. Does it stay even in case of ALTER SYSTEM FLUSH SHARED_POOL. Thanks

they will be onre-used if they can be reused if the cursors are still open, you cannot re-use it - and you can only re-use it if the memory chunk is big enough for the statement you are using

davey23uk/all,
Thanks for your help. The reason I ask is sometimes we have very less active session. Even then this error ORA-4031 comes. So is the memory allocated by each session as defined by session_cached_cursors untouchable. Meaning new requests cannot touch it even if the sessions are inactive. This is my specific question. Thanks again. What else is untouchable? Apart form PINNED packages. Any help would be appreciated.

what are you parameters for: shared_pool_size, shared_pool_reserved_size

Have you considered taking a Heap dump? Then look at the dump once the error occurs. This will help you verify if you have a fragmentation issue or you are truely out of space.

ixion,
shard pool : 500 Mb reserved : 50MB.
can u tell how i can take heap dump. is it system state?
the number of active sessions is less. so i am still wondering if memory allocated to each session as defined by session_cached_cursors stays in shared pool no matter what. meaning it is untouchable.

Talking about bind variables, you can consider setting the cursor_sharing parameter to similar or force. That should immediately substitute where possible the constants with bind variables.
See metalink Note:94036.1 for details

Good luck
Boris

Boris,
Thanks. We cannot set cursor_sharing to force/similar because looks like it has some issues. And bugs. So at this point it would help if I can know if memory related to session_cached_cursors can be moved out or not or is it like PINNED/KEPT objects which cannot be moved out. Thanks