Re: Oracle 12.2 logins hang

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_at_gmail.com> 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?
>

-- 
//
zztat - The Next-Gen Oracle Performance Monitoring and Reaction Framework!
Visit us at zztat.net | _at_zztat_oracle | fb.me/zztat | zztat.net/blog/

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