How does Oracle database close client connections

[Hameed, Amir]

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

[Stefan Knecht]

The database does not automatically close inactive connections by default.

You can achieve this, if desired, through the use of profiles. Have a look
at IDLE_TIME here:
https://docs.oracle.com/database/121/SQLRF/statements_6012.htm#SQLRF01310

Post by Hameed, Amir
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

[Hemant K Chitale]

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

Post by Hameed, Amir
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

[Stefan Koehler]

Hello Amir,
seriously you have a more serious issue than the IDLE connections not being ended/closed.

How many cores do you have? I assume that it is less than 400 cores and so you are heavily oversubscribing your CPUs. Define a static connection pool according to your amount of available cores and that's it - anyway no need to worry about the IDLE connections afterwards.

For more information please check out the RWP Learning Library (Videos #13 & #14): https://apexapps.oracle.com/pls/apex/f?p=44785:141:0::NO::P141_PAGE_ID,P141_SECTION_ID:119,870

Best Regards
Stefan Koehler

Independent Oracle performance consultant and researcher
Website: http://www.soocs.de

Post by Hameed, Amir
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.

Post by Hameed, Amir
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

[Tanel Poder]

Hi Amir,

Yep it's the applications job to close connections that it doesn't need
anymore (or release them back to connection pool). Oracle by default does
the right thing and does not kill any connections that the application is
holding. It doesn't have the visibility into whether the app wants to use
the connection again.

So if you have a "connection leak" bug in the app and it can't be fixed for
some reason, then you could set a PROFILE to limit the IDLE_TIME for the
username this app is using (especially as you seem to already have some
sort of a 30 sec timeout in IIS). This would kill idle connections after X
minutes regardless if these connections were idle due to a connetion leak
bug or they genuinely needed to be idle for a while until the app completed
some other task. So you might end up killing wrong sessions that way too
and get exceptions in your app.

Note that the IIS timeout might not apply (I don't know) if the application
code itself still holds the connection. A typical problem why connection
(and cursor leaks) happen is this.

Code runs like this in ideal world:

1) Get gonnection
2) Do something
3) Release Connection
4) Return

But if some transactions hit an exception and if the developers don't
release the connection in an exception handler, you'll get this:

1) Get connection
2) Do something
Exception
3) Release Connection
4) Return

The connection release operation is skipped as the latest SQL hit an
exception.

You could query the V$SESSION.PREV_SQL_ID of all those excessive sessions
and see if they all have a similar SQL_ID as the last one executed. SQL
tracing some sessions would help to see which error you got.

--
Tanel Poder
https://blog.tanelpoder.com/seminar/

Post by Hameed, Amir
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