Questions about kghupr1 (shared pool latch)

From: "Eagle Fan" <eagle.f@xxxxxxxxx>
To: ORACLE-L <oracle-l@xxxxxxxxxxxxx>
Date: Wed, 16 May 2007 08:56:19 +0800

hi:

I'm reading Steve Adams 2001 Feb. News letter:

http://www.ixora.com.au/newsletter/2001_02.htm

I have the same problem with high shared pool & library cache latch
contention on our production databases.

During the peak time, the number of active sessions went up to above 100.
More than 80% of CPU time was spent on "latch free" wait event.

Applications are using bind variables and shared_pool_size has been set as
4G. (3G free now)

It's very like what it said
"
In the first case there was contention for both the shared pool and library
cache latches. This combination is very familiar, but in this case much of
the problem was at kghupr1
which is very unusual. In kghupr the shared pool latch is taken to unpin a
recreatable chunk of memory and add it to the head of one of the shared pool
LRU lists. The latch was
not being retained for a long time, therefore we concluded that there must
have been unusually high demand for the latch at this point. What would
cause a carefully written application
that makes consistent use of bind variables to discard more chunks of memory
than it reuses? The only possibility that we could think of was if the
application was consistently rebinding
each cursor prior to re-execution. That would cause chunks of bind metadata
to be discarded, and never reused. A quick question to the developers
revealed that this was indeed the case.
The solution was to modify the application to refrain from rebinding unless
the location, rather than just the values, of the bind variables had
changed.
"

My question is how applications can consistently rebind each cursor prior to
re-execution?

What's the quick question to the developers? :)

And any symptoms we can check in database for this?

Thanks


--
Eagle Fan

Oracle DBA

Questions about kghupr1 (shared pool latch)

Other related posts: