ORA-04031

Few days back, while on a visit to client place, came across ORA-04031 on an oracle 10g database.

ORA-04031: "unable to allocate 4096 bytes of shared memory ("shared pool", "object_name", "alloc type(2,0)" ...)"

Many good articles are available on shared pool management and ORA-04031, but i really like the two mentioned below :-

1. http://blog.tanelpoder.com/2009/06/04/ora-04031-errors-and-monitoring-shared-pool-subpool-memory-utilization-with-sgastatxsql/

2. http://coskan.wordpress.com/2007/09/14/what-i-learned-about-shared-pool-management/

It was time to analyze the issue.

17:11:49 SQL>  select to_char(end_time, 'dd-Mon-yyyy hh24:mi') end, oper_type, initial_size,target_size, final_size from V$SGA_RESIZE_OPS where component='shared pool' order by end;
END                OPER_TYPE     INITIAL_SIZE TARGET_SIZE FINAL_SIZE
------------------ ------------- ------------ ----------- ----------
24-Jun-10 16:52     GROW	  922746880	939524096	922746880
24-Jun-10 16:53	   GROW	          922746880	939524096	922746880
24-Jun-10 16:54	   GROW	          922746880	939524096	922746880
24-Jun-10 16:55	   GROW	          922746880	939524096	922746880
24-Jun-10 16:56	   GROW	          922746880	939524096	922746880
24-Jun-10 16:57	   GROW	          922746880	939524096	922746880
24-Jun-10 16:58	   GROW	          922746880	939524096	922746880
24-Jun-10 16:59	   GROW	          922746880	939524096	922746880
24-Jun-10 17:00	   GROW	          922746880	939524096	922746880
24-Jun-10 17:01	   GROW	          922746880	939524096	922746880
24-Jun-10 17:02	   GROW	          922746880	939524096	922746880
24-Jun-10 17:03	   GROW	          922746880	939524096	922746880
24-Jun-10 17:04	   GROW	          922746880	939524096	922746880
24-Jun-10 17:05	   GROW	          922746880	939524096	922746880
24-Jun-10 17:06	   GROW	          922746880	939524096	922746880
24-Jun-10 17:07	   GROW	          922746880	939524096	922746880
24-Jun-10 17:08	   GROW	          922746880	939524096	922746880
24-Jun-10 17:09	   GROW	          922746880	939524096	922746880
24-Jun-10 17:10	   GROW	          922746880	939524096	922746880
24-Jun-10 17:11	   GROW	          922746880	939524096	922746880

The above shows that the shared pool wants to grow from inital size of 922746880 to 939524096.

select to_char(start_time,'hh24:mi:ss')timed_at,oper_type,component,parameter,oper_mode,initial_size,final_size from v$sga_resize_ops where start_time >= trunc(sysdate) order by start_time, component;
TIMED_AT OPER_TYPE      COMPONENT                PARAMETER             OPER_MODE INITIAL_SIZE FINAL_SIZE
-------- -------------- ------------------------ --------------------- --------- ------------ ----------
17:12:30 SHRINK         DEFAULT buffer cache     db_cache_size         IMMEDIATE    570425344  570425344
         SHRINK         DEFAULT buffer cache     db_cache_size         IMMEDIATE    570425344  570425344
         GROW           shared pool              shared_pool_size      IMMEDIATE    922746880  922746880
         GROW           shared pool              shared_pool_size      IMMEDIATE    922746880  922746880

17:12:33 SHRINK         DEFAULT buffer cache     db_cache_size         IMMEDIATE    570425344  570425344
         GROW           shared pool              shared_pool_size      IMMEDIATE    922746880  922746880

17:12:37 SHRINK         DEFAULT buffer cache     db_cache_size         IMMEDIATE    570425344  570425344
         GROW           shared pool              shared_pool_size      IMMEDIATE    922746880  922746880

17:12:49 SHRINK         DEFAULT buffer cache     db_cache_size         IMMEDIATE    570425344  570425344
         GROW           shared pool              shared_pool_size      IMMEDIATE    922746880  922746880

It now becomes very clear, that the shared pool was not able to grow,hence throwing ORA-04031.

The SGA_MAX_SIZE and SGA_TARGET was set to 1.5Gb, which i increased to 2Gb and the issue was resolved.Still i wanted to find out, why all of a sudden was shared pool trying to grow continuously.

After a few investigation, i came to know that some changes/addition were made in the application code, where “LITERALS” were being used instead of “BIND VARIABLES”.

The cursor_sharing parameter was set to “EXACT”, and as the newly added application code was using literals, every time a “NEW PARENT CURSOR” was getting generated (, because the sql statement though being textually same had different values passed to columns in the where clause.The sql was in top in “SQL ordered by Executions” in AWR report.

The parameter cursor_sharing was changed to “SIMILAR” and application developers were asked to changed the literals to bind variables in application code.

To conclude:-

1. Increased the SGA_TARGET value.
2. Changed cursor_sharing parameter to SIMILAR
3. Recommended using bind variables instead of literals.

It would be great ,to know your views on it 🙂

Advertisement