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 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 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@xxxxxxxxxxxxx]
Sent: Tuesday, June 27, 2006 7:47 PM
To: Schultz, Charles
Cc: duncan.lawie@xxxxxxxxxxxxxxxxx; Hallas, John, Tech Dev;
oracle-l@xxxxxxxxxxxxx
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
--
//www.freelists.org/webpage/oracle-l
