Re: Question on concurrency waits

Thank You Mark and Andy. Yes it's mostly the invalid login attempts that have exposed the concurrency bug and have caused the impact. But yes the cause must be the place from where exactly those 'alter user..' (which is mostly password change request only) is coming up. We are yet to get it from the application side.

On Tue, Oct 26, 2021 at 3:22 AM Mark W. Farnham <mwf_at_rsiz.com> wrote:

> Yes to both of Andy’s remarks, but you still also have the issue with
> “program 'passchng.exe'” and what is causing someone to issue Alter User
> commands (which from the program name the suggestion is that the password
> is being changed).
>
>
>
>
>
>
>
> *From:* oracle-l-bounce_at_freelists.org [mailto:
> oracle-l-bounce_at_freelists.org] *On Behalf Of *Andy Sayer
> *Sent:* Monday, October 25, 2021 4:46 PM
> *To:* Pap
> *Cc:* Mark W. Farnham; Oracle L
> *Subject:* Re: Question on concurrency waits
>
>
>
> This sounds like the 11.2 issue with bad concurrency when incorrect
> password attempts are made, this was fixed in 12.1 (both better concurrency
> and instrumented as a more obvious wait).. I suggest you check for ORA-1017
> in your application and listener logs.
>
>
>
> Also, it's about time you upgrade ;)
>
>
>
> Thanks,
>
> Andy
>
>
>
> On Mon, 25 Oct 2021 at 21:29, Pap <oracle.developer35_at_gmail.com> wrote:
>
> 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