ORA- 4031 – A Case Study

Today I will be taking up one ORA-4031 issue we faced on one of our client database. Please note that SQL statements mentioned below have been changed and does not reveal any confidential information.

Our client was facing ORA-4031 on a 9.2 database.This was a new Database which had gone Live recently. Following error messages were recorded in alert log.

ORA-00604: error occurred at recursive SQL level 1
ORA-04031: unable to allocate 4200 bytes of shared memory ("shared pool","select    owner#,name,namespace...","sga heap(1,0)","library cache")

Initial shared pool at time of failure was 160 Mb. After the failure the shared_pool_size was increased to 1 Gb. We did not have any other information and were asked to perform RCA for the same. Luckily we had Statspack configured on the server, so we took a report for the duration when the error occurred.

              Snap Id     Snap Time      Sessions Curs/Sess Comment
            --------- ------------------ -------- --------- -------------------
Begin Snap:     1000 15-Apr-08 06:32:06  #######       4.6
  End Snap:     1001 15-Apr-0806:53:41  #######       4.6
   Elapsed:               21.58 (mins)

Cache Sizes (end)
~~~~~~~~~~~~~~~~~
Buffer Cache:     2,048M      Std Block Size:          8K
Shared Pool Size:       160M          Log Buffer:     32,768K

Load Profile
~~~~~~~~~~~~                Per Second       Per Transaction
                           ---------------       ---------------
    Redo size:              2,459.98              2,911.95
    Logical reads:              6,447.76              7,632.40
    Block changes:                  8.31                  9.83
    Physical reads:              3,642.28              4,311.47
    Physical writes:                  0.80                  0.95
    User calls:                106.51                126.08
    <strong>Parses:                151.48                179.31</strong>
    <strong>Hard parses:                 11.08                 13.12</strong>
    Sorts:                 91.50                108.31
    Logons:                  1.41                  1.67
    Executes:                283.09                335.10
    Transactions:                  0.84

We can clearly see that lot of hard parses were occurring at the time of error.

Library Cache Activity for DB: TESTDB1  Instance: TESTDB1
 Snaps: 1000 -1001->"Pct Misses"  should be very low

                         Get  Pct        Pin        Pct               Invali-
Namespace           Requests  Miss     Requests     Miss     Reloads  dations
--------------- ------------ ------ -------------- ------ ---------- --------
BODY                   1,564    1.9          2,903   62.9        469        0
CLUSTER                6,827    0.0          9,011    0.1          0        0
INDEX                  9,376   62.8          9,375   63.7          5        0
SQL AREA              77,058   11.0        442,341    5.2      <strong>6,723 </strong>       0
TABLE/PROCEDURE       72,495   10.4        135,173   24.2      <strong>8,291</strong>        0
TRIGGER                2,288   14.9          2,288   49.5        218        0
          -------------------------------------------------------------

Also looking at information from V$LIBRARYCACHE, we can see that there were lot of reloads happening for SQL AREA and TABLE/PROCEDURE Namespace. This gives indication that Shared Pool is not appropriately sized. So we kind of expected this to be a shared pool sizing issue.

But after increasing the shared_pool_size to 1 Gb, we saw that memory for “sql area” was increasing. So as to avoid further ORA-4031, we started flushing the shared pool when the free memory reached to 200M.

NAME                                  BYTES
    -------------------------- ----------------
 <strong>free memory                     597,154,376</strong>  &lt; -large amount of free, was done after flushing shared pool
<strong>sql area                        315,365,096</strong> &lt; - High value
library cache                    82,229,616
miscellaneous                    81,476,336
gcs resources                    39,331,928
gcs shadows                      26,133,184

Whenever I observe high memory usage for Sqlarea, I run following sql statements.

1) 10g

<strong><span style="color: #3366ff;">SQL&gt; select sa.sql_text,sa.version_count ,ss.*from v$sqlarea sa,v$sql_shared_cursor ss where<span> </span>sa.address=ss.address and sa.version_count &gt; 50 order by sa.version_count ;</span></strong>

8i/9i

<strong><span style="color: #3366ff;">select sa.sql_text,sa.version_count ,ss.*from v$sqlarea sa,v$sql_shared_cursor ss<span> </span>where sa.address=ss.KGLHDPAR<span> </span>and sa.version_count &gt; 50<span> </span>order by sa.version_count ;</span></strong>

– This one is to find if child cursors are being generated. To know about child cursors, refer to following Metalink note

Note 296377.1 – Handling and resolving unshared cursors/large version_counts

We did not get any SQL in this category.

2) Use script from Asktom website to find if application is using Bind variables. You can find ,more information by clicking here

This returned quite a few statements

    INSERT INTO EMP (columnname, columnname, columnname, columnname, columnname, columnname, columnname, columnname, columnname, columnname, columnname ,columnname) VALUES ('#', TO_DATE('#','#'), @, '#',
    @, @, @, '#', @, @, @,     @, @, @, @, @)          <strong>1948</strong>

    DELETE FROM EMP WHERE EMPID=@          <strong>1976</strong>

    INSERT INTO EMP (columnname, columnname, columnname, columnname) VALUES (@, @, '#',    @, NULL , @)          <strong>1976</strong>

    DELETE FROM EMP WHERE TYPE=@ AND EMPID=@          <strong>1976</strong>

     INSERT INTO DEPT VALUES (@, '#', '#', TO_DATE('#','#'), SYSDATE)
          <strong>2012</strong>

    DELETE FROM DEPT WHERE DEPTNO=@ AND DEPT_ID=@ AND MGR=@ AND SAL='#'          <strong>2375</strong>

    INSERT INTO SALARY (columnname, columnname, columnname, columnname, columnname, columnname, columnname) VA LUES (@, @, @, '#', '#', @, TO_DATE('#','#'), TO_DATE('#','#'), @, @)          <strong>2377</strong>

We can clearly see that statements not being shared and thus causing the high memory usage in shared pool.

Each sql statement in shared pool will be occupying some memory. If the sql are similar and differ only in literal, then they should be shared as otherwise they will occupy lot of extra space. By not sharing the statements, we are wasting space in shared pool and causing shared pool fragmentation.

Solution again is to use Bind variables or CURSOR_SHARING.

Tags: databaseora-4031oracle