Re: ora-04031 and x$ksmlru

I usually recommend letting HASH_AREA_SIZE default to (2*SORT_AREA_SIZE), but if HASH_AREA_SIZE hasn't been changed in the past couple days, then I'm sure that it's *not* the cause of your current issues, so please take that suggestion with a great big heaping grain o' salt...

...I'm still betting on some clever developer having recently discovered the pleasures of ALTER SESSION SET...

• Original Message ----- To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com> Sent: Thursday, May 16, 2002 12:48 PM

> NAME TYPE VALUE
> ----------------------------------- ------- ------------------------------
> sort_area_retained_size integer 0
> sort_area_size integer 2097152
> hash_area_size integer 20971520
>
> The developers might (very possibly) be using "alter session set", but not
> the regular users -- they're locked into that silly application stuff.
>
> Still trying to devour all the statistics I've accumulated.
>
> Thanks so much!
> Barb
>
>
> > ----------
> > From: Tim Gorman[SMTP:Tim_at_SageLogix.com]
> > Reply To: ORACLE-L_at_fatcity.com
> > Sent: Thursday, May 16, 2002 12:13 PM
> > To: Multiple recipients of list ORACLE-L
> > Subject: Re: ora-04031 and x$ksmlru
> >
> > 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...
> >
> > ----- Original Message -----
> > To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com>
> > Sent: Thursday, May 16, 2002 9:33 AM
> >
> >
> > >
> > > > 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).
> >
> --
> 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).