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

Depending on the application server/connection pool in use, there should be application level tools available to trace leaked connections. For example WebLogic has connection leak profiling which works great, and prints a stack trace of the point in code where connection was acquired, and was not released after configured timeout.
For other app servers/pools there may not be a direct tool to trace such leaks. What you can do is take a heap dump (if it’s a Java based app server) and then analyze it with Memory Analyzer, and figure out some details from the memory structures associated with connections - things like user name, sql text, etc most likely will be referenced by some connection objects. It takes some time to understand what to look for, the good thing is the tool is GUI and is relatively easy to use. Ultimately your app server configs should be changed to only keep open no more than a few hundreds of connections, otherwise the database won’t be able to handle the app.

On Sat, 10 Jun 2023 at 09:52, 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
>

-- 
Regards
Timur Akhmadeev

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