[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1397
  • Last Modified:

number of threads and sessions

Oracle 9.0.1.5.1 on windows 2003 Enterprise server.


SELECT COUNT FROM V$SESSION display 142 sessions
SELECT COUNT FROM V$PROCESS display 144 processes

Windows task manager shows 279 threads
PrefMON shows 279 threads


What is the exact number of session and threads actually in the system?



0
salasadi
Asked:
salasadi
  • 9
  • 7
  • 4
1 Solution
 
Mark GeerlingsDatabase AdministratorCommented:
Why does it matter?  The number of sessions, processes and threads will likely vary frequently in most Oracle systems on WIndows as users connect or disconnect.  Also, if you have any database jobs running (using dbms_job) they will open a new session (and thread) when they start, and close them when the job is done.

I managed an Oracle9 on WindowsServer2003 for a couple years, and spent some time tuning and tracking resource usage, but I never tried to reconcile these quantities with each other.  What do you hope to gain by knowing exactly how many sessions, processes and threads are active at any time?  Are you trying to find out how many can be supported on Windows?  If so, you can change that by using "orastack.exe".  We managed to support 530 concurrent, dedicated sessions (with about that same number of processes and threads) by using "orastack.exe" to reduce the memory used per session.
0
 
salasadiAuthor Commented:
Mark,
Thanks for your response. Once I tried to do orastack but I get an error:

C:\Documents and Settings\Administrator>orastack.exe 500000
Couldn't open file with CreateFile()
GetLastError() == 2

the purpose of my question is reduce amount of memory and resources.  I have problem with the existing server so I have to move my DB to temp server.  the temp server have 800 MB only.  by noon I start get calls from end users who connect vua JDBC stating that they could not get in. I can login with no problem, there is no alert log nor listner log about the issue.  
I want to be able to see how much memory per session the system need.  I was told by Oraclle that thread = current connection and = session.  
0
 
Mark GeerlingsDatabase AdministratorCommented:
Yes, those numbers will be approximately (or maybe even exactly) equal, that is: "thread = current connection and = session".

But, your problem isn't determining exactly how many of those you have, it is more an issue of how much memory each of those is using.

Your database and listener must be shut down when you run orastack, and you may need to provide the full path to the Oracle executables when you run it.  (Our Oracle_home was: "H:\Ora_920").  Here is a copy of what we used at one time:
orastack.exe H:\ora_920\bin\oracle.exe  409600
orastack.exe H:\ora_920\bin\tnslsnr.exe 409600
orastack.exe H:\ora_920\bin\sqlplus.exe 409600
orastack.exe H:\ora_920\bin\sqlplusw.exe 409600

I think later we set the values even lower, around 250MB or 300MB, but I didn't save a copy of that.
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
salasadiAuthor Commented:
you mean 250 KB NOT MB?  is 409600 = 4 MB
0
 
Mark GeerlingsDatabase AdministratorCommented:
Oops!  You are correct, I meant KB, not MB.  We ran our application with the memory per session around 250000 - 300000 bytes, instead of the default 1MB per connection, and we did not see any memory-related errors.  We did have stability issues though with Oracle on Windows with our user load of about 500 connections, so we have switched to Linux.  That was not an easy move for us since we had no UNIX or previous Linux experience, but the stability of Linux and Oracle on Linux is great.  We used to average at least one O/S or Oracle crash per week.  With Linux, we've only had one O/S crash in almost six months, and no Oracle crashes.
0
 
salasadiAuthor Commented:
Mark,
we are moving to Linux 64 BIT next year, for now we have to stack with windows.  
we also looking to move to 10g, cuz 10g can handle pool connection better then 9i.
i will try the oratask in my Dev box tday and hope it would work out for us.

Thanks
0
 
schwertnerCommented:
Normally Oracle uses 3-5 MB for an open session.
In average 4 MB nevertheles Oracle says it uses 5 MB.
Sessions are sessions - you can only limit them using sessions parameter.
Definitelly you will not be able to have more 30-50 sesions with this amount of RAM.
The trick that can slightly help you is to ask the Listener periodically to kill
dead session.
Set in SQLNET.ORA

SQLNET.EXPIRE_TIME = 10

every 10 minutes the listener will send probes to all clients and will
release the dead sessions.

Next step - buy more RAM :-))))
0
 
salasadiAuthor Commented:
well,
the productions server have 8 GB and  dual process.  but the tmp server is the issue. we have about 6 Oracle productions.  the larges server that handle JDBC application and has 700 GB size of DB.   each user open session and the lisnter refuse new connections after reaching 200-250 connections.  winows startle when the virsual byte usage reached 1.8 GB. now I have 2003 Enterprise with /3G switch, but the problem did not go away until I lower the SGA size.  
But what mark suggested was great, becuase I know my user session don't use the 5 MB per session.  I think the sql time out is great idea too.
0
 
schwertnerCommented:
Every sessions uses from 3 to 5 MB RAM.

This is the rule. I think this are buffers for Oracle NET interconnections needs, but nave not time to investigate it.

Do not agree with desimination of wrong basic information, so please look bellow:

A common question asked by customers is, how large can I set db_block_buffers
to ? Below is an example for a system running with 50 users and a 4K block
size :

   Address Space     : 2,000,000,000
   Average Code Size :    50,000,000 -
   java_pool_size    :             0 -
   shared_pool_size  :    50,000,000 -
   large_pool_size   :             0 -
   Session Memory    :   150,000,000 - (50 users with average session = 3MB)
                      --------------  
   db_block_buffers = 1,750,000,000 / 4096 = 427,200

