Re: Wait Event “cursor: pin s” in Oracle Applications

Oracle 12c, both R1 and R2 have their fair share of problems with latches. Oracle 11G had problems with buffer chain hash latches, which were mostly resolved in 12c. However, Oracle 12c has introduced large changes in the optimizer: much larger histograms, new features of the histograms ("most popular values") and the "authid current_user".  There is also a whole group of problems that Jonathan Lewis has aptly named "adaptive mayhem", essentially tuning by cardinality feedback on steroids. All of that extends the time needed to parse the statements. The "cursor pin:s" is a parsing latch. I have found only one way of dealing with the problem on the systemic base: using multi-threaded execution mode instead of multi-process execution mode. The reason is simple: when using multi-threaded execution mode, the latches become pthread mutexes, much cheaper to implement than multi-process mutexes which required IPC programming primitives and system services. The mutexes are still there, but they are now much cheaper and much less detrimental to the instance as a whole. However, switching to the multi-threaded architecture is a major change. One of the things that no longer work is "sqlplus / as sysdba". You should really test that in development, QA and UAT. It's not something that you can do casually, based on the post in the oracle-l.

I got my first customer who will deploy Oracle 18c in production on an Exadata X7-2 so before the 2019 is over, I will know much more about the production characteristics and problems of the 18c. So far, this is the only advice I can give you now.

Regards

On 12/14/18 8:18 AM, Kumar Madduri wrote:
> Hello:
> Oracle Applications 12.2 running  against 12c database:
> User submitted the same concurrent program (with different parameters)
> and are running for long time . Noticed that all of the programs are
> on event 'cursor: pin s' and a set of sqls are the same (program 1
> runs sql_id 1,
>  program 2 runs sql_id 1,
>  program 3 runs sql id 2,
> program 4 runs sql id 3
> and all of them are waiting on event "cursor: pin s" and that keeps
> rotating between different programs  (at time t1 program 1 uses sql_id
> 1 , at time  t2 program 1 uses sql_id 2 but program 2 uses sql_ids 1
> or 2 as well. I think you see the pattern there)
>
> sql_id 1, sql_id 2 , sql_id 3 , sql_id 4 are using the same table (the
> sqls are different). Something like update pa_Expenditure_items_All EA
> (where some set of conditions),
> update pa_Expenditure_items_All EA (another set of conditions)
> select pa_Expenditure_items_All EA (where clause)
> select pa_Expenditure_items_All EA (another where clause).
>
> To me it looks like a design issue and nothing much can be done other
> than terminating all of them (or wait long enough and let the programs
> run. Eventually it would be resolved in this case) and running one by
> one unless the design is changed.  In other words, cursor: pin s is
> seen because all programs are trying to get mutex on the same object
> in memory.
>
> Is this understanding correct?
>
> Thank you
> Kumar
>
>
>
>

-- 
Mladen Gogala
Database Consultant
Tel: (347) 321-1217

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