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_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] 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.
--
http://www.freelists.org/webpage/oracle-l
Received on Wed Mar 01 2006 - 10:45:55 CST
