100 percent miss in library cache
- From: "Kevin Lidh" <kevin.lidh@xxxxxxxxx>
- To: ORACLE-L <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 1 Mar 2006 08:21:29 -0700
I have a co-worker who is testing a large-ish database upgrade on HP-UX v2
from a 9.2.0.4 database to 10.2.0.1 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 10.1.0.2 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.
Kevin
Other related posts:
