Re: 100 hard parses per second in 10.2 database
Date: Sun, 16 Aug 2009 15:48:03 -0700 (PDT)
Message-ID: <b4f7297b-96d4-46f8-b7c9-c5db1dc8bd5c_at_18g2000yqa.googlegroups.com>
On Aug 16, 6:31 pm, ca111026 <ca111..._at_gmail.com> wrote:
snip
> We have an application that does not use bind variables. The database
> is Oracle 10.2.0.3 (64-bit) on AIX 5.3. Statspack shows following
> values per second (for 9:00 - 19:00 interval):
> user calls: 3000
> parses: 900
> hard parses: 100
> From time to time we get issue with library cache latch, I think it is
> caused by high rate of hard parses. We tried to apply 10.2.0.4 patch
> set (twice), both failed as application experienced issues.
> Obviously our testing is not sufficient. We also tried setting
> cursor_sharing = similar in test environment, again there were
> issues.
>
> Previously the rate of hard parses was even higher, some queries were
> changed to use bind variables, so we brought the rate of hard parses
> down to 100 - 110 per second.
>
> Do you think it is sustainable? Or Oracle wouldn't be able to handle
> such rate? The application is quite important, and every unplanned
> outage is a big deal.
>
> Shared pool is 4800 MB, should we increase or decrease it? As most SQL
> statements never get reused keeping them in the shared pool seems
> pointless, so reducing size of the shared pool makes sense.
>
> V$LATCH has column WAITS_HOLDING_LATCH, unfortunately it is missing
> from STATS$LATCH, so I am unable to state for certan whether the
> problems with library cache latch are result of high rate of hard
> parses (likely) or they occur because of other latches (unlikely).
>
> The distribution of work between latch children seems reasonable (V
> $LATCH_CHILDREN).
> We could try increasing _kgl_latch_count, anything else?
That's a huge chunk of shared pool for something that you know is not using bind variables all the time. I would ( test it out on a test system if you can first ) immediately think about going to a shared pool of like 20/25 percent of that size.
How about cursor_sharing set to force instead of similar?
With some fast hardware 100 hard parses a second may be ( far from ideal ) but survivable.
I had some issues with 10.2.0.3 any reason you are not on 10.2.0.4? ( With any luck 10.2.0.5 should be out soon ). Received on Sun Aug 16 2009 - 17:48:03 CDT