Orphan sessions

#1

Hello,

I have a question regarding "dead" inactive, orphan sessions.
Sometimes, there's a situation in which a client application (a
webserver on a remote machine) dies horribly and leaves an orphan
session (dedicated server mode) in the database. The user process at
the webserver no longer exists, but the shadow process, the database
session, remains inactive, holding all the resources and locks - which
is a bit of a problem as it usually blocks other transactions. In such
cases, we have to manually identify these sessions and kill them off
ourselves. The PMON then takes over and releases the resources and
locks, as described in 1020720.102 and a few other notes, and leaves
the sessions in the Killed state.

Now, I understand that this is correct behaviour, but I'm wondering if
there's a way or a feature that would look for such cases
automatically. The only sort of applicable thing I found is the
SQLNET.EXPIRE_TIME=xxx parameter but it doesn't really work for us in
this case.

The databases are Oracle 10.2.0.4 (on 64bit Win2k3 machines), the
clients are your typical IIS's using ODP.NET 11.2.0.2, using their own
connection pooling and connecting in dedicated server mode.

Thanks,
David

#2

On Wed, 22 Jun 2011 04:55:51 -0700, David Budac wrote:

Quote:

Hello,

I have a question regarding "dead" inactive, orphan sessions. Sometimes,
there's a situation in which a client application (a webserver on a
remote machine) dies horribly and leaves an orphan session (dedicated
server mode) in the database. The user process at the webserver no
longer exists, but the shadow process, the database session, remains
inactive, holding all the resources and locks - which is a bit of a
problem as it usually blocks other transactions. In such cases, we have
to manually identify these sessions and kill them off ourselves. The
PMON then takes over and releases the resources and locks, as described
in 1020720.102 and a few other notes, and leaves the sessions in the
Killed state.

Now, I understand that this is correct behaviour, but I'm wondering if
there's a way or a feature that would look for such cases automatically.
The only sort of applicable thing I found is the SQLNET.EXPIRE_TIME=xxx
parameter but it doesn't really work for us in this case.

The databases are Oracle 10.2.0.4 (on 64bit Win2k3 machines), the
clients are your typical IIS's using ODP.NET 11.2.0.2, using their own
connection pooling and connecting in dedicated server mode.

Thanks,
David

David, the problem is usually in the processes holding these connections.
If, say, Apache spawns a httpd process which is permanently connected to
the database, it will not get killed because the process will not be a
dead connection. The best solution that I've come up with is limiting the
idle time of the process. The same applies to weblogic and tomcat. If a
process acquires more than 1 hour of idle time, it shouldn't be running,
period.
Alternatively, the applications should not enter long waits while in
transaction. Transactions were initially modeled after the banking
business, which means that they are expected to be discrete, independent
and, above all other, short. The transaction should begin when the luser
pushes the "submit" button and end before the form is refreshed. Waiting
for input, while in transaction, is a bad coding practice.
As for the platform, I have fairly decent experience with 64 bit
Windows7, my impression is that it is much more stable than now fairly
old Win2k3. I cannot substantiate it with numbers, it's just my
impression.

--
http://mgogala.byethost5.com

#3

On Jun 22, 3:30*pm, Mladen Gogala <gogala.mla... (AT) gmail (DOT) com> wrote:

Quote:

On Wed, 22 Jun 2011 04:55:51 -0700, David Budac wrote:
Hello,

I have a question regarding "dead" inactive, orphan sessions. Sometimes,
there's a situation in which a client application (a webserver on a
remote machine) dies horribly and leaves an orphan session (dedicated
server mode) in the database. The user process at the webserver no
longer exists, but the shadow process, the database session, remains
inactive, holding all the resources and locks - which is a bit of a
problem as it usually blocks other transactions. In such cases, we have
to manually identify these sessions and kill them off ourselves. The
PMON then takes over and releases the resources and locks, as described
in 1020720.102 and a few other notes, and leaves the sessions in the
Killed state.

Now, I understand that this is correct behaviour, but I'm wondering if
there's a way or a feature that would look for such cases automatically..
The only sort of applicable thing I found is the SQLNET.EXPIRE_TIME=xxx
parameter but it doesn't really work for us in this case.

