Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> RE: 100 percent miss in library cache

RE: 100 percent miss in library cache

From: Johnson, George <GJohnson_at_GAM.COM>
Date: Wed, 1 Mar 2006 16:45:55 -0000
Message-ID: <>


            We definitely found that we were getting away with murder on our 9.2.x DB, with regards bad SQL. When we upgraded to 10.2, we uncovered some real hornets nests of unbound SQL statements, which worked without problem under 9.x but killed the 10g DB. The libcache was the first component that got slaughtered post upgrade. The blurb states that 10g will be faster no questions asked, we spent 3 weeks tweaking our post upgrade tester DB to get things back up to an acceptable level. We mainly doubled the memory params, collected full stats over the entire DB and flipped a few small schema's over to CURSOR_SHARING=FORCE on login triggers, while we get devs to fix things properly. The 10g DB appears to me, to be a lot less tolerant of bad code, which is a good thing.  

	-----Original Message-----
	From: [] On Behalf Of Kevin Lidh
	Sent: 01 Mar 2006 15:21
	Subject: 100 percent miss in library cache
	I have a co-worker who is testing a large-ish database upgrade on HP-UX v2 from a database to which just came out.  She and the application people doing the test ran a 40-minute 4000 user test and saw that performance ran good for about 10 minutes and then all of a sudden things just started slowing down to an unaccepable level.  The top event was: 
	Event=>  latch: library cache
	Waits=>  3,503,648
	Time (s)=>  563,560
	Avg wait (ms)=> 161
	%Total Call Time=> 77.4
	I had her send me the STATSPACK report and in the Latch Activity section I saw this: 
	                         Get  Pct        Pin        Pct               Invali-
	Namespace           Requests  Miss     Requests     Miss     Reloads  dations
	--------------- ------------ ------ -------------- ------ ---------- -------- 
	BODY                   1,188    0.4          1,396    0.4          0        0
	CLUSTER                   16    6.3             30    3.3          0        0
	INDEX                     37   48.6            128   15.6           2        0
	SQL AREA             142,617  100.0      3,975,409    6.2        140        9
	TABLE/PROCEDURE        1,192   14.7      2,018,879   -0.3        158        0
	TRIGGER                   72    0.0          68,984   -0.0          0        0
	The Pct Miss for INDEX was 48.6 and SQL AREA is 100.  The shared pool size is 2 GB.  I told her, for curiosity's sake, to increase the shared pool to 3.2 GB and the result was that it took a little longer to hit the performance dive but the result in STATSPACK was about the same: 
	                         Get  Pct        Pin        Pct               Invali-
	Namespace           Requests  Miss     Requests     Miss     Reloads  dations
	--------------- ------------ ------ -------------- ------ ---------- -------- 
	BODY                   7,313    0.0          7,503    0.0          0        0
	CLUSTER                   10    0.0             28    0.0          0        0
	INDEX                      2   50.0             19    5.3           0        0
	SQL AREA             140,904  100.0      2,500,634   11.0         25        0
	TABLE/PROCEDURE        5,404    4.6      1,476,379   -0.1          8        0
	TRIGGER                  174    1.7          79,836   -0.0          0        0
	They had tested this before with an upgrade to with the same database and same scripts and the results were a Pct Miss of 19% for SQL AREA.  They did the typical stuff like looking for bind variable issues but I knew from my experience with the application that binds weren't the issue.  Oracle recommended setting cursor sharing to "FORCE" but that didn't change anything.  Has anybody seen a situation where the Pct Miss was 100% not due to non-bind SQL?  They're going to submit a TAR but I was just curious. 

This message contains confidential information and is intended only for the individual or entity named. If you are not the named addressee you should not disseminate, distribute or copy this e-mail. Please notify the sender immediately by e-mail if you have received this e-mail by mistake and delete this e-mail from your system. E-mail transmission cannot be guaranteed to be secure or error-free as information could be intercepted, corrupted, lost, destroyed, arrive late or incomplete, or contain viruses. The sender therefore does not accept liability for any errors or omissions in the contents of this message which arise as a result of e-mail transmission. If verification is required please request a hard-copy version. This message is provided for informational purposes and should not be construed as an invitation or offer to buy or sell any securities or related financial instruments.
GAM operates in many jurisdictions and is regulated or licensed in those jurisdictions as required.
Received on Wed Mar 01 2006 - 10:45:55 CST

Original text of this message