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