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

Well, waiting on the latch is always done as running on the CPU. Latches are implemented using the "test and set lock" instruction. If the lock cannot be set, the code will "wait" by executing an "idle loop"  on the CPU. Once upon a time, there was even an "underscore parameter" defining the number of iterations in the loop.  So, it is technically possible to be both waiting and running on CPU. The main problem with wide spread latch wait is an exorbitant CPU consumption. Waiting for a latch is most active form of waiting there is, which negates the old joke that all computers wait at the same speed.

Regards

On 12/14/18 5:43 PM, Tanel Poder wrote:
> How did you determine that the sessions are /waiting/ and not on CPU
> (where v$session incorrectly shows the previous wait event)...  which
> exact query ... v$session ... or ASH?
>
> --
> Tanel Poder
> https://blog.tanelpoder.com
>
> On Fri, Dec 14, 2018 at 8:20 AM Kumar Madduri <ksmadduri_at_gmail.com
> <mailto:ksmadduri_at_gmail.com>> 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)
>
>

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


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