RE: 100 percent miss in library cache

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_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org]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

--

http://www.freelists.org/webpage/oracle-l Received on Wed Mar 01 2006 - 09:28:46 CST