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

Thread: Ora-04031 Unable To Allocate 316 Bytes Of Shared Memory

  1. #1
    Join Date
    Apr 2001
    Location
    Ludhiana (Punjab) India
    Posts
    36

    Question Ora-04031 Unable To Allocate 316 Bytes Of Shared Memory

    HELLO ORACLE GURU'S

    I AM FACING A STRANGE PROBLEM I MY DATABASE I HAVE 2 VIEWS :
    PENDING_ANX_IV_POST_VIEW & PENDING_ANX_V_POST_VIEW.
    WHEN EVER I USE TO RUN THESE VIEWS THEY BOTH GIVE SAM ERROR :

    ORA-04031: unable to allocate 316 bytes of shared memory
    ("large pool","unknown object","cursor work he","kdiixs1 : kdipc")

    HOWEVER WHEN I VIEW SHARED POOL AND LARGE POOL THE STATISTICS IS :


    SGA CACHE STATISTICS
    ********************
    SQL Cache Hit rate = 99.73
    Dict Cache Hit rate = 92.03
    Buffer Cache Hit rate = 99.93
    Redo Log space requests = 1
    Total SGA = 292.69

    INIT.ORA SETTING
    ****************
    Shared Pool Size = 149380352 Bytes
    DB Block Buffer = 47202 Blocks
    Log Buffer = 32768 Bytes



    Main SGA Areas NAME SUM(BYTES)
    -------------------- -------------------- ----------------
    DB Buffer Cache db_block_buffers 386,678,784
    Shared Pool shared pool 158,447,628
    Large Pool large pool 40,405,560
    Redo Log Buffer log_buffer 65,536
    Fixed SGA fixed_sga 65,484
    ----------------
    585,662,992


    Shared Pool Areas NAME SUM(BYTES)
    -------------------- -------------------- ----------------
    Shared Pool free memory 133,477,124
    Shared Pool miscellaneous 14,100,744
    Shared Pool sql area 5,939,240
    Shared Pool library cache 4,290,964
    Shared Pool dictionary cache 639,556
    ----------------
    sum 158,447,628




    I AM ATTACHING SOURCE CODE OF BOTH VIEWS. WHEN I USE TO RUN THESE VIEWS BY MAKING PART OF IT. THEY RUN SUCCESFULLY. I AM NOT GETTING WHAT IS THE PROBLEM.

    PLEASE HELP WHAT IS THE REASON.

    REGARDS

    GURPREET SINGH SETHI
    Attached Files Attached Files

  2. #2
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938

    Re: Ora-04031 Unable To Allocate 316 Bytes Of Shared Memory

    What do you mean by "RUN A VIEW"??
    Oracle Certified Master
    Oracle Certified Professional 6i,8i,9i,10g,11g,12c
    email: ocp_9i@yahoo.com

  3. #3
    Join Date
    Apr 2003
    Location
    South Carolina
    Posts
    148
    It is probably because the SGA has become fragmented to the point
    that there is not enough contiguous space for the object to be loaded.
    As objects are aged out of the SGA, they leave "holes". Objects have to have contigous space in order to be loaded into SGA... You need to
    1. reboot the database in order to clear the problem (flushing shared pool doesn't do it)
    2. Look at the procs, functions, etc that are loaded into the SGA, their size and the number of executions and the reloads of them ... Find the problem ones and PIN them in 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;


    --- Objects that have been reloaded ---

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


    HTH
    Gregg

  4. #4
    Join Date
    May 2001
    Location
    San Francisco, California
    Posts
    511
    Increase your large pool. If that does not fix your problem, set this event in your init.ora:

    event = "4031 trace name errorstack level 3"

    It will create a dump file when the error occurs next. Send the dump to oracle support.
    Remember the Golden Rule - He who has the gold makes the rules!
    ===================
    Kris109
    Ph.D., OCP 8i, 9i, 10g, 11g DBA

  5. #5
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    Your large pool is not large enough.
    Jeff Hunter

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