ora-04031 and x$ksmlru

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