MIME-Version: 1.0
From: Pap <oracle.developer35@gmail.com>
Date: Mon, 25 Oct 2021 15:12:21 +0530
Message-ID: <CAEjw_fgM4ap9hSyy+k_PodiKT7EMeefzOqqhXLNKvowpGnEX3g@mail.gmail.com>
Subject: Question on concurrency waits
To: Oracle L <oracle-l@freelists.org>
Content-Type: multipart/alternative; boundary="00000000000015c65c05cf2a2e40"
Sender: oracle-l-bounce@freelists.org
Errors-to: oracle-l-bounce@freelists.org
Precedence: normal
Reply-To: oracle.developer35@gmail.com
--00000000000015c65c05cf2a2e40
Content-Type: text/plain; charset="UTF-8"

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

--00000000000015c65c05cf2a2e40
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><p style=3D"font-family:&quot;Oracle Sans&quot;;word-break=
:break-word;line-height:inherit;color:rgb(85,90,98);font-size:16px;box-sizi=
ng:border-box;padding:0px;margin:8px 0px 16px;border:0px;vertical-align:bas=
eline;outline:0px;text-overflow:ellipsis;white-space:pre-wrap">Hi, =C2=A0It=
&#39;s 11.2.0.4 Oracle database. We are suddenly seeing many application sq=
l queries running slow and are showing &#39;library cache lock&#39;. And ch=
ecking the ASH for the exact time period when the issue started and the wai=
t event appeared, we found few SYS sessions were doing &#39;ALTER USER&#39;=
 from program &#39;passchng.exe&#39; and we are not able to see exact state=
ment from sql_text for this (which may be because of its DDL and for DDL th=
e AWR doesn&#39;t capture the text as of 11.2.0.4). So I wanted to understa=
nd if the ALTER USER command can cause such locking issues?</p><p style=3D"=
font-family:&quot;Oracle Sans&quot;;word-break:break-word;line-height:inher=
it;color:rgb(85,90,98);font-size:16px;box-sizing:border-box;padding:0px;mar=
gin:8px 0px 16px;border:0px;vertical-align:baseline;outline:0px;text-overfl=
ow:ellipsis;white-space:pre-wrap">And also I see in the initial few minutes=
 this session(Alter user session) was showing &#39;library cache lock&#39; =
and I don&#39;t know how to get more information from the value of &quot;ha=
ndle address&quot; but after some time, that session was showing the &#39;r=
ow cache lock&#39; with cache_id pointing to the below cache objects. Are t=
hese can cause concurrency / &quot;library cache lock&quot; for other appli=
cation queries?</p><p style=3D"font-family:&quot;Oracle Sans&quot;;word-bre=
ak:break-word;line-height:inherit;color:rgb(85,90,98);font-size:16px;box-si=
zing:border-box;padding:0px;margin:8px 0px 16px;border:0px;vertical-align:b=
aseline;outline:0px;text-overflow:ellipsis;white-space:pre-wrap">kqlsubheap=
_object<br></p><p style=3D"font-family:&quot;Oracle Sans&quot;;word-break:b=
reak-word;line-height:inherit;color:rgb(85,90,98);font-size:16px;box-sizing=
:border-box;padding:0px;margin:8px 0px 16px;border:0px;vertical-align:basel=
ine;outline:0px;text-overflow:ellipsis;white-space:pre-wrap">extensible sec=
urity user and rol</p><p style=3D"font-family:&quot;Oracle Sans&quot;;word-=
break:break-word;line-height:inherit;color:rgb(85,90,98);font-size:16px;box=
-sizing:border-box;padding:0px;margin:8px 0px 16px;border:0px;vertical-alig=
n:baseline;outline:0px;text-overflow:ellipsis;white-space:pre-wrap">extensi=
ble security principal pa</p><p style=3D"font-family:&quot;Oracle Sans&quot=
;;word-break:break-word;line-height:inherit;color:rgb(85,90,98);font-size:1=
6px;box-sizing:border-box;padding:0px;margin:8px 0px 16px;border:0px;vertic=
al-align:baseline;outline:0px;text-overflow:ellipsis;white-space:pre-wrap">=
extensible security UID to princ</p><p style=3D"font-family:&quot;Oracle Sa=
ns&quot;;word-break:break-word;line-height:inherit;color:rgb(85,90,98);font=
-size:16px;box-sizing:border-box;padding:0px;margin:8px 0px 16px;border:0px=
;vertical-align:baseline;outline:0px;text-overflow:ellipsis;white-space:pre=
-wrap">extensible security principal na</p><p style=3D"font-family:&quot;Or=
acle Sans&quot;;word-break:break-word;line-height:inherit;color:rgb(85,90,9=
8);font-size:16px;box-sizing:border-box;padding:0px;margin:8px 0px 16px;bor=
der:0px;vertical-align:baseline;outline:0px;text-overflow:ellipsis;white-sp=
ace:pre-wrap">extensible security principal ne</p><p style=3D"font-family:&=
quot;Oracle Sans&quot;;word-break:break-word;line-height:inherit;color:rgb(=
85,90,98);font-size:16px;box-sizing:border-box;padding:0px;margin:8px 0px 1=
6px;border:0px;vertical-align:baseline;outline:0px;text-overflow:ellipsis;w=
hite-space:pre-wrap">XS security class privilege</p><p style=3D"font-family=
:&quot;Oracle Sans&quot;;word-break:break-word;line-height:inherit;color:rg=
b(85,90,98);font-size:16px;box-sizing:border-box;padding:0px;margin:8px 0px=
 16px;border:0px;vertical-align:baseline;outline:0px;text-overflow:ellipsis=
;white-space:pre-wrap">qmtmrctp_cache_entries</p><p style=3D"font-family:&q=
uot;Oracle Sans&quot;;word-break:break-word;line-height:inherit;color:rgb(8=
5,90,98);font-size:16px;box-sizing:border-box;padding:0px;margin:8px 0px 16=
px;border:0px;vertical-align:baseline;outline:0px;text-overflow:ellipsis;wh=
ite-space:pre-wrap">qmtmrciq_cache_entries</p><p style=3D"font-family:&quot=
;Oracle Sans&quot;;word-break:break-word;line-height:inherit;color:rgb(85,9=
0,98);font-size:16px;box-sizing:border-box;padding:0px;margin:8px 0px 16px;=
border:0px;vertical-align:baseline;outline:0px;text-overflow:ellipsis;white=
-space:pre-wrap">

</p></div>

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