Question on concurrency waits
From: Pap <oracle.developer35_at_gmail.com>
Date: Mon, 25 Oct 2021 15:12:21 +0530
Message-ID: <CAEjw_fgM4ap9hSyy+k_PodiKT7EMeefzOqqhXLNKvowpGnEX3g_at_mail.gmail.com>
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?
Date: Mon, 25 Oct 2021 15:12:21 +0530
Message-ID: <CAEjw_fgM4ap9hSyy+k_PodiKT7EMeefzOqqhXLNKvowpGnEX3g_at_mail.gmail.com>
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 Received on Mon Oct 25 2021 - 11:42:21 CEST