Re: How does Oracle database close client connections

From: Hemant K Chitale <hemantkchitale_at_gmail.com>
Date: Fri, 16 Nov 2018 12:01:08 +0800
Message-ID: <CAMNBsZuhQNBmL5XRVaaeCFgtBDNxF9evxVPiaibtq_sMjEyYQw_at_mail.gmail.com>



Two possibilities :
1. If a job ("invoice processing batch") takes a long time, the application could be spawning more connections to the database, thus resulting in more sessions (processes) on the database. Have you compared the time it takes to run the batches between the two environments ? 2. The trigger to create a new connection to the database may be lower in the pre-production environment, resulting in more connections ?

Idle connection shutdown time from the application may be different on pre-production (although you seem to have confirmed that it is the same in both environments). How long do those INACTIVE sessions actually persist in the non-prod environment ?

Hemant K Chitale

On Fri, Nov 16, 2018 at 10:55 AM Hameed, Amir <Amir.Hameed_at_xerox.com> wrote:

> Hi,
>
> I was recently engaged to troubleshoot an issue in a pre-production
> environment which involves Oracle 11.2.0.4 database running on Solaris
> server and IIS running on a Windows VM. On the IIS side, the minimum and
> maximum connection pool settings are set to 100 and 4000 respectively. On
> the Oracle database side, the processes and sessions parameters are set to
> 4000 and 6000 respectively. I have no visibility to IIS and I do not have
> any experience with IIS either. I am relying on my understanding of how
> connection pooling work in WebLogic Server to triage the problem. The
> application processes thousands of invoices in multiple batches and each
> batch can run up to 4000 invoices concurrently. The non-production
> environment has similar setup (at least this is what I have been told).
> When the application team runs load in non-production, it completes
> successfully and we do not see more than 150 connections coming from the VM
> (V$SESSION.MACHINE) and the INACTIVE session count goes down to 100.
> However, the same load when run in the pre-production environment, it
> consumes up to 4000 connections and this is a repeatable process. I have
> been told that IIS is setup the same way in non-production and
> pre-production environments. The default value of releasing idle
> connections frequency in IIS is 30 seconds. However, after processing
> invoices, even though connections established from the IIS VM have INACTIVE
> status, connections are not getting released in the pre-production
> environment and I am trying to understand the mechanism around how Oracle
> database releases connections.
>
>
>
> I believe (and I could be wrong) that Oracle does not automatically close
> INACTIVE connections on its own and this is not the same situation as DCD.
> Is it possible that even though IIS is trying to shrink the connection pool
> but Oracle database is not releasing connections? Both non-production and
> pre-production have same initialization parameters with the only difference
> being the SGA size.
>
>
>
> Any insight will be appreciated.
>
>
>
> Thanks,
>
> Amir
>

--
http://www.freelists.org/webpage/oracle-l
Received on Fri Nov 16 2018 - 05:01:08 CET

Original text of this message