Re: cache buffers chains & library cache --- latch, latchagain...
- From: Tim Gorman <tim@xxxxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Sat, 24 Apr 2004 10:22:36 -0600
"Gets" and "misses" are not indications of a problem, merely that latches
are being used.
"Sleeps" are more significant, indicative that latch waits are taking long
enough to require preemptive waits, but even so there is nothing in the
information you have presented which indicates that latch contention is even
a minor problem, let alone a major problem.
Before investigating the details in V$LATCH and related views, first
determine if the "latch free" wait-event is accounting for a significant
portion of your database's total response time, either over time or for a
particular time period. If it isn't, then delving into the details is
simply not worthwhile.
Please go to http://www.oraperf.com/whitepapers.html and read about the YAPP
method and how to use it with STATSPACK and the YAPP postprocessor. Please
first find out if latch contention is a problem before you begin to consider
it as such...
More importantly, learn how to use SQL trace, preferably using the O'Reilly
book "Optimizing Oracle Performance" by Holt and Millsap or using any of the
excellent white papers at http://www.hotsos.com. If there is latch
contention, you are not going to find the cause by looking at aggregate
statistics. If you wanted to hunt for problems with latches, you could look
for active sessions experiencing high values for "latch free" in the
V$SESSION_EVENT view and then use SQL trace on those sessions...
But, instead of assuming problems with latches, best to let the database
tell you first...
on 4/24/04 8:36 AM, syed jaffar hussain at sjaffarhussain@xxxxxxxxxxx wrote:
> Hi list,
>
> My database is up since december 2003. However, when I go through
> v$latch, I found the following;
>
> NAME GETS MISSES SLEEPS
> ------------------------------ ---------- ----------
> ----------
> session allocation 1197361058 152407 7480
> multiblock read objects 1209057328 1532778 41137
> cache buffers lru chain 2791819365 7612195 269427 ===>
> session idle bit 4929270638 87527 3590
> library cache 6719774102 2139530 937599 ====>
> cache buffers chains 7.6325E+10 14700803 934118 ====>
> redo writing 110939333 616464 75353
> messages 161204253 259719 22385
> checkpoint queue latch 352667859 73646 4821
> shared pool 418028301 58660 12236
> row cache objects 458785060 4877123 94779
> redo allocation 468537523 125368 9327
> enqueues 678664061 26255 3184
>
>
> And when I dig at v$latch_children I found a lot of misses for
> NAME GETS MISSES SLEEPS
> ------------------------------ ---------- ---------- ----------
> library cache 1145632268 500990 289291
> library cache 1864524856 524985 73132
> library cache 2557348906 753180 507015
> library cache 706338241 232889 41144
> library cache 445934611 127500 27031
> cache buffers chains 50657614 8439 492
> cache buffers chains 41067021 6318 427
> cache buffers chains 43140486 7272 474
> cache buffers chains 44849453 6217 450
> cache buffers chains 45883393 5701 407
> cache buffers chains 50139764 6847 556
> cache buffers chains 56838229 9668 625
> cache buffers chains 52779420 10468 653
> cache buffers chains 54897378 6835 466
> cache buffers chains 61131696 12187 560
>
> (hell a lot of rows found there for these two)
>
> How can I solve this problem? Is it very bad for performance point of
> view, having such big values for those latches?
>
> Best Regards,
>
> Syed Jaffar Hussain
> Sr Oracle DBA
> Sejel Technology Ltd.
> +966 2 6064971 x 324 4
> ----------------------------------------------------------------
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> ----------------------------------------------------------------
> To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
> put 'unsubscribe' in the subject line.
> --
> Archives are at //www.freelists.org/archives/oracle-l/
> FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
> -----------------------------------------------------------------
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
- » Re: cache buffers chains & library cache --- latch, latchagain...
