X$ksmsp (OSEE 10.2.0.2 on Solaris 8) 2006-06-28 - By Mark Strickland
A couple of months ago, Oracle Support sent me a query to run against
x$ksmsp in order to identify shared pool fragmentation. They assured me
that any problems with querying x$ tables were from earlier versions of
Oracle. The local technical sales rep also assured us that there should be
no issues. I was born in the morning but I wasn't born YESTERDAY morning,
so I was nervous about querying that table directly and, after testing the
query in a non-Production environment, verified that, not only did the query
hold the shared pool latch, but it took an hour for the query to run.
Couldn't log onto the database from another session. Could have been quite
painful in Production.
Incidently, we seem to have reached reasonable stability in our 10.1.0.3 RAC
environment. After suffering for months with instance crashes due to
ORA-04031 (See ORA-04031.ora-code.com), Oracle Support recommended that we set _lm_res_cache_cleanup=70.
We implemented that in early May and haven't had any crashes since. We do
still have a possible memory leak due to automatic statistics gathering
which shows up as a continually increasing value for MISCELLANEOUS in
V$SGASTAT for the shared pool. When it reaches 900-Mb (of 1300-Mb
shared_pool_size), we plan an off-hour bounce of that instance. Takes about
6 weeks for MISCELLANEOUS to reach 900-Mb. The other two instances in the
cluster don't seem to have the same problem. Those instances have been up
for almost 8 weeks now. Instances used to crash after 3 weeks on average.
Database stability is a wonderful thing.
Regards,
Mark Strickland
Next Online Technologies
Seattle, WA
On 6/28/06, Schultz, Charles <sac@(protected)> wrote:
>
> I looked at v$sgastat, but it is was too general. We have fragmentation
> issues (in the shared pool, I believe) and Oracle is saying that we have
> a potential memory leak (still in the diagnosis phase). Hence, I think
> the PGA and Buffer pool views are out, although I could be wrong. The
> ora-4031 (See ora-4031.ora-code.com) trace files are reporting errors on the following objects:
> kggfaAllocSeg
> kgghtInit
> kgghteInit
> qcdlgcd
> qcopCreateCol
> qcopCreateLog
> qcuAllocIdn
> qkshtQBAtomicAlloc
> qkxrMemAlloc
>
> Of course, one of the most confusing problems with this fragmentation
> issue is whether to decrease or increase the shared pool. Increasing the
> shared pool has the temporary affect of making the ora-4031 (See ora-4031.ora-code.com) errors
> disappear, but that seems to be a bad long-term solution, as decreasing
> the shared pool might actually be the better way to go. My one caveat
> with this approach (resizing the shared pool) ignores the root cause of
> the problem - if the fragmentation is avoidable, why not avoid it? I am
> still trying to learn more about this concept - even though I have read
> a lot (Tom Kyte, Jonathan Lewis, etc), the material is sinking in
> slowly. From talks I have had offline, this might be a case of
> contention on a shared pool heap latch - a requestor wants a certain
> size chunk and the latch for the size chunk is busy. My memory of the
> details might be fuzzy.
>
>
> I ran across note 367392.1, but all of our traces are from foreground
> processes, not background.
>
> Following note 146599.1, I peeked at V$SHARED_POOL_RESERVED but did not
> learn much (one size that has failed a number of times, 4200). Also,
> this note points to the x$ tables, hence my original question about
> x$ksmsp. If the performance is so bad and there are better alternatives,
> I am surprised that they are not listed here.
>
> And finally note 62143.1. I am still re-reading this one, as I still
> have much to learn in "tuning the shared pool". This is a good appendix
> for terms and offers various scripts, but none that I found to be very
> relevant.
>
> Other references:
> "Understanding Shared Pool Memory Structures" Russell Green, Sep 2005
> Oracle white paper
> Scripts from Alejandro Vargas' blog
>
> -- --Original Message-- --
> From: Mladen Gogala [mailto: gogala@(protected)]
> Sent: Tuesday, June 27, 2006 7:47 PM
> To: Schultz, Charles
> Cc: duncan.lawie@(protected); Hallas, John, Tech Dev;
> oracle-l@(protected)
> Subject: Re: X$ksmsp (OSEE 10.2.0.2 on Solaris 8)
>
>
> On 06/27/2006 10:30:11 AM, Schultz, Charles wrote:
> > What is the alternative to track down memory issues? Sure, one could
> > use DMA (Direct Memory Access), but I for one am not there yet. If
> > there is a better way to diagnose and resolve memory issues, I am all
> > ears (or rather, eyes *grin*).
> >
>
> Track what memory issues? Insufficient shared pool? Try with V$SGASTAT.
> PGA? Try with V$PROCESS_MEMORY. Buffer cache? Try with
> V$BUFFER_POOL_STATISTICS.
> What do you have in mind when you say "memory issues"? All those tables
> are well documented and stable.
>
> --
> Mladen Gogala
> http://www.mgogala.com
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>
A couple of months ago, Oracle Support sent me a query to run against x$ksmsp
in order to identify shared pool fragmentation. They assured me
that any problems with querying x$ tables were from earlier versions of Oracle.
The local technical sales rep also assured us that there should be no
issues. I was born in the morning but I wasn't born YESTERDAY morning, so
I was nervous about querying that table directly and, after testing the query
in a non-Production environment, verified that, not only did the query hold the
shared pool latch, but it took an hour for the query to run. Couldn't log
onto the database from another session. Could have been quite painful in
Production.
<br><br>Incidently, we seem to have reached reasonable stability in our <a href
="http://10.1.0.3">10.1.0.3</a> RAC environment. After suffering for
months with instance crashes due to ORA-04031 (See ORA-04031.ora-code.com), Oracle Support recommended that
we set _lm_res_cache_cleanup=70. We implemented that in early May and
haven't had any crashes since. We do still have a possible memory leak
due to automatic statistics gathering which shows up as a continually
increasing value for MISCELLANEOUS in V$SGASTAT for the shared pool. When
it reaches 900-Mb (of 1300-Mb shared_pool_size), we plan an off-hour bounce of
that instance. Takes about 6 weeks for MISCELLANEOUS to reach 900-Mb.
The other two instances in the cluster don't seem to have the
same problem. Those instances have been up for almost 8 weeks now.
Instances used to crash after 3 weeks on average. Database
stability is a wonderful thing.
<br><br>Regards,<br>Mark Strickland<br>Next Online Technologies<br>Seattle, WA
<br><br><br><div><span class="gmail_quote">On 6/28/06, <b class="gmail
_sendername">Schultz, Charles</b> <<a href="mailto:sac@(protected)" target=
"_blank" onclick="return top.js.OpenExtLink(window,event,this)">
sac@(protected)</a>> wrote:</span><blockquote class="gmail_quote" style=
"border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding
-left: 1ex;">
I looked at v$sgastat, but it is was too general. We have fragmentation<br
>issues (in the shared pool, I believe) and Oracle is saying that we have<br>a
potential memory leak (still in the diagnosis phase). Hence, I think
<br>the PGA and Buffer pool views are out, although I could be wrong. The<br
>ora-4031 (See ora-4031.ora-code.com) trace files are reporting errors on the following objects:<br
>kggfaAllocSeg<br>kgghtInit<br>kgghteInit<br>qcdlgcd<br>qcopCreateCol<br>
qcopCreateLog
<br>qcuAllocIdn<br>qkshtQBAtomicAlloc<br>qkxrMemAlloc<br><br>Of course, one of
the most confusing problems with this fragmentation<br>issue is whether to
decrease or increase the shared pool. Increasing the<br>shared pool has the
temporary affect of making the ora-4031 (See ora-4031.ora-code.com) errors
<br>disappear, but that seems to be a bad long-term solution, as decreasing<br
>the shared pool might actually be the better way to go. My one caveat<br>with
this approach (resizing the shared pool) ignores the root cause of
<br>the problem - if the fragmentation is avoidable, why not avoid it? I am<br
>still trying to learn more about this concept - even though I have read<br>a
lot (Tom Kyte, Jonathan Lewis, etc), the material is sinking in<br>
slowly. From talks I have had offline, this might be a case of<br>contention on
a shared pool heap latch - a requestor wants a certain<br>size chunk and the
latch for the size chunk is busy. My memory of the<br>details might be fuzzy.
<br><br><br>I ran across note 367392.1, but all of our traces are from
foreground<br>processes, not background.<br><br>Following note 146599.1, I
peeked at V$SHARED_POOL_RESERVED but did not<br>learn much (one size that has
failed a number of times, 4200). Also,
<br>this note points to the x$ tables, hence my original question about<br>x
$ksmsp. If the performance is so bad and there are better alternatives,<br>I am
surprised that they are not listed here.<br><br>And finally note
62143.1. I am still re-reading this one, as I still<br>have much to learn in
"tuning the shared pool". This is a good appendix<br>for terms and
offers various scripts, but none that I found to be very<br>relevant.
<br><br>Other references:<br>"Understanding Shared Pool Memory Structures
" Russell Green, Sep 2005<br>Oracle white paper<br>Scripts from Alejandro
Vargas' blog<br><br>-- --Original Message-- --<br>From: Mladen Gogala [mailto:
<a href="mailto:gogala@(protected)" target="_blank" onclick="return top.js
.OpenExtLink(window,event,this)">gogala@(protected)</a>]<br>Sent: Tuesday,
June 27, 2006 7:47 PM<br>To: Schultz, Charles<br>Cc: <a href="mailto:duncan
.lawie@(protected)" target="_blank" onclick="return top.js.OpenExtLink
(window,event,this)">
duncan.lawie@(protected)</a>; Hallas, John, Tech Dev;
<br><a href="mailto:oracle-l@(protected)" target="_blank" onclick="return top
.js.OpenExtLink(window,event,this)">oracle-l@(protected)</a><br>Subject: Re: X
$ksmsp (OSEE <a href="http://10.2.0.2" target="_blank" onclick="return top.js
.OpenExtLink(window,event,this)">
10.2.0.2</a> on Solaris 8)<br><br><br>On 06/27/2006 10:30:11 AM, Schultz,
Charles wrote:<br>> What is the alternative to track down memory issues?
Sure, one could
<br>> use DMA (Direct Memory Access), but I for one am not there yet. If<br>
> there is a better way to diagnose and resolve memory issues, I am all<br>
> ears (or rather, eyes *grin*).<br>><br><br>Track what memory issues?
Insufficient shared pool? Try with V$SGASTAT.
<br>PGA? Try with V$PROCESS_MEMORY. Buffer cache? Try with<br>V$BUFFER_POOL
_STATISTICS.<br>What do you have in mind when you say "memory issues"?
All those tables<br>are well documented and stable.<br><br>--<br>
Mladen Gogala<br><a href="http://www.mgogala.com" target="_blank" onclick=
"return top.js.OpenExtLink(window,event,this)">http://www.mgogala.com</a><br><br
>--<br><a href="http://www.freelists.org/webpage/oracle-l" target="_blank"
onclick="return top.js.OpenExtLink(window,event,this)">
http://www.freelists.org/webpage/oracle-l</a><br><br><br></blockquote></div><br>
|
|
