Re: ora - 4031 while inserting into a parent table

From: <DBarbour_at_austin.isd.tenet.edu>
Date: Thu, 30 Aug 2001 09:50:26 -0700
Message-ID: <F001.0037D270.20010830083557@fatcity.com>

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_at_subex       To:     Multiple recipients of list 
ORACLE-L <ORACLE-L_at_fatcity.com>  
                    group.com>              cc:                                        
                          
                    Sent by:                Subject:     ora - 4031 while inserting 
into a parent table          
                    root_at_fatcity.com                                                   
                          
                                                                                       
                          
                                                                                       
                          
                    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 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: ranganathk_at_subexgroup.com

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: ListGuru_at_fatcity.com (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:
INET: DBarbour_at_austin.isd.tenet.edu

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