Forum Stats

  • 3,726,767 Users
  • 2,245,253 Discussions
  • 7,852,386 Comments

Discussions

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

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

Varma
Varma Member Posts: 139
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.

Best Answer

  • VenkatB
    VenkatB Member Posts: 568
    Accepted Answer
    >
    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

Answers

  • sb92075
    sb92075 Member Posts: 42,196
    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.  
    sb92075
  • CKPT
    CKPT Member Posts: 9,955 Silver Crown
    Check this note, Applicable for all the versions.

    *Master Note for Diagnosing ORA-4031 [ID 1088239.1]*
    CKPT
  • Shivananda Rao
    Shivananda Rao Member Posts: 2,513 Gold Trophy
    Try increasing the size of the shared_pool_size
    Shivananda Rao
  • VenkatB
    VenkatB Member Posts: 568
    Accepted Answer
    >
    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
  • Varma
    Varma Member Posts: 139
This discussion has been closed.