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
