Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

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

100 percent miss in library cache

From: Kevin Lidh <kevin.lidh_at_gmail.com>
Date: Wed, 1 Mar 2006 08:21:29 -0700
Message-ID: <cb4807f0603010721sbfb4d63nd36e9d34ae345c8f@mail.gmail.com>


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:21:29 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US