0

I was executing a bunch of UPDATE statement. Around ~32.8K got executed and committed, then got the error:

cx_Oracle.DatabaseError: ORA-04031: unable to allocate 3896 bytes of shared memory ("shared pool","UPDATE COHORTS_A SET COHORT5...","sga heap(1,0)","kglsim object batch")

This made me think that the SQL area on Oracle DB grew too much to the point of maxing out the shared pool's memory.

Running the follow query would confirm it, but ironically and unsurprisingly I get the ORA-04031 error on it as well:

SELECT * FROM (
SELECT name, bytes/(1024*1024) MB FROM v$sgastat 
WHERE  pool = 'shared pool'  ORDER BY bytes DESC)
WHERE rownum < 11;

What could have caused the shared pool to be full? If there's no room left in the SQL area cache, shouldn't older statements be removed?

I am running Oracle 11gR2 11.2.0.3.

Here are the SQL UPDATE statements I was executing and committing (I know I can optimize them):

UPDATE COHORTS_A SET COHORT2 = 6 WHERE HADM_ID = 22800
UPDATE COHORTS_A SET COHORT2 = 5 WHERE HADM_ID = 22801
UPDATE COHORTS_A SET COHORT2 = 5 WHERE HADM_ID = 22802
UPDATE COHORTS_A SET COHORT2 = 6 WHERE HADM_ID = 22803
UPDATE COHORTS_A SET COHORT2 = 6 WHERE HADM_ID = 22804
UPDATE COHORTS_A SET COHORT2 = 6 WHERE HADM_ID = 22805
UPDATE COHORTS_A SET COHORT2 = 6 WHERE HADM_ID = 22806
UPDATE COHORTS_A SET COHORT2 = 6 WHERE HADM_ID = 22807
UPDATE COHORTS_A SET COHORT2 = 6 WHERE HADM_ID = 22808
UPDATE COHORTS_A SET COHORT2 = 6 WHERE HADM_ID = 22809
UPDATE COHORTS_A SET COHORT2 = 6 WHERE HADM_ID = 22810
UPDATE COHORTS_A SET COHORT2 = 6 WHERE HADM_ID = 22811
4
  • Not using bind variables causes this. If changing the SQLs is too much work you can try ALTER SESSION cursor_sharing=SIMILAR;. May 5, 2015 at 5:32
  • @MindaugasRiauba Thanks, good to know. I use the same cursor though: in that case, would using ALTER SESSION cursor_sharing=SIMILAR; make a difference? Also, if there's no room left in the SQL area cache, shouldn't older statements be removed? May 5, 2015 at 14:36
  • The only way to know for sure is to try. :) And regarding shared memory error the problem is that with such huge amount of SQLs shared pool gets fragmented. And it maybe has enough memory to hold further statements but memory is scattered in such small chunks that Oracle is unable to defragment it in a few steps. May 6, 2015 at 19:18
  • @MindaugasRiauba Thanks, in the end I used prepared statements, but I'll keep ALTER SESSION SET CURSOR_SHARING=SIMILAR; in mind. May 6, 2015 at 22:19

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Browse other questions tagged or ask your own question.