100 hard parses per second in 10.2 database
Date: Sun, 16 Aug 2009 15:31:16 -0700 (PDT)
Message-ID: <306ec1bd-3676-4062-9bfd-5c51f30e5162_at_g10g2000yqh.googlegroups.com>
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?
Received on Sun Aug 16 2009 - 17:31:16 CDT