On 06/18/2017 01:28 PM, Jonathan Lewis wrote:
For an idea of the actual impact you could compare (with an interval) the
number of executions of the cursor with the number of waits for the pin -
multiply the average wait time by (pin waits / cursor executions). Of course
you can't guarantee that all the waits are for the same cursor, but it's a
ballpark figure. You've also got the elapsed time for cursor execution (from
v$sql or the AWR/statspack) so you can compare that with the pin time.
Have you looked at dbms_shared_pool.markhot ? This will make Oracle generate
multiple copies of a single cursor, hashing to different library cache hash
buckets, and may be an easy way to bypass the pin waits.
I have some notes to write up of a recent experience of using this, but haven't
got around to it yet, but the blog Mladen referenced before has an article on
it:
http://andreynikolaev.wordpress.com/2011/05/01/divide-and-conquer-the-true-mutex-contention/
Regards
Jonathan Lewis
