RE: 100 percent miss in library cache
- From: "Guang Mei" <GMei@xxxxxx>
- To: <kevin.lidh@xxxxxxxxx>, "ORACLE-L" <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 1 Mar 2006 10:28:46 -0500
Are the parameters in init.ora file the same from <http://10.2.0.1/>
10.2.0.1 and <http://10.1.0.2/> 10.1.0.2 ?
Guang
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]On
Behalf Of Kevin Lidh
Sent: Wednesday, March 01, 2006 10:21 AM
To: ORACLE-L
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 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:
