Re: Wait Event “cursor: pin s” in Oracle Applications
From: Kumar Madduri <ksmadduri_at_gmail.com>
Date: Fri, 14 Dec 2018 06:37:50 -0800
Message-ID: <CAHDOOG7o91tkwwEsYjqdkb9A+i=YbDewgKUygp+hBzJZjhCeKg_at_mail.gmail.com>
sql_id = 9cwcasubj9ynu
UPDATE PA_EXPENDITURE_ITEMS_ALL EI SET COST_DISTRIBUTED_FLAG = 'N', ADJUSTMENT_TYPE ='BURDEN_RECOMPILE', COST_BURDEN_DISTRIBUTED_FLAG = 'N', LAST_UPDATE_DATE = SYSDATE, LAST_UPDATED_BY = :B7 , LAST_UPDATE_LOGIN = :B6 , REQUEST_ID = :B5 , PROGRAM_APPLICATION_ID = :B4 , PROGRAM_ID = :B3 , PROGRAM_UPDATE_DATE = SYSDATE, DENOM_BURDENED_COST = NULL, PROJECT_BURDENED_COST = NULL, ACCT_BURDENED_COST = NULL, BURDEN_COST = NULL WHERE COST_IND_COMPILED_SET_ID = :B2 AND EXISTS (SELECT TASK_ID FROM PA_TASKS TASK WHERE TASK.TASK_ID = EI.TASK_ID AND TASK.COST_IND_SCH_FIXED_DATE BETWEEN :B9 AND NVL(:B8 , COST_IND_SCH_FIXED_DATE)) AND NVL(EI.NET_ZERO_ADJUSTMENT_FLAG, 'N') <>'Y' AND PA_PROJECT_STUS_UTILS.IS_PROJECT_CLOSED(EI.PROJECT_ID) <>'Y' AND
and
sql_id = 1saq100206z99
SELECT EXPENDITURE_ITEM_ID FROM PA_EXPENDITURE_ITEMS_ALL EI WHERE COST_IND_COMPILED_SET_ID = :B2 AND EXISTS (SELECT T1.TASK_ID FROM
,'Y',GMS_PA_API2.IS_AWARD_CLOSED(EI.EXPENDITURE_ITEM_ID,EI.TASK_ID),'N') =
'N' )
Date: Fri, 14 Dec 2018 06:37:50 -0800
Message-ID: <CAHDOOG7o91tkwwEsYjqdkb9A+i=YbDewgKUygp+hBzJZjhCeKg_at_mail.gmail.com>
Hi Andy
Thanks for the information.
In my case select * from v$sql_shared_cursor where sql_id in
('1saq100206z99', '9cwcasubj9ynu','d5pngwrc6t7nv','dnx7b202v57gj'); I get 4
rows and then all *_MISMATCH columns have value of 'N'.
So I assume no hard parsing
Only thing I noticed are the queries have the same alias and they belong to
same schema like below
sql_id = dnx7b202v57gj is
SELECT EXPENDITURE_ITEM_ID FROM PA_EXPENDITURE_ITEMS_ALL EI WHERE
COST_IND_COMPILED_SET_ID = :B2 AND EXISTS (SELECT TASK_ID FROM PA_TASKS
TASK WHERE TASK.TASK_ID = EI.TASK_ID AND TASK.COST_IND_SCH_FIXED_DATE
BETWEEN :B4 AND NVL(:B3 , COST_IND_SCH_FIXED_DATE)) AND
NVL(EI.NET_ZERO_ADJUSTMENT_FLAG,'N') <>'Y' AND
PA_PROJECT_STUS_UTILS.IS_PROJECT_CLOSED(EI.PROJECT_ID) <>'Y' AND DECODE(:B1
,'Y',GMS_PA_API2.IS_AWARD_CLOSED(EI.EXPENDITURE_ITEM_ID,EI.TASK_ID),'N') =
'N'
and
sql_id =d5pngwrc6t7nv is
UPDATE PA_EXPENDITURE_ITEMS_ALL EI SET REVENUE_DISTRIBUTED_FLAG = 'N',
LAST_UPDATE_DATE = SYSDATE, LAST_UPDATED_BY = :B6 , LAST_UPDATE_LOGIN = :B5 , REQUEST_ID = :B4 , PROGRAM_APPLICATION_ID = :B3 , PROGRAM_ID = :B2 , PROGRAM_UPDATE_DATE = SYSDATE WHERE COST_IND_COMPILED_SET_ID = :B1 ANDEXISTS (SELECT T1.TASK_ID FROM PA_PROJECT_TYPES_ALL PT, PA_PROJECTS_ALL P, PA_TASKS T1 WHERE PT.PROJECT_TYPE = P.PROJECT_TYPE AND NVL(PT.ORG_ID, -99) = NVL(P.ORG_ID, -99) AND P.PROJECT_ID = T1.PROJECT_ID AND TRUNC(T1.COST_IND_SCH_FIXED_DATE) BETWEEN TRUNC(:B8 ) AND TRUNC(NVL(:B7 , T1.REV_IND_SCH_FIXED_DATE)) AND T1.TASK_ID = EI.TASK_ID AND PT.PROJECT_TYPE_CLASS_CODE = 'CAPITAL' AND PT.CAPITAL_COST_TYPE_CODE = 'B') AND NVL(EI.NET_ZERO_ADJUSTMENT_FLAG,'N') <>'Y' AND PA_PROJECT_STUS_UTILS.IS_PROJECT_CLOSED(EI.PROJECT_ID)<>'Y' AND ( GMS_PA_API2.IS_AWARD_CLOSED(EI.EXPENDITURE_ITEM_ID,EI.TASK_ID) = 'N' ) and
sql_id = 9cwcasubj9ynu
UPDATE PA_EXPENDITURE_ITEMS_ALL EI SET COST_DISTRIBUTED_FLAG = 'N', ADJUSTMENT_TYPE ='BURDEN_RECOMPILE', COST_BURDEN_DISTRIBUTED_FLAG = 'N', LAST_UPDATE_DATE = SYSDATE, LAST_UPDATED_BY = :B7 , LAST_UPDATE_LOGIN = :B6 , REQUEST_ID = :B5 , PROGRAM_APPLICATION_ID = :B4 , PROGRAM_ID = :B3 , PROGRAM_UPDATE_DATE = SYSDATE, DENOM_BURDENED_COST = NULL, PROJECT_BURDENED_COST = NULL, ACCT_BURDENED_COST = NULL, BURDEN_COST = NULL WHERE COST_IND_COMPILED_SET_ID = :B2 AND EXISTS (SELECT TASK_ID FROM PA_TASKS TASK WHERE TASK.TASK_ID = EI.TASK_ID AND TASK.COST_IND_SCH_FIXED_DATE BETWEEN :B9 AND NVL(:B8 , COST_IND_SCH_FIXED_DATE)) AND NVL(EI.NET_ZERO_ADJUSTMENT_FLAG, 'N') <>'Y' AND PA_PROJECT_STUS_UTILS.IS_PROJECT_CLOSED(EI.PROJECT_ID) <>'Y' AND
((PA_UTILS2.PROJ_TYPE_BURDEN_DISP_METHOD(EI.PROJECT_ID) IN
('S','s','D','d') AND :B1 ='N') OR
(PA_UTILS2.PROJ_TYPE_BURDEN_DISP_METHOD(EI.PROJECT_ID) IN ('S','s') AND :B1
='Y')) AND ( GMS_PA_API2.IS_AWARD_CLOSED(EI.EXPENDITURE_ITEM_ID,EI.TASK_ID)
= 'N' )
and
sql_id = 1saq100206z99
SELECT EXPENDITURE_ITEM_ID FROM PA_EXPENDITURE_ITEMS_ALL EI WHERE COST_IND_COMPILED_SET_ID = :B2 AND EXISTS (SELECT T1.TASK_ID FROM
PA_PROJECT_TYPES_ALL PT, PA_PROJECTS_ALL P, PA_TASKS T1 WHERE PT.PROJECT_TYPE = P.PROJECT_TYPE AND PT.ORG_ID = P.ORG_ID AND P.PROJECT_ID = T1.PROJECT_ID AND T1.COST_IND_SCH_FIXED_DATE BETWEEN :B4 AND NVL(:B3 ,T1.REV_IND_SCH_FIXED_DATE) AND T1.TASK_ID = EI.TASK_ID AND PT.PROJECT_TYPE_CLASS_CODE = 'CAPITAL' AND PT.CAPITAL_COST_TYPE_CODE = 'B') AND NVL(EI.NET_ZERO_ADJUSTMENT_FLAG,'N') <>'Y' AND PA_PROJECT_STUS_UTILS.IS_PROJECT_CLOSED(EI.PROJECT_ID) <>'Y' AND ( DECODE(:B1
,'Y',GMS_PA_API2.IS_AWARD_CLOSED(EI.EXPENDITURE_ITEM_ID,EI.TASK_ID),'N') =
'N' )
On Fri, Dec 14, 2018 at 6:25 AM Andy Sayer <andysayer_at_gmail.com> wrote:
> 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-lReceived on Fri Dec 14 2018 - 15:37:50 CET