The databases are Oracle 10.2.0.4 (on 64bit Win2k3 machines), the
clients are your typical IIS's using ODP.NET 11.2.0.2, using their own
connection pooling and connecting in dedicated server mode.

Thanks,
David

David, the problem is usually in the processes holding these connections.
If, say, Apache spawns a httpd process which is permanently connected to
the database, it will not get killed because the process will not be a
dead connection. The best solution that I've come up with is limiting the
idle time of the process. The same applies to weblogic and tomcat. If a
process acquires more than 1 hour of idle time, it shouldn't be running,
period.
Alternatively, the applications should not enter long waits while in
transaction. Transactions were initially modeled after the banking
business, which means that they are expected to be discrete, independent
and, above all other, short. The transaction should begin when the luser
pushes the "submit" button and end before the form is refreshed. Waiting
for input, while in transaction, is a bad coding practice.
As for the platform, I have fairly decent experience with 64 bit
Windows7, my impression is that it is much more stable than now fairly
old Win2k3. I cannot substantiate it with numbers, it's just my
impression.

--http://mgogala.byethost5.com

Mladen,
the thing is, there are no user processes (httpd's in your comparison)
present at the time => you can completely reboot the box on which this
IIS is located and it has no effect on the database sessions
whatsoever. They are still there, orphaned, holding their resources.
Which is perfectly fine, in my understanding of how Oracle handles it
- the client is responsible for closing them. I'm just looking for a
way of getting rid of them or at least identifying them automatically.
It's something that very very rarely happens and I'm trying to find a
clean solution on the side of the database.

Limiting the idle time of the process is, I believe, for a completely
different discussion. I'm looking for something different and it's
hopefully clearer now.

Thank you for the input, I appreciate it.
David

#4

On 22 čvn, 15:30, Mladen Gogala <gogala.mla... (AT) gmail (DOT) com> wrote:

Quote:

On Wed, 22 Jun 2011 04:55:51 -0700, David Budac wrote:
Hello,

I have a question regarding "dead" inactive, orphan sessions. Sometimes,
there's a situation in which a client application (a webserver on a
remote machine) dies horribly and leaves an orphan session (dedicated
server mode) in the database. The user process at the webserver no
longer exists, but the shadow process, the database session, remains
inactive, holding all the resources and locks - which is a bit of a
problem as it usually blocks other transactions. In such cases, we have
to manually identify these sessions and kill them off ourselves. The
PMON then takes over and releases the resources and locks, as described
in 1020720.102 and a few other notes, and leaves the sessions in the
Killed state.

Now, I understand that this is correct behaviour, but I'm wondering if
there's a way or a feature that would look for such cases automatically..
The only sort of applicable thing I found is the SQLNET.EXPIRE_TIME=xxx
parameter but it doesn't really work for us in this case.

The databases are Oracle 10.2.0.4 (on 64bit Win2k3 machines), the
clients are your typical IIS's using ODP.NET 11.2.0.2, using their own
connection pooling and connecting in dedicated server mode.

Thanks,
David

David, the problem is usually in the processes holding these connections.
If, say, Apache spawns a httpd process which is permanently connected to
the database, it will not get killed because the process will not be a
dead connection. The best solution that I've come up with is limiting the
idle time of the process. The same applies to weblogic and tomcat. If a
process acquires more than 1 hour of idle time, it shouldn't be running,
period.
Alternatively, the applications should not enter long waits while in
transaction. Transactions were initially modeled after the banking
business, which means that they are expected to be discrete, independent
and, above all other, short. The transaction should begin when the luser
pushes the "submit" button and end before the form is refreshed. Waiting
for input, while in transaction, is a bad coding practice.
As for the platform, I have fairly decent experience with 64 bit
Windows7, my impression is that it is much more stable than now fairly
old Win2k3. I cannot substantiate it with numbers, it's just my
impression.

--http://mgogala.byethost5.com

