oracle threads increasing, even after closing sessions
Jimmy
We have a problem with 2 Oracle Server 10.2.0.0 on Windows 2003
Server.
The number of threads in the oracle.exe process keeps increasing, even
after the user session is finished.
We currently have +- 35 session open, and already 136 threads !
We already changed the PROCESSES ini parameter to 300, because we
received ORA-00020 error.
It seems that the threads are not properly cleaned up, after the
session is finished.
Is PMON not supposed to clean this up ?
Any ideas ?
fitzj...@cox.net
Hmmm, 10.2.0.0 .... I thought 10.2.0.2 was the base release...
You have checked metalink for this issue, I presume. And you do
realise that 10gR2 is certified for Windows 2003 server ONLY at the
10.2.0.2 and 10.2.0.3 patch levels.
It appears you need to patch your installation.
David Fitzjarrell
Cristian Cudizio
fitzj...@cox.net ha scritto:
We have similar problem on development database, 10.2.0.2 on window
2003, it seems that under some
circustances Oracle is unable to cleanup sessions not cleanly closed.
i've searched metalink, but
i've not found something usefull. I've tried with window utility
process explorer to kill some threads and it works, but obviously is
not the solution, at least it may permit to not restart Oracle.
Cristian Cudizio
http://oracledb.wordpress.com
http://cristiancudizio.wordpress.com
Jimmy
Dear,
sorry litle mistake, the oracle version we're using is 10.2.0.3 .
Jimmy
>
> - Tekst uit oorspronkelijk bericht weergeven -
After reboot of the server, still the same problem :-(
It runs fine for a couple of hours, but after that, threads are
increasing,
session seem to disappear properly, but the thread remains .
Cristian Cudizio
mmmh, i suspect it's slightly different from my problem. Threads on
windows are process on Oracle, if you count
a lot of process on v$process it seems as if clients are closing
sessions but non disconnecting, as suggest
Thomas Kyte on his last book you can see that by connecting with
sqlplus, then launching "disconnect" command,
the session is being closed, but the shadow process (so the windows
thread) remains.
Then remains my suspect that Oracle is unable to cleanup such
processes if clients close uncleanly. Maybe that
activatinc DCD (Dead Connection Detection) may help, but it is subject
to TCP timeout that on windows defaults to 2 hours.
Bye
Jimmy
>
> - Tekst uit oorspronkelijk bericht weergeven -
DCD is already active SQLNET.EXPIRE_TIME is set to 10 minutes
EscVector
>
> > - Tekst uit oorspronkelijk bericht weergeven -
>
> DCD is already active SQLNET.EXPIRE_TIME is set to 10 minutes
So what is the issue that this is causing?
EscVector
wrote:
NO CONNECTIONS
C:\work\admin\sysinternals>pslist |find "oracle"
oracle 2036 8 25 << Threads 541 655484
0:00:07.687 1:23:36.435
Launched 10 connections via sqlplus and stared closing the cmd
windows via the x.
C:\work\admin\sysinternals>pslist |find "oracle"
oracle 2036 8 35 << Threads 700 669164
0:00:07.828 1:30:10.060
C:\work\admin\sysinternals>pslist |find "oracle"
oracle 2036 8 32 << Threads 649 665336
0:00:07.843 1:31:20.795
C:\work\admin\sysinternals>pslist |find "oracle"
oracle 2036 8 30 << Threads 623 662332
0:00:07.843 1:35:36.998
C:\work\admin\sysinternals>pslist |find "oracle"
oracle 2036 8 29 << Threads 608 661024
0:00:07.843 1:35:43.513
C:\work\admin\sysinternals>pslist |find "oracle"
oracle 2036 8 28 << Threads 593 659656
0:00:07.843 1:35:59.341
C:\work\admin\sysinternals>pslist |find "oracle"
oracle 2036 8 27 << Threads 578 658356
0:00:07.859 1:36:15.795
Here's where it get interesting....
C:\work\admin\sysinternals>pslist |find "oracle"
oracle 2036 8 26 563 657064 0:00:07.937
1:42:09.795
Last cmd window open with sqlplus connected.
Issued Disconnect and checked threads with cmd window still open but
sqlplus closed.
C:\work\admin\sysinternals>pslist |find "oracle"
oracle 2036 8 26 << Threads Remain 563 657064
0:00:07.953 1:43:07.138
Double check....
C:\work\admin\sysinternals>pslist |find "oracle"
oracle 2036 8 26 563 657064 0:00:07.953
1:43:24.888
Closed cmd window and viola:
C:\work\admin\sysinternals>pslist |find "oracle"
oracle 2036 8 25 << Tread is gone.... 546 655484
0:00:07.968 1:43:31.060
Are you launching the connections via cmd prompt from batch file?
Are users using sqlplus and not exiting the window?
check to make sure the cmd prompts are not really the issue.
If they are hanging around after disconnect, the threads will increase
and never go away....
Anytime a cmd prompt is not exited, it starts to kill the machine,
slowly, but surely.
Running batch this way is just plain bad. But this might not be your
issue.
Jimmy
>
> - Tekst uit oorspronkelijk bericht weergeven -
Hi,
in our case the number of threads is not decreasing,
if the clients close their program, the number of threads stays the
same
Cristian Cudizio
I'm not sure to hava understood your tests, however if i close cmd
window with "X" oracle
cleanups session and process without problem, i think that is an
handled situation, instead
you can try unplugging your network cable for a while.
What i mentioned was:
sqlplus user/password@database
SQL> disconnect
Disconnesso da Oracle Database 10g Release 10.2.0.2.0 - Production
SQL>
You will see that on your v$session your session has disappeared,
but corresponding process in v$process still remains until you do
SQL> exit
C:\Documents and Settings\ccudizio>
EscVector
What's the program?
yon...@yahoo.com
wrote:
That behavior, that a client issues a disconnect sqlplus command but
the server process (Windows thread) remains, is something I can't
figure out the reason for. It's like Oracle thinks the disconnect
command is only temporary and the user will log back in soon, so the
server process may as well stay around and keep the SQL*Net
connection. But the fact is that if you connect again in the same
sqlplus window, that server process does some cleanup and exists,
immediately followed by a new server process being created to serve
this new client request. So what's the point of keeping the old one
after disconnect?
By the way, DCD won't work because the SQL*Net connection is left
intact and TCP connection state is still established.
Yong Huang
Cristian Cudizio
> By the way, DCD won't work because the SQL*Net connection is left
> intact and TCP connection state is still established.
>
but if there is not a client process (that opened the socket)
responding i can't figure out why it will
not detect a death connection ....
> Yong Huang
yon...@yahoo.com
wrote:
...
>
> > By the way, DCD won't work because the SQL*Net connection is left
> > intact and TCP connection state is still established.
>
> but if there is not a client process (that opened the socket)
> responding i can't figure out why it will
> not detect a death connection ....
>
> > Yong Huang
>
> Bye
>
> Cristian Cudizio
>
> http://oracledb.wordpress.comhttp://cristiancudizio.wordpress.com
Isn't it true that DCD can only tear down a connection and terminate
its server process if the client doesn't respond to an "Are you
there?" packet? If the connection is healthy, the software client
(sqlplus) does respond with a "Yes, I'm here", although the human
client (user) can't type any command, other than exit and connect.
Yong Huang
Cristian Cudizio
Maybe i've not understood,
DCD works at lower level, SQLNET, Server sends a TCP packet and waits
a ACK,
because this appens the socket must be open and i espect that this
does not happen if
client application has closed. I'm wrong?
EscVector
So we're back OPs issue... If the client is closed, why are the
processes staying around?
Jimmy
>
> - Tekst uit oorspronkelijk bericht weergeven -
The program is an ASP application on a Windows 2003 Webserver.
Jimmy
When i check for TCP connections on the server,
i see a lot of TCP connection in the state CLOSE_WAIT; these
connection are from the Webserver ( hosting the ASP application )
EscVector
Guess: It's a connection pool issue from the server and it's not
closing the connections.
Charles Hooper
If you have a copy of "Expert Oracle Database Architecture" on hand,
take a look at chapter 5, which discusses processes. Brief quotes of
that chapter:
pg 162 "Technically, that command [disconnect] should be called
DESTROY_ALL_SESSIONS instead of DISCONNECT, since we haven't really
disconnected physically." "Note: The true disconnect in SQL*Plus is
'exit,' as you would have to exit to completely destroy the
connection." "We have, however, closed all our sessions. If we open
another session using some other account ..., we can see that we have
no sessions - but we still have a process, a physical connection."
pg 159 "It surprises many people to discover that a connection is not
synonymous with a session."
pg 155 "When I use the term 'process' in this chapter, consider it to
be synonymous with the term 'thread' on operating systems where Oracle
is implemented with threads (such as Windows)."
Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc.
joel garry
This means the ASP application is not properly closing _a_ TCP
connection.
See http://support.microsoft.com/kb/137984 "consider the following
scenario:..."
jg
--
@home.com is bogus.
http://lauren.vortex.com/archive/000251.html
EscVector
Is this an in-house app or 3rd party? Sounds like .Net connection
pool is not being used correctly.
If .Net, search for stuff by Mark Williams http://oradim.blogspot.com/
if this is the case. He's got a good chapter in his book
about .Net.ADO connections.
yon...@yahoo.com
I assume your "server" here is the Oracle server, not the Websphere.
How long do these connections stay in CLOSE_WAIT state? What are the
corresponding connection states on the Websphere side?
We thought your application did something similar to sqlplus's
disconnect, but it doesn't look like, because in that case you would
see ESTABLISHED state. I'm not sure if there's some bug in .net
connector. You may want to search on Metalink or open a Tar.
Yong Huang
Jimmy
>
> - Tekst uit oorspronkelijk bericht weergeven -
Yes, probably,
have to check that with the developper
But this doesn't explain why this application worked fine with Oracle
9.2. This issue only appeared since we moved the database to a Oracle10
Jimmy
the connections in CLOSE_WAIT are on the Oracle server, on the
webserver i don't have those connections.
These connections stay i CLOSE_WAIT state, until we restart the
Database.
Should Dead Connection Detection not clean this up ?
EscVector
http://www.sunmanagers.org/pipermail/summaries/2006-January/007068.html
CLOSE_WAIT means that the local end of the connection has received
a FIN from the other end, but the OS is waiting for the program at the
local end to actually close its connection.
LAST_ACK is what kill the connection, but this comes from the client
TCP connection. Something in the connection path is holding these
connections "open".
I'm betting the issue on the webserver. If not, do you have a load
balancing hardware between the webserver and the database?
EscVector
sam
>
> - Show quoted text -
I had this issue one time, I don't remember the oracle version, but
the issue was that when the connection becomes idle for a time, oracle
doesn't close the session, it will only actualy close it when the user
issues another command against the database, because
sam
>
> - Show quoted text -
I had this issue one time, I don't remember the oracle version, but
the issue was that when the connection becomes idle for a time, oracle
doesn't close the session, it will only actualy close it when the user
issues another command against the database, because the application
server wasn't closing the connections
It was an websphere server
Jimmy
Some updates on this problem, we probably found it. It had nothing to
do with application, but it's the combination of Oracle 10.2.0.3 and
Windows 2003 server.
After searching on metalink, we found these 2 bugs:
5573896: DCD IS NOT WORKING IF SET MORE THAN 1 MINUTE
5475041: PROBE PACKETS ARE NOT SENT IF SQLNET.EXPIRE_TIME IS GREATER
THAN 1
This sounds like our problem, we had sqlnet.expire_time = 10
so we changed it to 1 minute. But no result. The number of connections
was still increasing.
Then i found Bugno. 5607984:
ORACLE DOES NOT CLOSE TCP CONNECTIONS. REMAIN IN CLOSE_WAIT STATE
This is exactly what happens on our site except:
We don't use OC4J application, and our product version is 10.2.0.3
We tried the workaround , and removed the SQLNET.EXPIRE_TIME parameter
completely from the sqlnet.ora file.
And since then our system is stable again !!!
No more connections in CLOSE_WAIT state !!
Our server is now running fine for already 4 days. ( before we had to
restart the database every 4 hours ! )
So it seems that is has something to do with the SQLNET.EXPIRE_TIME
parameter.
According to Note:342443.1
the bug 5607984 was solved in 10.2.0.3 Patch 1.
So we installed the latest patch bundle ( 10.2.0.3 Patch 6 ) on the
test server.
We're currently monitoring that system. The sqlnet.expire_time
parameter is set to 10
on this testserver
If everything seems fine on the testserver, we'll probably patch the
production server this weekend, patch 6.
Thanks to all people we helped/replied.