5 Replies Latest reply on Jul 18, 2012 10:19 AM by Varma

    ORA-04031: unable to allocate 4160 bytes of shared memory

    Varma
      Hi all,
      i am working on HP-UX B.11.23 U 9000/800 and
      BANNER
      ----------------------------------------------------------------
      Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production
      PL/SQL Release 9.2.0.8.0 - Production
      CORE 9.2.0.8.0 Production
      TNS for HPUX: Version 9.2.0.8.0 - Production
      NLSRTL Version 9.2.0.8.0 - Production

      i getting error
      ORA-00604: error occurred at recursive SQL level 2
      ORA-04031: unable to allocate 4160 bytes of shared memory ("shared pool","JOB$","sga heap(1,0)","kglsim heap")

      i already increased the shared pool size,still i am facing this problem.can any one help me on this
      and it's a development database.
        • 1. Re: ORA-04031: unable to allocate 4160 bytes of shared memory
          sb92075
          841517 wrote:
          Hi all,
          i am working on HP-UX B.11.23 U 9000/800 and
          BANNER
          ----------------------------------------------------------------
          Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production
          PL/SQL Release 9.2.0.8.0 - Production
          CORE 9.2.0.8.0 Production
          TNS for HPUX: Version 9.2.0.8.0 - Production
          NLSRTL Version 9.2.0.8.0 - Production

          i getting error
          ORA-00604: error occurred at recursive SQL level 2
          ORA-04031: unable to allocate 4160 bytes of shared memory ("shared pool","JOB$","sga heap(1,0)","kglsim heap")

          i already increased the shared pool size,still i am facing this problem.can any one help me on this
          and it's a development database.
          04031, 00000, "unable to allocate %s bytes of shared memory (\"%s\",\"%s\",\"%s\",\"%s\")"
          // *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.  
          1 person found this helpful
          • 2. Re: ORA-04031: unable to allocate 4160 bytes of shared memory
            CKPT
            Check this note, Applicable for all the versions.

            *Master Note for Diagnosing ORA-4031 [ID 1088239.1]*
            1 person found this helpful
            • 3. Re: ORA-04031: unable to allocate 4160 bytes of shared memory
              Shivananda Rao
              Try increasing the size of the shared_pool_size
              1 person found this helpful
              • 4. Re: ORA-04031: unable to allocate 4160 bytes of shared memory
                VenkatB
                >
                i getting error
                ORA-00604: error occurred at recursive SQL level 2
                ORA-04031: unable to allocate 4160 bytes of shared memory ("shared pool","JOB$","sga heap(1,0)","kglsim heap")

                i already increased the shared pool size,still i am facing this problem.can any one help me on this
                and it's a development database.
                >

                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. But looks like you have already done it and it's still throwing ORA-4031. So that cannot be a solution.

                Check your application and see if your code is parsed extensively. This will happen where you don't bind the variables resulting in Oracle having to hard parse every SQL every time. So if your application is not using bind variables, you will have to fix it before it's too late. Parsing is the most expensive part in a SQL query execution.

                On another note, as you are on 9i, you will definitely have turned on SGA_TARGET which means automatic memory management. In this case Oracle will distribute memory from SGA central pool to different components wherever demanded. Check V$SGA_TARGET_ADVICE and also check V$SGA_DYNAMIC_COMPONENTS and make sure your SGA memory is not too low. If they are all fine, then your only option is to make sure that you don't do extensive parsing unnecessarily.

                Regards