You could use a logon trigger to "throttle" the logons down. Perhaps
forcing each logon to wait a second or two on a dbms_lock.sleep might do
the trick. That might also make it worse. It's hard to say, but probably
worth a shot.
You really, really need to do two things in my opinion:
1) Fix the broken application
2) Move to a supported OS (Centos isn't one of them)
Stefan
On Tue, May 29, 2018 at 9:33 PM, Chris Stephens <cstephens16@xxxxxxxxx>
wrote:
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?
