RE: 100 percent miss in library cache
- From: "Johnson, George" <GJohnson@xxxxxxx>
- To: <kevin.lidh@xxxxxxxxx>, "ORACLE-L" <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 1 Mar 2006 16:45:55 -0000
We definitely found that we were getting away with murder on our
9.2.x DB, with regards bad SQL. When we upgraded to 10.2, we uncovered some
real hornets nests of unbound SQL statements, which worked without problem
under 9.x but killed the 10g DB. The libcache was the first component that got
slaughtered post upgrade. The blurb states that 10g will be faster no questions
asked, we spent 3 weeks tweaking our post upgrade tester DB to get things back
up to an acceptable level. We mainly doubled the memory params, collected full
stats over the entire DB and flipped a few small schema's over to
CURSOR_SHARING=FORCE on login triggers, while we get devs to fix things
properly. The 10g DB appears to me, to be a lot less tolerant of bad code,
which is a good thing.
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Kevin Lidh
Sent: 01 Mar 2006 15:21
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
****************************************************************************
This message contains confidential information and is intended only
for the individual or entity named. If you are not the named addressee
you should not disseminate, distribute or copy this e-mail.
Please notify the sender immediately by e-mail if you have received
this e-mail by mistake and delete this e-mail from your system.
E-mail transmission cannot be guaranteed to be secure or error-free
as information could be intercepted, corrupted, lost, destroyed, arrive
late or incomplete, or contain viruses. The sender therefore does not
accept liability for any errors or omissions in the contents of this
message which arise as a result of e-mail transmission.
If verification is required please request a hard-copy version.
This message is provided for informational purposes and should not
be construed as an invitation or offer to buy or sell any securities or
related financial instruments.
GAM operates in many jurisdictions and is
regulated or licensed in those jurisdictions as required.
****************************************************************************
Other related posts:
