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

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

Re: 100 percent miss in library cache

From: Kevin Lidh <kevin.lidh_at_gmail.com>
Date: Wed, 1 Mar 2006 10:37:06 -0700
Message-ID: <cb4807f0603010937x1408b99di16c027083aca9c84@mail.gmail.com>


I think it's a good thing, too. Unless you have no control over the code... An update is that Oracle suggested upping the KGL latches and they saw a higher TPS but when I looked at the STATSPACK report, still 100% miss on SQL AREA. There was a previous suggestion to change CURSOR_SHARING to FORCE but when I had her search V$SQLTEXT for system generated binds (:SYS_B...) and she didn't see any. Good news but it would be nice to have someone to blame.

Kevin

On 3/1/06, Johnson, George <GJohnson_at_gam.com> wrote:
>
>
> 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 - 11:37:06 CST

Original text of this message

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