Questions about kghupr1 (shared pool latch)

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

--
http://www.freelists.org/webpage/oracle-l