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