7 Replies Latest reply: Jan 4, 2013 2:27 AM by 884659 RSS

    How to troubleshoot ORA-04031 error?

    884659
      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
          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
            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
              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
                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
                  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
                    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
                      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