Oracle 12.2 logins hang

From: Chris Stephens <cstephens16_at_gmail.com>
Date: Tue, 29 May 2018 09:33:18 -0500
Message-ID: <CAEFL0szPOxKTjrSx5GAhTrxbAtLNV30HkGJOiHVjjY82f1pNrQ_at_mail.gmail.com>



Oracle 12.2 on CentOS 7.

we recently upgraded one of our databases from 12.1 to 12.2 on centos 7. we have an application that logs on and logs off for every database operation regardless of size. that results in an obscenely high connection/disconnection rate which eventually (after several minutes) results in all logins to the application account hanging indefinitely on
"library cache lock". there is no lock holder. the session is attempting
to update last logon column in sys.user$ (one of the spare columns).

we are working to address the application issue properly but have implemented database resident connection pooling to temporarily alleviate the issue. we thought that had the issue licked but it happened again on Friday when connection pool was in place. we think there were several sessions created outside of the pool by this account earlier in the day or the day before but i can't see how that would contribute to the problem.

we have an SR open with Oracle and have provided systemstate and hanganalyze output to them. looking at the hang analyze shows blocked sessions waiting on the previously mentioned "library cache lock" but
"blocking:

0 sessions".

has anyone seen this before?

any suggestions on how to work around the issue until we get the application code changed to reuse sessions?

--
http://www.freelists.org/webpage/oracle-l
Received on Tue May 29 2018 - 16:33:18 CEST

Original text of this message