Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: ora-04031 and x$ksmlru

Re: ora-04031 and x$ksmlru

From: Tim Gorman <Tim_at_SageLogix.com>
Date: Thu, 16 May 2002 10:13:35 -0800
Message-ID: <F001.004629D6.20020516101335@fatcity.com>


Thanks to the wonderful search capabilities that Steve Adams has installed on his website at www.ixora.com.au, the following page has some more information about the X$KSMLRU fixed-table (http://www.zoftware.org/tuning/tune_shared_pool.html#fixed_table)...

I did an "advanced search" on MetaLink for "kllcqc", making sure to check the checkbox for "Bug Database" -- quite a few bugs appeared (for what they are worth). One of them (#2324210) is against 9.0.1.3 on Solaris, but the error message looks remarkably like yours even so. Like you, they are using MTS. They indicate that the settings for SORT_AREA_SIZE and HASH_AREA_SIZE are too large for the Shared Pool, hence the ORA-04031. The solution is to reduce SORT_AREA_SIZE and HASH_AREA_SIZE...

What are the settings for SORT_AREA_SIZE and HASH_AREA_SIZE here? Is it possible that the users may be using ALTER SESSION SET to set their own "custom" values for these parameters? I think this statement should appear in the V$SQL or V$SQLAREA if they are using it. This would possibly explain the sudden (and violent) onset of these symptoms...

>
> > Oracle 8.0.5
> > Solaris 2.6
> > shared_pool_reserved_min_alloc 5K
> > shared_pool_reserved_size 6656000
> > shared_pool_size 133120000
> >
> > total sga size is 597 megs
> >
> >
> > I'm fighting a particularly difficult ora-04031 error. The error can be
> > reproduced easily with several queries, including this one:
> > select a.agreement from advdb.ad a, advdb.pub p
> > where p.adno=a.adno and p.vno=a.vno;
> > ERROR:
> > ORA-04031: unable to allocate 340032 bytes of shared memory ("shared
> > pool","unknown object","cursor work he","kllcqc:kllcqslt")
> >
> > In reviewing metalink article 146599.1, it says that I can determine
> > "allocations in the shared pool that cause other objects in the shared
> > pool to be aged out. This fixed table can be used to identify what is
> > causing the large allocation"
> >
> > This query (select * from x$ksmlru where ksmlrsiz>0) returned this:
> >
> >
> > ADDR INDX INST_ID KSMLRCOM KSMLRSIZ
KSMLRNUM
>
> -------- ---------- ---------- -------------------- ---------- ----------
> > KSMLRHON KSMLROHV KSMLRSES
> > -------------------------------- ---------- --------
> > 800002B8 0 1 sort area 4152
8
> > 0 A42C4048
> >
> > 800002FC 1 1 kafco : qkacol 4292
1032
> > insert into pub (adno,pubno,... 1730627729 A427B7E0
> >
> > 80000340 2 1 kllcqc:kllcqslt 324100
13311
> > 0 A438EC84
> >
> > 80000384 3 1 kllcqc:kllcqslt 326124
9590
> > 0 A433A828
> >
> > 800003C8 4 1 kllcqc:kllcqslt 376908
6326
> > 0 A438EC84
> >
> > 8000040C 5 1 kllcqc:kllcqslt 384268
10731
> > 0 A433A828
> >
> > I notice several references in the column KSMLRCOM to kllcq:kllcqslt,
> > which matches information from the error message. However, I have no
idea
> > what that means.
> >
> > Can the information from this column in some way help me figure out what
> > the problem is?
> >
> > Since my request_failures is 41 and my last_failure_size 384,268 and
> > shared_pool_reserved_min_alloc is 5k, I believe I need to increase
> > shared_pool_size. I'm currently begging for permission to bounce the
> > database, but the timing isn't great . . .
> >
> > I've flushed shared_pool several times,to no avail.
> >
> > Anything I can do till I can bounce the database??
> >
> > Thanks for any help!
> >
> > Barb
> >
> >
> >
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Baker, Barbara
> INET: bbaker_at_denvernewspaperagency.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: Tim Gorman
  INET: Tim_at_SageLogix.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).
Received on Thu May 16 2002 - 13:13:35 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US