Re: ora - 4031 while inserting into a parent table
KQLs heap is part of dictionary cache. However since 8.0.X it is allocated in the library cache. Therefore you should insist with increasing shared pool and monitoring the amount needed for that heap from v$subcache: select name,heap_sz,heap_aloc,heap_used from v$subcache where heap_num=10; Also look in the v$sgastat for the free memory and v$shared_pool_reserved for statistics of the shared pool reserved. Finally if it isn't get solved, call Oracle support because there are bugs asociated with 4031 errors. Regards. --- Ranganath K [EMAIL PROTECTED] wrote: Dear DBA Gurus, I am getting the following error while inserting into a schema's parent table which is being referred by 42 tables of another schema. ORA-04031: unable to allocate 2196 bytes of shared memory (shared pool,RA_VOLUME_PLAN_POOL,KQLS heap,KQLS MEM BLOCK); How do I resolve this? I increased the shared_pool_size from 15mb to 30mb and shared_pool_reserved_size from 1mb to 5mb but it didn't solve the problem. I also set the _db_cached_cursors parameter to 0 and bounced the database but in vain. BTW I am using oracle 8.1.6 enterprise edition on Sun Solaris 2.7. Any help in this regard will be very much appreciated. TIA and Regards, Ranganath DISCLAIMER: This correspondence is confidential and intended for the named recipient(s) only. If you are not the named recipient and receive this correspondence in error, you must not copy, distribute or take any action in reliance on it and you should delete it from your system and notify the sender immediately. Unless otherwise stated, any views or opinions expressed are solely those of the author and do not represent those of Subex Systems Limited. www.subexgroup.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ranganath K INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). = Eng. Christian Trassens Senior DBA Systems Engineer [EMAIL PROTECTED] [EMAIL PROTECTED] Phone : 541149816062 __ Do You Yahoo!? Get email alerts NEW webcam video instant messaging with Yahoo! Messenger http://im.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Christian Trassens INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
ora - 4031 while inserting into a parent table
Dear DBA Gurus, I am getting the following error while inserting into a schema's parent table which is being referred by 42 tables of another schema. ORA-04031: unable to allocate 2196 bytes of shared memory (shared pool,RA_VOLUME_PLAN_POOL,KQLS heap,KQLS MEM BLOCK); How do I resolve this? I increased the shared_pool_size from 15mb to 30mb and shared_pool_reserved_size from 1mb to 5mb but it didn't solve the problem. I also set the _db_cached_cursors parameter to 0 and bounced the database but in vain. BTW I am using oracle 8.1.6 enterprise edition on Sun Solaris 2.7. Any help in this regard will be very much appreciated. TIA and Regards, Ranganath DISCLAIMER: This correspondence is confidential and intended for the named recipient(s) only. If you are not the named recipient and receive this correspondence in error, you must not copy, distribute or take any action in reliance on it and you should delete it from your system and notify the sender immediately. Unless otherwise stated, any views or opinions expressed are solely those of the author and do not represent those of Subex Systems Limited. www.subexgroup.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ranganath K INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: ora - 4031 while inserting into a parent table
Why is your shared pool so tiny? -Original Message- Sent: Thursday, August 30, 2001 10:37 AM To: Multiple recipients of list ORACLE-L Dear DBA Gurus, I am getting the following error while inserting into a schema's parent table which is being referred by 42 tables of another schema. ORA-04031: unable to allocate 2196 bytes of shared memory (shared pool,RA_VOLUME_PLAN_POOL,KQLS heap,KQLS MEM BLOCK); How do I resolve this? I increased the shared_pool_size from 15mb to 30mb and shared_pool_reserved_size from 1mb to 5mb but it didn't solve the problem. I also set the _db_cached_cursors parameter to 0 and bounced the database but in vain. BTW I am using oracle 8.1.6 enterprise edition on Sun Solaris 2.7. Any help in this regard will be very much appreciated. TIA and Regards, Ranganath DISCLAIMER: This correspondence is confidential and intended for the named recipient(s) only. If you are not the named recipient and receive this correspondence in error, you must not copy, distribute or take any action in reliance on it and you should delete it from your system and notify the sender immediately. Unless otherwise stated, any views or opinions expressed are solely those of the author and do not represent those of Subex Systems Limited. www.subexgroup.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ranganath K INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Kevin Kostyszyn INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: ora - 4031 while inserting into a parent table
Raganath, Sounds like you got some bad SQL operating. Usually the error you are seeing is caused not by the shared pool being too small (although if it's too small for you're trying to do, nothing will help), but rather by excessive fragmentation making the requisite amount of continuous free space unavailable. Your remark that this is an insert involving 42 tables from another DB sort of reinforces my suspicions. One of the things I found out (from this list last year) was that you need to commit across DB links after DML to free memory. Make sure you're using bind variables. Make sure you're sharing SQL. Try using fully qualified table names. You could try flushing the shared pool to see if this resolves your problem, but you run the risk of losing sequences unless you've specified they should not be flushed using dbms_shared_pool.keep. The kqls heap message might also indicate library cache problems. Oracle suggests using the following to determine which parameters you need to address in tuning: First 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. Note that _shared_pool_reserved_min_alloc is a hidden parameter in 8i. If all else fails, increase the size of the shared pool again. Hope some of this helps. David A. Barbour Oracle DBA, OCP AISD 512-414-1002 Ranganath K ranganathk@subex To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] group.com cc: Sent by:Subject: ora - 4031 while inserting into a parent table [EMAIL PROTECTED] 08/30/2001 09:37 AM Please respond to ORACLE-L Dear DBA Gurus, I am getting the following error while inserting into a schema's parent table which is being referred by 42 tables of another schema. ORA-04031: unable to allocate 2196 bytes of shared memory (shared pool,RA_VOLUME_PLAN_POOL,KQLS heap,KQLS MEM BLOCK); How do I resolve this? I increased the shared_pool_size from 15mb to 30mb and shared_pool_reserved_size from 1mb to 5mb but it didn't solve the problem. I also set the _db_cached_cursors parameter to 0 and bounced the database but in vain. BTW I am using oracle 8.1.6 enterprise edition on Sun Solaris 2.7. Any help in this regard will be very much appreciated. TIA and Regards, Ranganath DISCLAIMER: This correspondence is confidential and intended for the named recipient(s) only. If you are not the named recipient and receive this correspondence