Mladen,
the thing is, there are no user processes (httpd's in your comparison)
present at the time => you can completely reboot the box on which this
IIS is located and it has no effect on the database sessions
whatsoever. They are still there, orphaned, holding their resources.
Which is perfectly fine, in my understanding of how Oracle handles it
- the client is responsible for closing them. I'm just looking for a
way of getting rid of them or at least identifying them automatically.
It's something that very very rarely happens and I'm trying to find a
clean solution on the side of the database.

Limiting the idle time of the process is, I believe, for a completely
different discussion. I'm looking for something different and it's
hopefully clearer now.

Thank you for the input, I appreciate it.
David

#5

On Wed, 22 Jun 2011 07:00:48 -0700, David Budac wrote:

Quote:

hey are still there, orphaned, holding their resources. Which is
perfectly fine, in my understanding of how Oracle handles it - the
client is responsible for closing them. I'm just looking for a way of
getting rid of them or at least identifying them automatically. It's
something that very very rarely happens and I'm trying to find a clean
solution on the side of the database

The following SQLNET.ORA parameters may help you:

SQLNET.EXPIRE_TIME
SQLNET.SEND_TIMEOUT
SQLNET.SEND_TIMEOUT

They work well on Linux. Not sure about W2k3, though.

--
http://mgogala.freehostia.com

#6

I guess I have to figure out why SQLNET.EXPIRE_TIME didn't work. We've
had this parameter set. The more I read about it, the more I see it
was supposed to work. The protocol in use is TCP which makes it even
weirder.

Thanks again

#7

On Jun 22, 10:33*am, David Budac <davidbu... (AT) gmail (DOT) com> wrote:

Quote:

I guess I have to figure out why SQLNET.EXPIRE_TIME didn't work. We've
had this parameter set. The more I read about it, the more I see it
was supposed to work. The protocol in use is TCP which makes it even
weirder.

Thanks again

I seem to recall this can happen if the oracle user process has
spawned a child, the child dies or gets hung up in a wait (or odd
things that put errors in the alert log), and the parent waits forever
for the child to respond, ignoring the death signal from the client.
Or something like that.

Be careful if someone tells you to use code like this (X and z are
obfuscation):

SYS@XXXX> SELECT spid FROM v$process WHERE NOT EXISTS
( SELECT 1 FROM v$session WHERE paddr = addr); 2

SPID
------------

2029
2031

SYS@XXXX> !ps -ef|grep 2029
oracle 2029 1 0 Jun 5 ? 0:00 ora_d000_XXXX
oracle 20039 19987 0 13:08:13 pts/0 0:00 grep 2029

SYS@XXXX> !ps -ef|grep 2031
oracle 2031 1 0 Jun 5 ? 0:01 ora_s000_XXXX
zzzzzzz 12031 1 0 06:35:15 ? 0:20 oracleXXXX
(DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
oracle 20070 19987 1 13:08:30 pts/0 0:00 grep 2031

Note they've been handed off to init, but you can't tell if they are
orphans or legitimate (well, you could have excluded background
processes by username), and of course it picked up an extra process.

jg
--
@home.com is bogus.
http://www.freelists.org/post/oracle...sulting-info,9

#8

On Jun 23, 3:33*am, David Budac <davidbu... (AT) gmail (DOT) com> wrote:

Quote:

I guess I have to figure out why SQLNET.EXPIRE_TIME didn't work. We've
had this parameter set. The more I read about it, the more I see it
was supposed to work. The protocol in use is TCP which makes it even
weirder.

There's a known issue with Windows and DCD. Resolution depends on your
version of Windows and is too gruesome to spell out here. Google
"sqlnet.expire_time +keepalive" for the details.

-- Phil

#9

On Wed, 22 Jun 2011 16:18:23 -0700, phil_herring (AT) yahoo (DOT) com.au wrote:

Quote:

There's a known issue with Windows and DCD. Resolution depends on your
version of Windows and is too gruesome to spell out here. Google
"sqlnet.expire_time +keepalive" for the details.

I knew that my suggestion to upgrade to W7 would eventually prove useful.

--
http://mgogala.byethost5.com

#10

Try cleaning up idle sessions automatically from the server side using
db_resource_manager.

You can specify directives to to terminate idle sessions belonging to
any specific user. You specify this by setting KILL_SESSION as the
switch group using the resource plan directive MAX_IDLE_TIME: Maximum
idle time for the session.

Info here: http://download.oracle.com/docs/cd/B...m.htm#i1010776