Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Join Date
    Jan 2003
    Location
    Torrance Ca 90210
    Posts
    5

    Unanswered: Shared Pool Error

    ORA-00604: error occurred at recursive SQL level 2
    ORA-04031: unable to allocate 744 bytes of shared
    memory ("shared pool","sele

    Thsi error occurs exactly once in 48 Hours. What could be the problem ?

  2. #2
    Join Date
    Mar 2002
    Location
    Ireland
    Posts
    181
    Hi Mohank,
    Has this just started happening?
    There isprobably a large query running that is causing this.
    Increase SHARED_POOL_SIZE in your init.ora.

    Also, are you running MTS (Multi-Threaded Server) - if so do you have any space allocated to the LARGE_POOL?

    Regards,
    Breen.

  3. #3
    Join Date
    Feb 2003
    Posts
    3

    Re: Shared Pool Error

    Originally posted by mohank01
    ORA-00604: error occurred at recursive SQL level 2
    ORA-04031: unable to allocate 744 bytes of shared
    memory ("shared pool","sele

    Thsi error occurs exactly once in 48 Hours. What could be the problem ?
    what is your Oracle version . if it is V 8.1.7 know that there is a bug regarding the "shared pool" management indeed the bug n1727717 has been detected by oracle and is corrected with the patch 2 of this version.
    you can solve temporarily this problem by increasing the size of the "shared pool" then stop/start the instance to take it into account (see the initXXX file to change it) but the problem will occur again in a longer term but will appear anyway. patch id : 17.
    good luck
    Stephane.

  4. #4
    Join Date
    Oct 2003
    Location
    Pune
    Posts
    59
    I am also facing the same problem in 9 i but I was not able to find a firm solution.
    We have increased the pool size but it again comes after a day or two.

    Cyrus

  5. #5
    Join Date
    Mar 2002
    Location
    Ireland
    Posts
    181
    Are you running in MTS mode?
    Do you have LARGE_POOL set?

    Rgs,
    Breen.

  6. #6
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    This is fragmentation of the SGA. As objects are "aged" out of the SGA
    they can leave "gaps" which fragments the SGA. You may need to
    increase the large_pool as well as the shared pool. You should look
    at the objects that have been "flushed" out of the SGA and their
    size. Look at "pinning" objects in the SGA (ones that have the highest
    number of reloads.....

    Attached script will show you the number of times objects have been
    reloaded and the number of times they have been executed. I usually
    "pin" these objects with a DB startup trigger for my clients....

    HTH
    Gregg
    Attached Files Attached Files

  7. #7
    Join Date
    Oct 2003
    Posts
    71
    Originally posted by gbrabham
    This is fragmentation of the SGA. As objects are "aged" out of the SGA
    they can leave "gaps" which fragments the SGA. You may need to
    increase the large_pool as well as the shared pool. You should look
    at the objects that have been "flushed" out of the SGA and their
    size. Look at "pinning" objects in the SGA (ones that have the highest
    number of reloads.....

    Attached script will show you the number of times objects have been
    reloaded and the number of times they have been executed. I usually
    "pin" these objects with a DB startup trigger for my clients....

    HTH
    Gregg
    Greg,

    We also have same proble. We are using Oracle 8.1.7 and even after increasing the size of shared pool, the problem comes after 4-5 days.
    I was trying to use the script which you ahve suggested but the first query doesnot run as it cannot find "x$kglob" object. Where hsould i run this query.
    If I see the trace files at the time of error, here is what I see:

    apps/oracle/admin/ISPHERES/bdump/isphprod_snp3_15861.trc
    Oracle8i Enterprise Edition Release 8.1.7.0.0 - 64bit Production
    With the Partitioning option
    JServer Release 8.1.7.0.0 - 64bit Production
    ORACLE_HOME = /apps/oracle/product/8.1.7
    System name: SunOS
    Node name: weezy
    Release: 5.8
    Version: Generic_108528-11
    Machine: sun4u
    Instance name: ISPHPROD
    Redo thread mounted by this instance: 1
    Oracle process number: 15
    Unix process pid: 15861, image: oracle@weezy (SNP3)

    *** SESSION ID36.26729) 2003-10-22 06:20:43.646
    *** 2003-10-22 06:20:43.646
    ORA-00604: error occurred at recursive SQL level 3
    ORA-04031: unable to allocate 4192 bytes of shared memory ("shared pool","PROCEDURE$","sga heap","state objects")
    *** SESSION ID36.26733) 2003-10-22 06:21:45.234
    *** 2003-10-22 06:21:45.234
    ORA-00604: error occurred at recursive SQL level 2
    ORA-04031: unable to allocate 4224 bytes of shared memory ("shared pool","JOB$","sga heap","library cache")
    *** SESSION ID36.26737) 2003-10-22 06:22:46.685
    *** 2003-10-22 06:22:46.685
    ORA-00604: error occurred at recursive SQL level 1
    ORA-04031: unable to allocate 4224 bytes of shared memory ("shared pool","unknown object","sga heap","library cache")
    *** SESSION ID36.26741) 2003-10-22 06:23:48.142
    *** 2003-10-22 06:23:48.142
    ORA-00604: error occurred at recursive SQL level 2
    ORA-04031: unable to allocate 4224 bytes of shared memory ("shared pool","JOB$","sga heap","library cache")
    *** SESSION ID36.26745) 2003-10-22 06:24:49.601
    *** 2003-10-22 06:24:49.601
    ORA-00604: error occurred at recursive SQL level 1
    ORA-04031: unable to allocate 4224 bytes of shared memory ("shared pool","unknown object","sga heap","library cache")
    *** SESSION ID36.26749) 2003-10-22 06:25:51.064
    *** 2003-10-22 06:25:51.064
    ORA-00604: error occurred at recursive SQL level 2
    ORA-04031: unable to allocate 4224 bytes of shared memory ("shared pool","JOB$","sga heap","library cache")
    *** SESSION ID36.26753) 2003-10-22 06:26:52.536
    *** 2003-10-22 06:26:52.535
    ORA-00604: error occurred at recursive SQL level 2
    ORA-04031: unable to allocate 4224 bytes of shared memory ("shared pool","JOB$","sga heap","library cache")
    *** SESSION ID36.26757) 2003-10-22 06:27:53.990
    *** 2003-10-22 06:27:53.989
    ORA-00604: error occurred at recursive SQL level 2
    ORA-04031: unable to allocate 4224 bytes of shared memory ("shared pool","JOB$","sga heap","library cache")
    *** SESSION ID36.26761) 2003-10-22 06:28:55.473
    "isphprod_snp3_15861.trc" 63 lines, 3379 characters

    What is the best way to find what causes this problem.

    Any help is highly appreciated

    Thanks
    Reema

  8. #8
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    This script should be run under SYS account. You should look at
    increasing the large_pool and maybe even the java pool... Based
    on the size of the objects that you are going to PIN in the SGA from
    the above script....

    There is also an undocumented parameter in 8.1.7 ...
    Put this in the INITxxx.ORA initialization file and restart the
    database... This comes from METALINK

    #### Work around for BAMIMA Buffer (4031) errors ####
    _db_handles_cached = 0

    I have a database startup trigger on some databases that will
    pin objects in the SGA upon DB Startup...

    HTH
    Gregg

  9. #9
    Join Date
    Oct 2003
    Posts
    71
    Originally posted by gbrabham
    This script should be run under SYS account. You should look at
    increasing the large_pool and maybe even the java pool... Based
    on the size of the objects that you are going to PIN in the SGA from
    the above script....

    There is also an undocumented parameter in 8.1.7 ...
    Put this in the INITxxx.ORA initialization file and restart the
    database... This comes from METALINK

    #### Work around for BAMIMA Buffer (4031) errors ####
    _db_handles_cached = 0

    I have a database startup trigger on some databases that will
    pin objects in the SGA upon DB Startup...

    HTH
    Gregg
    Thanks for your prompt reply.

    The first query doesnot return any rows.
    What is the implication of increasing Large_Pool size.
    Currently
    Shared_Pool = 500MB
    Larg_Pool = 600Kb
    Java_Pool = 20MB

    What does "_db_handles_cached =0" parameter in init<sid>.ora file does?? Can I use it in Oracle 8.1.7 version and do I need to startup the database after doing this change.

    The second query returned following output
    SQL> select substr(owner,1,10),substr(name,1,32), sharable_mem,loads,executions
    2 from v$db_object_cache
    3 where sharable_mem > 100
    4 and (type = 'PACKAGE' or type = 'PACKAGE BODY' or type = 'FUNCTION'
    5 or type = 'PROCEDURE')
    6 and kept = 'NO'
    7 order by owner,loads desc;

    SUBSTR(OWN SUBSTR(NAME,1,32) SHARABLE_MEM LOADS EXECUTIONS
    ---------- -------------------------------- ------------ ---------- ----------
    SYS STANDARD 307348 1 0
    SYS DBMS_STANDARD 29817 1 0
    SYS DBMS_OUTPUT 20623 1 1
    SYS DBMS_OUTPUT 7719 1 1
    SYS DBMS_APPLICATION_INFO 20713 1 10
    SYS DBMS_APPLICATION_INFO 4041 1 7


    Thanks
    Reema

  10. #10
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    I like to keep the large_pool approx: 10% of my shared_pool.
    This is where large objects are "pinned"...

    The second query shows the objects that are larger than 100kb
    (change the number if you want) that are currently in the SGA and
    the number of times they have been reloaded and executed...

    The 3rd query shows all objects (regardless of size) that have been
    reloaded into the SGA and the number of times they have been
    executed.


    --- 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;

    The _db_handles_cached =0 is an undocumented parameter for 8.1.7
    that will help with the problem.

    As objects are loaded in an out of the SGA, they leave "chucks" of
    the SGA. Other objects that have to be loaded MUST fill the "left chunk".
    If you have a lot of smaller objects that are getting loaded in and out
    of the SGA, then the SGA will become fragmented with little chunks...
    Remember... objects loaded into the SGA MUST have contiguous space
    in order to get loaded, or you will get errors as you are showing ...

    By "Pinning" larger objects, they will not get "aged" out of the SGA... Smaller objects that are executed numerous times and have been
    reloaded should be pinned to keep from having the "Small Chunks"

    HTH
    Gregg

  11. #11
    Join Date
    Oct 2003
    Posts
    71
    Originally posted by gbrabham
    I like to keep the large_pool approx: 10% of my shared_pool.
    This is where large objects are "pinned"...

    The second query shows the objects that are larger than 100kb
    (change the number if you want) that are currently in the SGA and
    the number of times they have been reloaded and executed...

    The 3rd query shows all objects (regardless of size) that have been
    reloaded into the SGA and the number of times they have been
    executed.
    --------------------
    Greg,

    If I add
    _db_handles_cached =0
    Large_pool size to 50Mb (10% of SHared Pool) as you have suggested, can there be any negative affects on the database.
    Do I have to restart database after changing these parameters, can I chnage them while db is running.
    are they any other parameters/ disk size etc that I have to check before increasing /adding these parameters.

    Appreciate your help
    Thanks
    Reema
    -------------------------------------------------------
    --- 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;

    The _db_handles_cached =0 is an undocumented parameter for 8.1.7
    that will help with the problem.

    As objects are loaded in an out of the SGA, they leave "chucks" of
    the SGA. Other objects that have to be loaded MUST fill the "left chunk".
    If you have a lot of smaller objects that are getting loaded in and out
    of the SGA, then the SGA will become fragmented with little chunks...
    Remember... objects loaded into the SGA MUST have contiguous space
    in order to get loaded, or you will get errors as you are showing ...

    By "Pinning" larger objects, they will not get "aged" out of the SGA... Smaller objects that are executed numerous times and have been
    reloaded should be pinned to keep from having the "Small Chunks"

    HTH
    Gregg

  12. #12
    Join Date
    Jun 2003
    Posts
    14
    I found this thread useing the search feature and it has been very helpful because this is my exact problem. Does anyone know any more about this? I am running version 8.1.7 which leads me to belive I have the bug. I don't really want to put the patch in. Will adding this to my initXX.ora fix the problem?

    #### Work around for BAMIMA Buffer (4031) errors ####
    _db_handles_cached = 0

  13. #13
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    I had this at a few of my client's sites.... By putting this in the INITxxx.ora's
    AND pinning "reusable" objects worked for them ... That's "for what it's worth"...

    HTH
    Gregg

  14. #14
    Join Date
    Jun 2003
    Posts
    14
    what if I dont have any reusable objects? I'm not useing any triggers or stored procedures or packages no pl/sql. I pretty must just send plain SQL through SQLplus to this server.

  15. #15
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    I would still look at the objects that have been reloaded using the script provided above... You will find that there are system objects that may
    be getting flushed out ...

    Gregg

Posting Permissions

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