Re: 100 percent miss in library cache
- From: "Kevin Lidh" <kevin.lidh@xxxxxxxxx>
- To: "Guang Mei" <GMei@xxxxxx>
- Date: Wed, 1 Mar 2006 08:32:21 -0700
The only thing I found different was when they did 10gR1, they used
automatic UNDO and in 10gR2 they went back to identified rollback segments
(long story). But the rest of the parameters are the same.
On 3/1/06, Guang Mei <GMei@xxxxxx> wrote:
>
> Are the parameters in init.ora file the same from 10.2.0.1 and 10.1.0.2 ?
>
> Guang
>
> -----Original Message-----
> *From:* oracle-l-bounce@xxxxxxxxxxxxx [mailto:
> oracle-l-bounce@xxxxxxxxxxxxx]*On Behalf Of *Kevin Lidh
> *Sent:* Wednesday, March 01, 2006 10:21 AM
> *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
>
>
Other related posts:
