RE: ora-04031 and x$ksmlru

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