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 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 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@xxxxxxxxx> 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@xxxxxxxxx> 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@xxxxxxxxx>
wrote:
Hello:Rgds,
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
--
Sachin Pawar
https://twitter.com/sach_pwr
