1 2 Previous Next 17 Replies Latest reply on Jul 18, 2012 12:40 PM by jey84

    shared memory

    jey84
      Oracle9i Enterprise Edition Release 9.0.1.1.1 - Production
      PL/SQL Release 9.0.1.1.1 - Production
      CORE 9.0.1.1.1 Production
      TNS for 32-bit Windows: Version 9.0.1.1.0 - Production
      NLSRTL Version 9.0.1.1.1 - Production

      the following error happened frequently in my server. I searched in google but no help.

      any one can help to solve problem?

      ERROR:
      ORA-04031: unbale to allocate 34824 bytes of shared memory("shared pool",
      *"unknown object","sga heap(1,0)","trace buf hdr xtend")*
        • 1. Re: shared memory
          Fran
          Error: ORA-04031: unable to allocate string bytes of shared memory ("string","string","string","string")

          Cause: More shared memory is needed than was allocated in the shared pool.

          Action: 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.
          • 2. Re: shared memory
            Samuel G. Cristobal
            hi,

            look answer in: http://www.dba-oracle.com/t_ora_04031_unable_to_allocate_shared_memory.htm

            --sgc                                                                                                                                                                                                                   
            • 3. Re: shared memory
              Osama_Mustafa
              ORA-04031: unable to allocate 4032 bytes of shared memory

              also read
              http://dbarajabaskar.blogspot.com/2011/08/ora-04031-unable-to-allocate-65560.html

              OSama ...
              • 4. Re: shared memory
                Varma
                Hi jey84,


                ORA-04031:unable to allocate string bytes of shared memory ("string","string","string","string")
                Cause: More shared memory is needed than was allocated in the shared pool.
                Action: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 INIT.ORA parameters "shared_pool_reserved_size" and "shared_pool_size". If the large pool is out of memory, increase the INIT.ORA parameter "large_pool_size".

                Your SGA has become fragmented ...
                Your should attempt to pin some of the objects in memory that
                are getting reloaded multiple times... See script below to view
                the reloads and sizes of objects...

                You also may need to look at resizing your SGA

                To determine what large PL/SQL objects are currently loaded in the shared pool
                and are not marked 'kept' and therefore may cause a problem, execute the following:

                select substr(owner,1,10),substr(name,1,32), sharable_mem,loads,executions
                from v$db_object_cache
                where sharable_mem > 100
                and (type = 'PACKAGE' or type = 'PACKAGE BODY' or type = 'FUNCTION'
                or type = 'PROCEDURE')
                and kept = 'NO'
                order by owner,loads desc;

                also refer the link below:

                ORA-04031:unable to allocate 140 bytes of shared memory.
                http://www.dba-oracle.com/sf_ora_04031_unable_to_allocate_string_bytes_of_shared_memory.htm
                • 5. Re: shared memory
                  Billy~Verreynne
                  Please do not share URLs to 3rd party commercial web sites - that is a violation of ONT's Terms of Use that states:
                  <i>You agree that you will not Share any Content that: .. (j) contains a solicitation of funds, goods or services, or <b>promotes or advertises goods or services</b>;</i>

                  Also, sharing a URL you've obviously picked up from the 1st couple of hits from Google, is not what assisting people on OTN is about. If you cannot actually do some work and effort in providing assistance, beyond sharing a URL you've found on Google, you should not bother responding to a problem IMO.
                  • 6. Re: shared memory
                    Billy~Verreynne
                    jey84 wrote:
                    Oracle9i Enterprise Edition Release 9.0.1.1.1 - Production
                    This is a very old and unsupported version of Oracle. It is also a very buggy version of Oracle. If you cannot upgrade to 11gr2 for some reason, you must at least consider in patching this buggy version to the last 9i patch that was released.
                    the following error happened frequently in my server. I searched in google but no help.
                    any one can help to solve problem?
                    ERROR:
                    ORA-04031: unbale to allocate 34824 bytes of shared memory("shared pool",
                    *"unknown object","sga heap(1,0)","trace buf hdr xtend")*
                    The Shared Pool is used to store cursors. All SQL and all PL/SQL anonymous blocks are parsed and stored as cursors. The Shared Pool is designed for shareable cursors.

                    A cursor is like a program - it is executable code. The Shared Pool allows a single copy of such a cursor "program" to exist, and be executed by 100's of different Oracle sessions. This is similar to the concept of a DLL on Windows (or shared object library on Unix/Linux). The executable code (the DLL) is loaded once in memory. Any number of applications can then call the DLL and use it - with no additional overheads of needing multiple copies of the executable code to be loaded into memory.

                    For a cursor to be reusable and shareable, it needs to use bind variables. This is one of the most critical and fundamental concepts of using Oracle. Without bind variables, a cursor cannot be effectively shared. For example, instead of reusing the very same INSERT cursor to insert a 100,000 invoices, a 100,000 distinct INSERT cursors are created - as the INSERT SQL statement does not use bind variables.

                    A 100,000 INSERT cursors will cause Shared Pool fragmentation, increase CPU utilisation, and cause the error you are seeing.

                    Of course, there are other reasons too for this error - but SQL cursors not using bind variables is more often than not, the primary cause for the error.

                    The very worse thing to do, is to increase the size of the Shared Pool. That moves the brick wall a few metres away, allowing processes to run even faster into it.

                    The correct approach is to fix application code - all SQL and all PL/SQL anonymous calls should make use of bind variables.

                    One can also address the symptom - by forcing Oracle to "fix" these shoddy SQL statements by injecting bind variables into them (a feature called forcing cursor sharing).

                    So the first thing to do is to identify what is causing the error. Is it because of application code not using SQL with bind variables? Have a look at the Shared Pool (via v$ views) to determine that.
                    • 7. Re: shared memory
                      jey84
                      HAI

                      sga_max_size big integer 119828432

                      i ran the following query
                      select substr(owner,1,10),substr(name,1,32), sharable_mem,loads,executions
                      from v$db_object_cache
                      where sharable_mem > 100
                      and (type = 'PACKAGE' or type = 'PACKAGE BODY' or type = 'FUNCTION'
                      or type = 'PROCEDURE')
                      and kept = 'NO'
                      order by owner,loads desc;

                      but how to find allotted sga and balance sga memory?
                      • 8. Re: shared memory
                        jey84
                        thanks billy

                        where i can get the patch for 9i?

                        can you post the link?
                        • 9. Re: shared memory
                          Varma
                          jey84 wrote:
                          HAI

                          sga_max_size big integer 119828432

                          i ran the following query
                          select substr(owner,1,10),substr(name,1,32), sharable_mem,loads,executions
                          from v$db_object_cache
                          where sharable_mem > 100
                          and (type = 'PACKAGE' or type = 'PACKAGE BODY' or type = 'FUNCTION'
                          or type = 'PROCEDURE')
                          and kept = 'NO'
                          order by owner,loads desc;

                          but how to find allotted sga and balance sga memory?
                          SQL> show parameter shared_pool_size

                          NAME TYPE VALUE
                          ------------------------------------ ----------- ------------------------------
                          shared_pool_size big integer 25165824

                          SQL> select sum(bytes) from v$sgastat where pool='shared pool';

                          SUM(BYTES)
                          ----------
                          268435456

                          SQL> select pool,name,bytes from v$sgastat where name='free memory' and pool='shared pool';

                          POOL NAME BYTES
                          ----------- -------------------------- ----------
                          shared pool free memory 65181796
                          *********** ----------
                          sum 65181796
                          • 10. Re: shared memory
                            jey84
                            hai

                            Total sga 46137344 bytes
                            free sga 15468312 bytes

                            so when the free sga becomes zero, the shred memory error occured? isn't it?
                            • 11. Re: shared memory
                              Fran
                              Please, before post you must know what are you question:
                              http://docs.oracle.com/cd/B28359_01/server.111/b28318/memory.htm
                              • 12. Re: shared memory
                                no

                                -------------
                                Sybrand Bakker
                                Senior Oracle DBA
                                • 13. Re: shared memory
                                  jey84
                                  hai billy

                                  Line: -----
                                  Heading 2: h2.
                                  A 100,000 INSERT cursors will cause Shared Pool fragmentation, increase CPU utilisation, and cause the error you are seeing.
                                  Heading 2: h2.
                                  Line: -----

                                  You are correct. In package, i insert 50,000 records using cursors. So only that occurred I think so.

                                  Is there any other way?

                                  thank you for your valuable response.
                                  • 14. Re: shared memory
                                    Varma
                                    jey84 wrote:
                                    hai

                                    Total sga 46137344 bytes
                                    free sga 15468312 bytes

                                    so when the free sga becomes zero, the shred memory error occured? isn't it?
                                    Hi,

                                    Actually no,Increasing the shared pool blindly will only postpone the inevitable. If your shared pool was really too small, you might be able to fix it by adding more memory to Shared pool.

                                    check the below links:

                                    ORA-04031: unable to allocate
                                    ORA-04031: unable to allocate 4160 bytes of shared memory
                                    ORA-04031: unable to allocate 59408 bytes of shared memory
                                    1 person found this helpful
                                    1 2 Previous Next