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 :)

About these ads
    • Santosh Kumar
    • July 10th, 2010

    Nice Post and findings, Anand.

    Why you choosed to go for cursor_sharing=similar and not for ‘force’?

    Personally, I have seen SGA_TARGET giving a lot of locking issue in OLTP environment(because of high shrink and grow activity), so I don’t recommend my customers to use the parameter for OLTP.

    • Hi Santosh,

      Thanks for patience of visiting my blog and reading :)

      Setting cursor_sharing=similar, causes similar statements to share the same cursor,without compromising execution plan, i.e, only “optimally sharable statement share cursors without deteriorating execution plan” and as few code were newly added, the previous AWR reports didn’t show significant value for hard parsing, making me to take the decision of using SIMILAR.I feel its better to try SIMILAR before changing to FORCE.FORCE can sometimes lead to unlucky sharing of executions plans.

      And, also application team has been very strictly asked to change the code and use bind variables.

      It would be great if you can throw some light on “SGA_TARGET giving a lot of locking issue”.Kindly share your experience on this.

      Regards,
      Anand

        • Santosh Kumar
        • July 11th, 2010

        :).
        ————————————————
        It was a 10g database. If automatic stats gathering is happening, then there may be chances that the columns in question may be having Histograms. The cursor_sharing=similar with and without histogram has different behavior.

        Cursor_sharing=’similar’ + histogram –> behaves similar to cursor_sharing=exact(but will use less space in shared_pool, this is because of parent and child cursor).

        Cursor_sharing=’similar’ – histogram –> behaves similar to cursor_sharing=FORCE.

        As you’ve mentioned that the change was with only few codes, So in my opinion convincing them to use bind variables for those few codes instead of changing the behavior of the whole system is more acceptable solution.

        I have seen application modules behaving erroneous after changing the cursor_sharing parameter.

        N.B.: Based on version of the 10g, there are bugs attached with cursor_sharing=similar
        ————————————————–
        Regarding SGA_TARGET, I must be having an AWR report showing heavy activity of ‘shrink’ and ‘grow’ for shared pool and library cache(Let me check). This was a heavy OLTP environment (I would say uncontrolled environment). Believe me, we resolved some severe performance issues by un-setting SGA_TARGET.I think this facility is suitable only for controlled environments.

        You may also refer the topic described by Jonathan:
        http://jonathanlewis.wordpress.com/2006/12/04/resizing-the-sga/
        ————————————————–

        Disclaimer:
        The views expressed by me here is solely mine. It doesn’t re-present the views of my existing and previous company.

        Regards,
        Santosh Kumar

      • Hi Santosh,
        Hi Santosh,

        Thanks for your views.Apprciated.Let me know your views on the below (database 10g):-

        with “literals + histograms + equality prediacte in sql statement + cursor_sharing exact” –> New Parent cursor will be created everytime the SQL is executed
        with “literals + histograms + equality prediacte in sql statement + cursor_sharing similar” –> One Parent cursor will be created, with multiple version count (child cursor)
        with “literlas + no histogram + equality prediacte in sql statement + cursor_sharing similar” –> What will be its result???
        with “literlas + no histogram + equality prediacte in sql statement + cursor_sharing force” –> Reduces the number child cursor counts further, with one parent cursor.

        I am trying to analyze the above with respect to the space occupied in the shared pool and not the performance.

        Regards,
        Anand

        • Santosh Kumar
        • July 11th, 2010

        @@with “literlas + no histogram + equality prediacte in sql statement + cursor_sharing similar” –> What will be its result???

        Should behave like cursor_sharing=force. i.e. one plan will be used for all set of literals in the query.

    • Narendra
    • July 10th, 2010

    Anand,

    Nice blog entry.
    But I had to disagree on your list of conclusions. For me, there is only one conclusion:
    “Rollback the changes and force the development team to fix the actual issue of not using bind variables”.

    Yes, yes I know some will say this approach is not always feasible, but my experience is if you try to find workarounds (maybe due to so-called “management pressure”) or do not enforce it, the workaround tends to become a permanent solution and developers will do the same mistake again

    • Hi Narendra,

      Thanks for visiting my blog entry and i really appreciate your thoughts.

      Regards,
      Anand

  1. I think you should communicate with the dev team to use bind variables. with the parameter cursor_sharing=similar, you have more opportunity to meet oracle bugs, and it is also not the best method.

    the following two presentation are very good reference of Library Cache.
    http://www.juliandyke.com/Presentations/LibraryCacheInternals.ppt By JulianDyke
    http://www.perfvision.com/papers/10_libc.ppt by Kyle Hailey

    • Hi,

      Appreciate your views and thanks for the references :)

      Regards,
      Anand

  2. Hi Anand,

    why did you choose cursor_sharing to be SIMILAR instead of FORCE?

    I’m not saying that setting cursor_sharing to SIMILAR is incorrect – just curious why do you think that SIMILAR is better opinion…

    Thanks for answer and keep on good work.

    Regards,
    Marko

    • Hi Marko,

      Thanks for visiting the blog.

      FORCE may lead to unlucky sharing of execution plan.As per my thinking, its better to try SIMILAR before resorting to FORCE.

      Regards,
      Anand

    • harsha kb
    • August 10th, 2010

    Hi,

    I’m getting the ora 04031 error while starting the database itself. I’m unable to query the database for analysis.

    Also i tried with with increasing the SGA memory. Still i’m getting the error.

    I’m using Oracle 10g and windows 2003 enterprise 32 bit OS.

    Please help.

    Harsha

    • Hi Harsha,

      More details would be required.What is the actual error in the alert log?What is th SGA size?Is SGA_TARGET set?
      You can refer to metalink doc id :- 146599.1,839789.1, 554521.1

      Regards,
      Anand

  3. You can also check out my post on ora-4031. See if it helps :)

    http://askdba.org/weblog/2008/07/simplified-approach-to-resolve-ora-4031/

    -Amit

    • Hi Amit,

      Thanks for the link. :)

      Regards,
      Anand

    • lekhraj
    • December 16th, 2011

    Dear Anand,

    If I replace the all sga parameter configuration to SGA_TARGET then is it ok to solve ora-04031.

    Regards,
    Lekhraj

    • Hi Lekhraj,

      Setting SGA_TARGET might help to certain extent. Better would be ,make your applications use bind variables.

      Regards,
      Anand

  1. January 2nd, 2011

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 462 other followers

%d bloggers like this: