DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: how to solve : ORA-04031

  1. #1
    Join Date
    Dec 2001
    Posts
    120

    how to solve : ORA-04031

    hi all,

    I am getting this error whenever i am running a big query which has couple of inline views in it. I have recently loaded oracle9i and this problem started occurring after that. I know that I have to increase the shared memory size and the only way to do that is to increase the SGA size. is there a way to find out what should be the approximate size of SGA so that this error never appears in future? pls guide me once again to get rid of this problem for once and for all.
    -----------------the error message----------------------

    SQL error occurred while retrieving data. Error: ORA-04031: unable t
    o allocate 71416 bytes of shared memory ("large pool" "unknown object" "hash-join subh" "kllcqc:kl
    lcqslt")
    --------------------------------------------------------

    thanks a lot!

    Parijat

  2. #2
    Join Date
    Apr 2003
    Posts
    32
    1) This error indicates that Oracle is unable to allocate memory from the ‘large’ pool.To determine the number of free bytes in the ‘large’ pool execute the following SQL:

    SELECT NAME, SUM(BYTES) FROM V$SGASTAT WHERE POOL='LARGE POOL' GROUP BY ROLLUP (NAME);
    To resolve the problem, consider increasing the value for ‘LARGE_POOL_SIZE’.

    2) You can issue a 'ALTER SYSTEM FLUSH SHARED_POOL' then identify the main SQLs/packages and pin them.

    3) Please also increase SHARED_POOL_RESERVED_SIZE to more than size of the biggest PL-SQL you use.

    4) Some one suggested the following in other forum:

    Determine if the ORA-04031 was a result of fragmentation in the library cache or in the shared pool reserved space by issuing the following query:

    SELECT free_space,
    avg_free_size,
    used_space,
    avg_used_size,
    request_failures,
    last_failure_size
    FROM v$shared_pool_reserved;

    The ORA-04031 is a result of lack of contiguous space in the shared pool reserved space if:

    REQUEST_FAILURES is > 0 and
    LAST_FAILURE_SIZE is > SHARED_POOL_RESERVED_MIN_ALLOC.

    To resolve this consider increasing SHARED_POOL_RESERVED_MIN_ALLOC to lower the number of objects being cached into the shared pool reserved space and
    increase SHARED_POOL_RESERVED_SIZE and SHARED_POOL_SIZE to increase the available memory in the shared pool reserved space.

    The ORA-04031 is a result of lack of contiguous space in the library cache if:

    REQUEST_FAILURES is > 0 and
    LAST_FAILURE_SIZE is < SHARED_POOL_RESERVED_MIN_ALLOC

    or

    REQUEST_FAILURES is 0 and LAST_FAILURE_SIZE is < SHARED_POOL_RESERVED_MIN_ALLOC

    The first step would be to consider lowering SHARED_POOL_RESERVED_MIN_ALLOC to put more objects into the shared pool reserved space and increase
    SHARED_POOL_SIZE.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width