Re: x$ksmlru
From: Tanel Poder <tanel_at_poderc.com>
Date: Sat, 10 Jul 2010 13:32:18 +0800
Message-ID: <AANLkTikovMJ0iU8C97OJ4F1UJiyrGs2EQm6RjOVF2lQZ_at_mail.gmail.com>
kkslc* functions deal with literal values. While bind variable values are physically located in UGA area, the literal values extracted from SQL statement text are stored in SGA. Do you have lots of (or long) literal values in your queries so every new query needs to load 26kB for the storage of literal values?
Date: Sat, 10 Jul 2010 13:32:18 +0800
Message-ID: <AANLkTikovMJ0iU8C97OJ4F1UJiyrGs2EQm6RjOVF2lQZ_at_mail.gmail.com>
kkslc* functions deal with literal values. While bind variable values are physically located in UGA area, the literal values extracted from SQL statement text are stored in SGA. Do you have lots of (or long) literal values in your queries so every new query needs to load 26kB for the storage of literal values?
Note that the flushers you see in X$KSMLRU now may be the victims of someone else eating up all memory earlier and now all the flushes happen because shared pool is full (or fragmented).
X$KSMSP or shared pool heapdumps would allow to troubleshoot ORA-4031 most accurately, but they may completely hang your database, so may be not usable in your environment.
-- Tanel Poder http://tech.e2sn.com http://blog.tanelpoder.com On Sat, Jul 10, 2010 at 3:24 AM, Jay Hostetter <hostetter.jay_at_gmail.com>wrote:Received on Sat Jul 10 2010 - 00:32:18 CDT
> We have a 9.2.0.4 database running on Windows (32-bit). 9.2.0.4 has some
> major memory bugs. Until we can get this database upgraded, I am trying to
> troubleshoot ORA-4031 errors. The database runs into these errors about
> every 30 days. I periodically capture x$ksmlru into a table in order to
> look for patterns regarding objects flushing from the SGA. Is there any
> documentaiton on wht the comments mean in KSMLRCOM? Some examples are
> below, sorted by KSMLRSIZ, followed by samples sorted by KSMLRNUM. I'd like
> to get an understanding of "kkslcr - unsafe po".
> BTW, the application does *not* make good use of bind variables.
>
> Thank you,
> Jay
>
>
> TS KSMLRCOM KSMLRSIZ KSMLRNUM
> --------- -------------------- ---------- ----------
> 14-MAY-10 trace buf hdr xten 35352 8
> 13-MAY-10 NETWORK BUFFER 32812 16
> 13-MAY-10 kkslcr - unsafe po 26284 8
> 13-MAY-10 kkslcr - unsafe po 26284 16
> 14-MAY-10 kkslcr - unsafe po 26284 16
> 07-MAY-10 kkslcr - unsafe po 26284 16
> 10-MAY-10 kkslcr - unsafe po 26284 8
> 11-MAY-10 kkslcr - unsafe po 26284 16
> 13-MAY-10 kkslcr - unsafe po 26284 24
> 17-MAY-10 kkslcr - unsafe po 26284 16
> 17-MAY-10 kkslcr - unsafe po 26284 8
> 13-MAY-10 kkslcr - unsafe po 26284 8
> 11-MAY-10 kkslcr - unsafe po 26284 8
> 10-MAY-10 kkslcr - unsafe po 26284 8
> 07-MAY-10 kkslcr - unsafe po 26284 24
>
>
> TS KSMLRCOM KSMLRSIZ KSMLRNUM
> --------- -------------------- ---------- ----------
> 14-MAY-10 BAMIMA: Bam Buffer 4132 208
> 09-JUN-10 obj stat memor 4228 208
> 17-MAY-10 object level s 4240 152
> 13-MAY-10 BAMIMA: Bam Buffer 4132 120
> 02-JUN-10 obj stat memor 4228 120
> 02-JUN-10 object level s 4240 94
> 14-MAY-10 obj stat memor 4228 88
> 07-JUN-10 object level s 4240 80
> 07-JUN-10 obj stat memor 4228 80
> 14-MAY-10 oacdef info 4320 72
> 02-JUN-10 oacdef info 4320 72
> 07-JUN-10 qry_text : qcpisqt 4340 72
> 07-JUN-10 oacdef info 4320 64
> 11-MAY-10 BAMIMA: Bam Buffer 4132 56
> 09-JUN-10 oacdef info 4248 56
> 02-JUN-10 oacdef info 4248 56
> 09-JUN-10 object level s 4240 56
>
>
-- http://www.freelists.org/webpage/oracle-l