Re: Question on concurrency waits

I tried the Dash Top script and below result from that clearly showing the top consumer as 'oauth' session. I am not able to see any rows from v$db_object_cache when passing the P1 as filter to hash_value column, maybe they must have been flushed away. Can this be some kind of buggy behavior?

TotalSeconds AAS %This EVENT TOP_LEVEL_CALL_NAME SQL_OPNAME P1TEXT P1 FIRST_SEEN LAST_SEEN
92480 77.1 72% library cache lock OAUTH handle address 1.28E+11 24-10-2021 22:25 24-10-2021 22:39
14560 12.1 11% SQL*Net message from dblink V8 Bundled Exec SELECT driver id 6.76E+08 24-10-2021 22:20 24-10-2021 22:39 4390 3.7 3% V8 Bundled Exec SELECT driver id 6.76E+08 24-10-2021 22:20 24-10-2021
22:39
820 0.7 1% OAUTH handle address 1.28E+11 24-10-2021 22:25 24-10-2021 22:39
780 0.6 1% VERSION2 SELECT cellhash# 1.61E+09 24-10-2021 22:20 24-10-2021 22:26

On Tue, Oct 26, 2021 at 1:18 AM Pap <oracle.developer35_at_gmail.com> wrote:

> 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