Re: How to identify Why two number of Application Servers have a plenty of connections on Oracle RAC 19.14?

This will be 100% down to application connection pooling configuration/code. The high processes parameter is indicative that it’s been bad for a while and rather than fixing the issue, the limit was increased. Your observations mean that the application is starting up sessions to do nothing, that’s not sensible.

Check what drivers are being used and how the connection pool is being configured.

I would not recommend DRCP as an easy win. It requires work from the application code side to benefit. I found that the default behaviour for most drivers creates unsharable sessions so it is essentially turning off connection pooling completely. It is decent if you have lots and lots of application servers that would benefit from using one shared session pool, but if you only have 10 or so then you should be fine managing an connection pool per application server.

Thanks,
Andy

On Fri, Jun 9, 2023 at 11:52 PM, Quanwen Zhao <quanwenzhao_at_gmail.com> wrote:

> 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-l