Re: oracle threads increasing, even after closing sessions

On Jun 14, 6:54 pm, Cristian Cudizio <cristian.cudi..._at_yahoo.it> wrote:
> On Jun 14, 10:33 am, Jimmy <jimmy.coole..._at_gmail.com> wrote:
>
>
>
> > On 14 jun, 09:48, Jimmy <jimmy.coole..._at_gmail.com> wrote:
>
> > > On 13 jun, 21:09, "fitzjarr..._at_cox.net" <fitzjarr..._at_cox.net> wrote:
>
> > > > On Jun 13, 1:50 pm, Jimmy <jimmy.coole..._at_gmail.com> wrote:
>
> > > > > Dear,
> > > > > 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 ?
>
> > > > 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
>
> > > Dear,
> > > sorry litle mistake, the oracle version we're using is 10.2.0.3 .- Tekst uit oorspronkelijk bericht niet weergeven -
>
> > > - 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 .
>
> 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
>
> Cristian Cudizio
>
> http://oracledb.wordpress.comhttp://cristiancudizio.wordpress.com

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 Received on Thu Jun 14 2007 - 09:59:52 CDT