Prior to 8.1.5 the Getting Started Guide for Windows NT stated that the
db_block_buffers parameter was restricted to 3200, this is not correct and
is documented in ..... Please note it may not be possible to achieve
exactly the number of user sessions listed above due to address space
fragmentation described in section 4.
0
 
Mark GeerlingsDatabase AdministratorCommented:
No, schwertner, I disagree with your statement that: "Every session uses from 3 to 5 MB RAM", at least for Oracle on Windows.  We ran a production Oracle9 instance on Windows for two years and that supported up to 530 concurrent, dedicated (not shared server) connections after we used "orastack" to reduce the memory per connection down to 300K.  That meant that we had about 1.5GB of RAM for user sessions (300K * 500 users) plus an sga_max_size of 1G and some other Oracle overhead that could all fit within the 3GB per process limit that 32-bit Windows imposed, when we used the /3GB switch in the boot.ini file.  If each session actually used 3M, that would be 15GB of RAM for 500 sessions!  We had nowhere near that much physical RAM on our server (we had 8GB, but I used 1GB of that for a RAMdisk for our temp tablespace, so only 7GB was available for the O/S).  I also used the Windows Performance Monitor regularly to make sure that virtual RAM was hardly ever used.

Also, db_block_buffers (or db_cache_size) is un-related to the number of sessions.  Now, if the number of sessions is high like ours was, then the value for db_block_buffers or db_cache_size has to be reduced (we used 400M for that) due to the physical memory constraints of the O/S or the hardware.  It would be possible to set up an instance with a large value for db_block_buffers, connect with only one user session and do a query from a table larger than the value for db_block_buffers, and have the entire cache filled even though there is only one user session in the database!
0
 
salasadiAuthor Commented:
Mark,
I tried the orastack.exe as you suggested, but I still get an error:

orastack.exe c:\oracle\ora90\bin\oracle.exe  409600

I shutdown Oracle, listner, and try it. I got the

orastack.exe c:\oracle\ora90\bin\oracle.exe  409600
Couldn't open file with CreateFile()
GetLastError() == 5

if we set the 409600 and user sessin exceed that value, what would happen?

Thanks
0
 
Mark GeerlingsDatabase AdministratorCommented:
You do have to run the orastack command with a user account that has "write" permissions in your Oracle_home directory.  Are you using the same Windows account when you run orastack that normally runs your database software?

If you succeed in running orastack, and get the value set lower, then if a session ever needs more memory than that, you will see a problem.  Maybe it will just kill the one user session, or maybe it will cause more serious problems, like a database crash, "application error" or even a Windows crash.  I don't know for sure, since as far as I know, we never had a session exceed the value we set.  But maybe we did, and maybe that caused the once a week or so crashes that we had with Oracle on Windows with a large number of user sessions.
0
 
schwertnerCommented:
Have nothing against your opinion.....
0
 
salasadiAuthor Commented:
the system currently have:

SQL> select count(*), status from v$session group by status;

  COUNT(*) STATUS
---------- --------
         9 ACTIVE
       164 INACTIVE

I set the SQLNET.EXPIRE_TIME = 10

then I check it in 30 minute later:

SQL> select count(*), status from v$session group by status;

  COUNT(*) STATUS
---------- --------
         9 ACTIVE
       164 INACTIVE

does this means that the inactive sessions are not expired by the added switch?


0
 
schwertnerCommented:
The added switch will delete only the orphan session (i.e. sessions that has lose connection to the client computer).

Also now I gues that you use SUN Solaris! Right?

If I am right may be you will wonder how I guess this from my place.

The reason is that the gyants also are weak.

Solaris has a bug (this is not Oracle bug! this is Solaris bug!) that prevents more then 256 sessions
via TNSNAMES.ORA way (or mechanism). Because it has an internal buffer with handles that can
have only 256 entries! Great!

There are 2 workarounds:
1. Hard coding the connection string in the JDBC cal
2. Use Solaris patch.

0
 
salasadiAuthor Commented:
Schwertner,
we have Oracle in Windows 2003.   but, may be Windows have th same issue.  

I did use orastack last night to lower the oracle.exe to be 204800 (2 MB). Now my session is 298 and my threads is 302.  
0
 
salasadiAuthor Commented:
in other note,
I did the orastack:

C:\>orastack.exe C:\oracle\product\10.2.0\db_1\BIN\oracle.exe  204800

Dump of file C:\oracle\product\10.2.0\db_1\BIN\oracle.exe

Current Reserved Memory per Thread  = 1048576
Current Committed Memory per Thread = 4096

New Reserved Memory per Thread = 204800

this is 10g r 2, and I think the oracle.exe seem to be 1 MB not 4 MB

Current Reserved Memory per Thread  = 1048576

am I right?
0
 
Mark GeerlingsDatabase AdministratorCommented:
I think my suggestions helped significantly and may have solved the real problem, even if they did not exactly answer the original question.
0
 
salasadiAuthor Commented:
markgeer  I agree, you give me a good way to deal with the issue, therefore I accept your suggestion :)

Thanks
0
 
Mark GeerlingsDatabase AdministratorCommented:
But a "C" grade?
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 9
  • 7
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now