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