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: