How to identify Why two number of Application Servers have a plenty of connections on Oracle RAC 19.14?
Date: Sat, 10 Jun 2023 14:51:16 +0800
Message-ID: <CABpiuuQFpM+jkfooQXxfOXGUBnbJRUam2UZyoBuWEXBD-NZUNg_at_mail.gmail.com>
Hello colleagues and friends,
Yesterday one of my customers reported his oracle database rac 19.14 occupied too many connections *9503* (oracle parameter *processes is 10000*) and I observed that OS workload is very low - only 3 and oracle average active session is less than 1 and never produced abnormal wait event (only consuming ON CPU), but from v$session I've found out the connections with count(*) of two number of application servers have *2000 and 1850* and other some servers have about 500.
Unfortunately I've found out *SQL_ID is NULL from v$session* for these 2 number of application servers, but PREV_SQL_ID has the value *9zg9qd9bm4spu*, the sql_text is as follows.
*update user$ set spare6=DECODE(to_char(:2, 'YYYY-MM-DD'), '0000-00-00',
> to_date(NULL), :2) where user#=:1*
An article said that it is the NEW FEATURE for oracle database 12c ( *https://www.modb.pro/db/26578* <https://www.modb.pro/db/26578>) that used to record the last login time of user, probably there has plenty of login and logout due to those 2 application servers on oracle rac 19.14 and suggestions configuring *DRCP (Database Resident Connection Pool)* on oracle directly. Is it able to perform on oracle?
Has anybody extra method to troubleshoot this issue or good advice?
Best Regards
Quanwen Zhao
-- http://www.freelists.org/webpage/oracle-lReceived on Sat Jun 10 2023 - 08:51:16 CEST