Home » RDBMS Server » Performance Tuning » Maximum # of sessions. What can we do.
Maximum # of sessions. What can we do. [message #194036] Wed, 20 September 2006 08:58 Go to next message
bogey
Messages: 53
Registered: February 2006
Member
This has been a reoccuring problem and I'm assuming that it's due to our developers not properly closing connections. Below is the trace file that indicated to many sessions. What's the best way to handle this?

************************************************
Dump file e:\oracle\product\10.1.0\admin\silo5\bdump\silo5_ora_5324.trc
Mon Sep 18 15:29:29 2006
ORACLE V10.1.0.4.0 - Production vsnsta=0
vsnsql=13 vsnxtr=3
Oracle Database 10g Enterprise Edition Release 10.1.0.4.0 - Production
With the Partitioning, OLAP and Data Mining options
Windows Server 2003 Version V5.2 Service Pack 1
CPU : 4 - type 586, 2 Physical Cores
Process Affinity: 0x00000000
Memory (A/P) : PH:2539M/3839M, PG:4797M/5731M, VA:942M/2047M
Instance name: silo5

Redo thread mounted by this instance: 1

Oracle process number: 0

5324


Died during process startup with error 20 (seq=46904)
OPIRIP: Uncaught error 20. Error stack:
ORA-00020: maximum number of processes (150) exceeded
Re: Maximum # of sessions. What can we do. [message #194049 is a reply to message #194036] Wed, 20 September 2006 09:40 Go to previous messageGo to next message
email.sandeepnaik
Messages: 121
Registered: August 2006
Senior Member
1]increase processes parameters in init.ora to higher value so that you can continue to have other use to access database
2] Run script and find out which user spawns more connection to database. Then troubleshoot more details on why you are seeing that many connections or may be they are not closing open connection.

Sandeep
Re: Maximum # of sessions. What can we do. [message #194280 is a reply to message #194049] Thu, 21 September 2006 06:55 Go to previous message
bogey
Messages: 53
Registered: February 2006
Member
Sandeep, the first thing we don't want to do is change the parameters without knowing why we are getting the errors.

After running Quest Spotlight and Toad, I saw a user application requesting over 40 sessions and holding them for a minute or so. This leads me to believe the app needs to be looked at. We've had issues in the past with cursors and we pin pointed code a the culpret.

thanks Sandeep

Previous Topic: sql query tuning
Next Topic: Inefficient sql
Goto Forum:
  


Current Time: Wed Dec 07 08:21:33 CST 2016

Total time taken to generate the page: 0.07212 seconds