Re: oracle threads increasing, even after closing sessions

On Jun 14, 6:54 am, 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

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. Received on Thu Jun 14 2007 - 08:42:26 CDT