Re: 100 hard parses per second in 10.2 database

From: John Hurley <>
Date: Sun, 16 Aug 2009 15:48:03 -0700 (PDT)
Message-ID: <>

On Aug 16, 6:31 pm, ca111026 <> wrote:


> We have an application that does not use bind variables. The database
> is Oracle (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 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
> 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 any reason you are not on ( With any luck should be out soon ). Received on Sun Aug 16 2009 - 17:48:03 CDT

Original text of this message