Hello,
the problem is that in waves we're encountering quite a lot of sessions
waiting on latch wait event. The problematic latch is the library cache
latch and our sufferer is a simple SQL statement.
The facts are:
- everything is happening in a wave pattern, each minute about twenty
seconds of waiting (60+ sessions on latch wait) and forty seconds of
working
- all queries are using BIND variables, the particular has only one
expression in WHERE clause and this expression is properly BINDed
- during the last two hours, we had 2631562 parses (3425 hard parses)
and 8034543 executions of SQL statements happened
- the problematic statement was issued 1.2 times per second
- the problem has started five days ago, but we were unable to find
any change in the system that could cause this
- AIX 5.2 64bit, 9.2.0.6, connection pooling, about 100 concurrent DB
calls
- during latch contention our CPUs were not stressed more than usual
(not typical behaviour while encountering a latch problem)
- we're using synonym in this statement, but the same applies also for
the rest of our statements and they are having no problem even the ones
that are executed more frequently
I have looked through Metalink, but regarding library cache contention,
there is no much information. Any ideas?
Thank you
--
Dusan Bolek
Received on Wed Jan 25 2006 - 10:42:25 CST
