Re: Question on concurrency waits

Thank You Mark. got it now.

Now as we are still trying to find what exact changes were made by the dev team. I tried capturing the Dash Wait chain using tanels scripts. And attached are the results. The top sessions seem to be from 'jdbc thin client' but from SYS , so I'm wondering if these are just from the OEM tool. And this might be the victim but not the culprit. And the application queries which were blocked with event = 'library cache lock' were showing in_parse and in_hardparse column as 'N' , so means they were not getting parsed while experiencing 'library cache lock'.

However, while I was manually trying to travel through the wait chain, I saw its ending at some session with top_level_call_name as 'OAUTH'. and it was executing the below query and then might be some 'alter user' from that session. And because of sampling , it has not captured all statements though. But we see the number of sessions with top_level_call_name as 'OAUTH' has been increased from <10 per hour to ~40K+ in an hour during the issue period. So can it be the cause or is it the victim of some other ? How should we proceed to get to the cause? And if it's a good idea to bump the shard_pool size here?

 select exptime, ltime, astatus, lcount from user$ where user#=:1

On Tue, Oct 26, 2021 at 12:19 AM Mark W. Farnham <mwf_at_rsiz.com> wrote:

> dc_users is being modified by alter user. any parse needs to check
> dc_users for what that user is allowed and where to put things like temp.
> consistency is protected by a latch.
>
>
>
> Is that the answer to your question about how it is logically related?
>
>
>
> mwf
>
>
>
> *From:* Pap [mailto:oracle.developer35_at_gmail.com]
> *Sent:* Monday, October 25, 2021 12:22 PM
> *To:* Mark W. Farnham
> *Cc:* Oracle L
> *Subject:* Re: Question on concurrency waits
>
>
>
> Thank You Mark. So we are trying to reach out to the Dev team because it
> seems to be fired from the application user as we can see from the user_id
> column in v$active_session_history. But unfortunately the exact sql_text we
> are not able to see from the database side.
>
>
>
> And regarding the relation of library cache lock+cache cache lock with the
> alter user, Would you explain a bit , how logically this is related. Is
> this because each application query must be getting hard parsed , if the
> underlying user from which it's fired is getting altered or anything else?
>
>
>
> On Mon, Oct 25, 2021 at 9:30 PM Mark W. Farnham <mwf_at_rsiz.com> wrote:
>
> yes, and the immediate question is to find out why alter user is being
> issued.
>
>
>
> *From:* oracle-l-bounce_at_freelists.org [mailto:
> oracle-l-bounce_at_freelists.org] *On Behalf Of *Pap
> *Sent:* Monday, October 25, 2021 6:31 AM
> *To:* Oracle L
> *Subject:* Re: Question on concurrency waits
>
>
>
> My mistake, one correction:- the cache_id for all those 'row cache lock'
> is pointing to dc_users.
>
>
>
> On Mon, Oct 25, 2021 at 3:12 PM Pap <oracle.developer35_at_gmail.com> wrote:
>
> Hi, It's 11.2.0.4 Oracle database. We are suddenly seeing many
> application sql queries running slow and are showing 'library cache lock'.
> And checking the ASH for the exact time period when the issue started and
> the wait event appeared, we found few SYS sessions were doing 'ALTER USER'
> from program 'passchng.exe' and we are not able to see exact statement from
> sql_text for this (which may be because of its DDL and for DDL the AWR
> doesn't capture the text as of 11.2.0.4). So I wanted to understand if the
> ALTER USER command can cause such locking issues?
>
> And also I see in the initial few minutes this session(Alter user session)
> was showing 'library cache lock' and I don't know how to get more
> information from the value of "handle address" but after some time, that
> session was showing the 'row cache lock' with cache_id pointing to the
> below cache objects. Are these can cause concurrency / "library cache lock"
> for other application queries?
>
> kqlsubheap_object
>
> extensible security user and rol
>
> extensible security principal pa
>
> extensible security UID to princ
>
> extensible security principal na
>
> extensible security principal ne
>
> XS security class privilege
>
> qmtmrctp_cache_entries
>
> qmtmrciq_cache_entries
>
>

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




application/vnd.openxmlformats-officedocument.spreadsheetml.sheet attachment: list_DASH_Wait_chain.xlsx