| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: cache buffers chains & library cache --- latch, latch again...
"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_at_hotmail.com 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_at_freelists.org
> put 'unsubscribe' in the subject line.
> --
> Archives are at http://www.freelists.org/archives/oracle-l/
> FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
> -----------------------------------------------------------------
-- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html -----------------------------------------------------------------Received on Sat Apr 24 2004 - 11:21:03 CDT
 |
 |