shared pool and ORA-04031
We are having shared pool issues and ORA-04031 errors. Database version : 126.96.36.199. 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.