This discussion is archived
7 Replies Latest reply: Jan 4, 2013 12:27 AM by 884659 RSS

How to troubleshoot ORA-04031 error?

884659 Newbie
Currently Being Moderated
I'm getting ORA-04031 errors such as the this one:

ORA-04031: unable to allocate 264 bytes of shared memory ("shared pool","unknown object","PCUR^a9c5b70f","kglob")

Here is the server's information:

OS: RHEL
MEMORY: 48 GB

There is another database on the same server.

Here is the information about the database which has the ORA-04031 errors:

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE     11.2.0.1.0     Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

Database size (sum of datafiles) is about 2 GB.
SGA_TARGET = 0
SGA_MAX_SIZE = 224M -- Since SGA_TARGET = 0, does SGA_MAX_SIZE still control the maximum size of SGA?
SHARED_POOL_SIZE = 128M
SHARED_POOL_RESERVED_SIZE = 6710886
LOG_BUFFER = 4890624
LARGE_POOL_SIZE = 0
DB_CACHE_SIZE = 64M
DB_BLOCK_SIZE = 8192
JAVA_POOL_SIZE = 24M
SESSION_CACHED_CURSORS = 50
STATISTICS_LEVEL = TYPICAL
MEMORY_TARGET = 0
MEMORY_MAX_TARGET = 0
CPU_COUNT = 16

SQL AREA has 57% GETHITRATIO, 39939 RELOADS, and 1885 INVALIDATIONS.

Should I just increase SHARED_POOL_SIZE?

