Path: news.easynews.com!newsfeed1.easynews.com!easynews.com!easynews!news.he.net!news-hog.berkeley.edu!ucberkeley!newsfeed.stanford.edu!postnews1.google.com!not-for-mail From: ydias@hotmail.com (dias) Newsgroups: comp.databases.oracle.server Subject: Re: Library cache latch contention Date: 3 Mar 2003 00:30:10 -0800 Organization: http://groups.google.com/ Lines: 100 Message-ID: <55a68b47.0303030030.4d4caebf@posting.google.com> References: <55a68b47.0303011427.12f689bc@posting.google.com> NNTP-Posting-Host: 194.2.91.222 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 8bit X-Trace: posting.google.com 1046680210 26055 127.0.0.1 (3 Mar 2003 08:30:10 GMT) X-Complaints-To: groups-abuse@google.com NNTP-Posting-Date: 3 Mar 2003 08:30:10 GMT Xref: newsfeed1.easynews.com comp.databases.oracle.server:178221 X-Received-Date: Mon, 03 Mar 2003 01:30:03 MST (news.easynews.com) Thanks Jonathan and Ricky, I'll check with Oracle Support. Dias "Jonathan Lewis" wrote in message news:... > Your results do look odd - after all, you can only > start accumulating sleeps after a miss, and with a > minimum sleep of 1/100 of a second (which is what > it used to be in v8 and below), you seem to be averaging > more than three hours of wait time every time you > miss a library cache latch (and that isn't allowing > for the roughly exponential increase in the length > of successive sleeps). > > I suspect an anomaly somewhere in the code where > Oracle 9 has an option to yield immediately (without > spinning) and use a much shorter sleep time. This type > of strategy change in the low-level code is likely to cause > all sorts of upsets and anomalies (or perhaps things that > look like anomalies) to appear in the migration from 8.1 > through 9.0 to 9.2 > > You could compare v$system_event with v$latch to > see if the number of 'latch free' events seems reasonable > compared to the number of sleeps in v$latch - and there > are a couple of micro-second time columns in v$latch, > v$latch_children, v$system_event, v$session_event > which you could cross-check for 'reasonableness'. And > there are a couple of 'max time' columns that you could > check in the event views. This MIGHT give you a clue > about whether the high sleep figures are genuine, or a bug > in the v$latch(_children) statistics. > > -- > Regards > > Jonathan Lewis > http://www.jlcomp.demon.co.uk > > Coming soon one-day tutorials: > Cost Based Optimisation > Trouble-shooting and Tuning > Indexing Strategies > (see http://www.jlcomp.demon.co.uk/tutorial.html ) > > ____UK_______March 19th > ____UK_______April 8th > ____UK_______April 22nd > > ____USA_(FL)_May 2nd > > > Next Seminar dates: > (see http://www.jlcomp.demon.co.uk/seminar.html ) > > ____USA_(CA, TX)_August > > > The Co-operative Oracle Users' FAQ > http://www.jlcomp.demon.co.uk/faq/ind_faq.html > > > "dias" wrote in message > news:55a68b47.0303011427.12f689bc@posting.google.com... > > Hi, > > > > I observed the following stats on a 9.0.1 (os is tru64) database > about > > "libary cache latch". The application is an industrial one with only > > small stored PL/SQL procedures. > > > > The first report: > > > > LATCH_NAME GETS MISSES HIT_RATIO SLEEPS > > SLEEPS/MISS > > ------------------ ----------- ----------- ----------- ----------- > > ----------- > > library cache 1021637 126 1 108618601 > > 862052.389 > > > > After setting session_cached_cursors = 100, and cursor_sharing = > FORCE > > (the application does not use bind variables): > > > > LATCH_NAME GETS MISSES HIT_RATIO SLEEPS > > SLEEPS/MISS > > ------------------ ----------- ----------- ----------- ----------- > > ----------- > > library cache 988150 34 1 31887719 > > 937874.088 > > > > There is no reloads. The v$sgastat reports 50 Mo of free memory, the > > shared pool is about 100 Mo. The time waited for latch free is very > > small (the average wait is 0). > > > > The question is, what causes a lot of sleeps over this latch ? > > > > Thanks