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

I’m no fan of rules of thumb in general, but 1-3 X CPUs for processes is way off. Don’t forget the few hundred background processes that you need to support as standard. The “perfect” number will depend on those (which is going to be version dependent and have defaults that will be dependent on other parameters), and the number of foreground processes that your application realistically requires to achieve throughput without queuing. I recommend over shooting this by some amount so you can afford for extra application servers to go live without requiring taking down your instance.

If you want my rule of thumb, you probably won’t be going wrong with 1,000 (remembering it’s per instance), unless you have very few cores or less than 8GB of ram (ie less than a reasonably modern laptop). The overhead of this shouldn’t be too high, and it’s not so high that connection pool leaking will go unnoticed. We have to realize that it’s okay for there to be some idle sessions, but if an application decides to connect a new one when there are plenty of free sessions ready, you are going to have a problem.

All that said, I wouldn’t be touching anything until I had a better idea of how the application is doing the connection pooling. The configuration for something like what your customer is seeing should be pretty easy to spot as being the issue.

Thanks,
Andy

On Sat, Jun 10, 2023 at 10:49 AM, Timur Akhmadeev <timur.akhmadeev_at_gmail.com> wrote:

> 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