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 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:
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).
