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:[EMAIL PROTECTED]] > Reply To: [EMAIL PROTECTED] > 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" <[EMAIL PROTECTED]> > 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: [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: Tim Gorman > 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: Baker, Barbara 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).
