Re: Concurrency - Cursor Pin:S

  • From: Mladen Gogala <gogala.mladen@xxxxxxxxx>
  • To: Jonathan Lewis <jonathan@xxxxxxxxxxxxxxxxxx>, "raviteja.bellamkonda7@xxxxxxxxx" <raviteja.bellamkonda7@xxxxxxxxx>
  • Date: Sun, 18 Jun 2017 14:37:00 -0400



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


Hi Jonathan,
This is Amazon RDS, the user may not have access to DBMS_SHARED_POOL. I remember having to grant execute on DBMS_SHARED_POOL to user system, for my DB startup triggers to work.
Regards

--
Mladen Gogala
Oracle DBA
Tel: (347) 321-1217

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


Other related posts: