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

Hi Kumar,

One thing to add Oracle Apps use the feature of result cache of database 12c in big way as Oracle wants to cache maximum amount of frequent used queries related FND module.

So please check if result_cache is enabled. It should be 10% of SGA size as per one of notes on metalink.

Also check advisory and if shared_pool seems on lower side, increase it as Apps has little higher memory needs.

Another thing,try to put optimizer_adaptive_features parameter to FALSE and see if issue is resolved.

Regards,
Harmandeep Singh

On Fri, Dec 14, 2018 at 6:49 PM 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
>
>
>
>
>

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