Re: Concurrency - Cursor Pin:S

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