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

What are the duration of these waits?
Do you expect to be doing hard parsing for these queries? Are you? Have a look at what’s in v$sql_shared_cursor for them Is there anything special about these queries (do they filter on functions etc)?
Anything funny like many objects with the same name but different owner?

A possible work around would be to mark the SQL as hot so Oracle produces different sql_ids for them, but we should get to the source of the problem first

Hope this helps,
Andy

On Fri, 14 Dec 2018 at 13:50, sachin pawar <getsach_at_gmail.com> wrote:

> Search for few new bugs in 12.2 in MOS. You may find hits
>
> On Fri, Dec 14, 2018 at 8:19 AM Kumar Madduri <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)
>>
>> 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
>>
>>
>>
>>
>> --
> Rgds,
> Sachin Pawar
> https://twitter.com/sach_pwr
>

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