Thanks for any help.
  • 1. Re: How to troubleshoot ORA-04031 error?
    JohnWatson Guru
    Currently Being Moderated
    I would look at the advisor views v$shared_pool_advice and v$db_cache_advice, and set the parameters accordingly. But in general, you have configured your instance with a tiny memory footprint.
  • 2. Re: How to troubleshoot ORA-04031 error?
    Fran Guru
    Currently Being Moderated
    Error: ORA-04031: unable to allocate string bytes of shared memory ("string","string","string","string")
    
    Causa: More shared memory is needed than was allocated in the shared pool.
    
    Acción: If the shared pool is out of memory, either use the DBMS_SHARED_ POOL package to pin large packages, reduce your use of shared memory, or increase the amount of available shared memory by increasing the value of the initialization parameters SHARED_POOL_RESERVED_SIZE and SHARED_ POOL_SIZE. If the large pool is out of memory, increase the initialization parameter LARGE_POOL_SIZE.
    Increase your SHARED_POOL_SIZE parameter and bounce the database.
  • 3. Re: How to troubleshoot ORA-04031 error?
    Osama_Mustafa Oracle ACE
    Currently Being Moderated
    Refer to
    Master Note for Diagnosing ORA-4031 [ID 1088239.1]
    How can we investigate ORA-04031 error if limited diagnostics are provided with 11g? [ID 1223833.1]
  • 4. Re: How to troubleshoot ORA-04031 error?
    884659 Newbie
    Currently Being Moderated
    select * from v$shared_pool_advice yields the following result:

    SHARED_POOL_SIZE_FOR_ESTIMATE SHARED_POOL_SIZE_FACTOR ESTD_LC_SIZE ESTD_LC_MEMORY_OBJECTS ESTD_LC_TIME_SAVED ESTD_LC_TIME_SAVED_FACTOR ESTD_LC_LOAD_TIME ESTD_LC_LOAD_TIME_FACTOR ESTD_LC_MEMORY_OBJECT_HITS
    ----------------------------- ----------------------- ---------------------- ---------------------- ---------------------- ------------------------- ---------------------- ------------------------ --------------------------
    112 0.875 3 184 21698 0.993 1073 1.1676 818916
    128 1 19 1053 21852 1 919 1 3913927
    144 1.125 35 1917 22458 1.0277 313 0.3406 4049674
    160 1.25 51 2745 22598 1.0341 173 0.1882 4095321
    176 1.375 66 3308 22683 1.038 88 0.0958 4127276
    192 1.5 79 3695 22756 1.0414 15 0.0163 4154839
    208 1.625 95 4171 22812 1.0439 1 0.0011 4173600
    224 1.75 109 4657 22853 1.0458 1 0.0011 4187012
    240 1.875 125 5032 22885 1.0473 1 0.0011 4198483
    256 2 139 5418 22915 1.0486 1 0.0011 4207445

    10 rows selected

    select * from v$db_cache_advice yields no row.

    I don't know how to interpret the v$shared_pool_advice result.
  • 5. Re: How to troubleshoot ORA-04031 error?
    sb92075 Guru
    Currently Being Moderated
    is your post or mine more understandable?
    SHARED_POOL_SIZE_FOR_ESTIMATE SHARED_POOL_SIZE_FACTOR ESTD_LC_SIZE ESTD_LC_MEMORY_OBJECTS ESTD_LC_TIME_SAVED ESTD_LC_TIME_SAVED_FACTOR ESTD_LC_LOAD_TIME ESTD_LC_LOAD_TIME_FACTOR ESTD_LC_MEMORY_OBJECT_HITS
    ----------------------------- ----------------------- ------------ ---------------------- ------------------ ------------------------- ----------------- ------------------------ --------------------------
                              124                   .7561            6                    526             342198                      .991              3331                  16.0918                     705799
                              144                    .878           26                   2018             343203                     .9939              2326                  11.2367                    2162766
                              164                       1           46                   3297             345322                         1               207                        1                    2177631
                              184                   1.122           66                   4698             345433                    1.0003                96                    .4638                    2179067
                              204                  1.2439           86                   6164             345438                    1.0003                91                    .4396                    2179314
                              224                  1.3659          101                   7094             345440                    1.0003                89                      .43                    2179379
                              244                  1.4878          104                   7324             345440                    1.0003                89                      .43                    2179379
                              264                  1.6098          104                   7324             345440                    1.0003                89                      .43                    2179379
                              284                  1.7317          104                   7324             345440                    1.0003                89                      .43                    2179379
                              304                  1.8537          104                   7324             345440                    1.0003                89                      .43                    2179379
                              324                  1.9756          104                   7324             345440                    1.0003                89                      .43                    2179379
                              344                  2.0976          104                   7324             345440                    1.0003                89                      .43                    2179379
    
    12 rows selected.
    
    SQL> 
  • 6. Re: How to troubleshoot ORA-04031 error?
    Maran Viswarayar Pro
    Currently Being Moderated
    From a very high level and with the amount of memory you have in your system and from the Shared_pool advice you can try increasing your SHARED_POOL_SIZE to 200 and restart the DB Increase the SGA_MAX to say 300MB...

    These are just rough assumptions...
  • 7. Re: How to troubleshoot ORA-04031 error?
    884659 Newbie
    Currently Being Moderated
    Yours is more understandable. I just learn how to use the code tag today.
    SHARED_POOL_SIZE_FOR_ESTIMATE SHARED_POOL_SIZE_FACTOR ESTD_LC_SIZE           ESTD_LC_MEMORY_OBJECTS ESTD_LC_TIME_SAVED     ESTD_LC_TIME_SAVED_FACTOR ESTD_LC_LOAD_TIME      ESTD_LC_LOAD_TIME_FACTOR ESTD_LC_MEMORY_OBJECT_HITS 
    ----------------------------- ----------------------- ---------------------- ---------------------- ---------------------- ------------------------- ---------------------- ------------------------ -------------------------- 
    112                           0.875                   3                      175                    25402                  0.9938                    1168                   1.1576                   843019                     
    128                           1                       19                     1022                   25561                  1                         1009                   1                        4375791                    
    144                           1.125                   34                     1684                   26254                  1.0271                    316                    0.3132                   4530670                    
    160                           1.25                    50                     2396                   26407                  1.0331                    163                    0.1615                   4581019                    
    176                           1.375                   66                     3119                   26501                  1.0368                    69                     0.0684                   4615507                    
    192                           1.5                     82                     3978                   26578                  1.0398                    1                      0.001                    4644726                    
    208                           1.625                   98                     4662                   26638                  1.0421                    1                      0.001                    4665101                    
    224                           1.75                    114                    5441                   26682                  1.0439                    1                      0.001                    4679919                    
    240                           1.875                   130                    6207                   26718                  1.0453                    1                      0.001                    4693133                    
    256                           2                       146                    6993                   26751                  1.0466                    1                      0.001                    4703568                    
    
     10 rows selected 
    Edited by: 881656 on Jan 4, 2013 12:02 AM

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points