How to limit the nymber of sessions that can be opened from a terminal?

Is this possible? Using 9.2.0.4 on an Windows 2003 server.

Say, to limit each user from each PC to two Oracle sessions?

07-21-2004, 10:32 AM

SANJAY_G

There is a profle parameter sessions_per_user BUT I doubt there is any such parameter like sessions_per_machine.

07-21-2004, 10:48 AM

JMac

Aha!
That might do it actually, Sanjay, thanks.

07-21-2004, 11:12 AM

JMac

The developers have now admitted that in order for the Crystal Reports to work (from a VB application) they have to open new ADO connections for each report. So each user now opens multiple sessions rather than one as in the past.

We used to have an average of 50 users = 50 Ora sessions at a time.
Now this could be 200 - 300 hundred???

What ramifications are there by upping the SESSIONS init.ora parameter?

07-21-2004, 11:45 AM

sreddy

Your sessions parm proportinal to your processes parm and inturn its proportional to all the avaialble resources on the database and machine.

Hope you got it by now, impact of changing these parms directly affects resources utilization of database and machine.

07-21-2004, 11:58 AM

JMac

Quote:

Originally posted by sreddy Your sessions parm proportinal to your processes parm and inturn its proportional to all the avaialble resources on the database and machine.

Hope you got it by now, impact of changing these parms directly affects resources utilization of database and machine.

So how would I begin to evaluate the impact? How do I measure the impact of new estimated number of sessions on my resources?

Processing power of the server?

07-21-2004, 12:31 PM

sreddy

Simple way of doing this (rather running behind facts, figures and ratios) is go up in processes, session in increments and look at the database resource utilization (hit ratios of buffer,library cache etc)

You can look at the load levels on Unix to see whats the usage of machine resources and look at the statspack report to see the usage of database resources while you bump up your processes and sessions in increments.

07-21-2004, 01:15 PM

Mr.Hanky

If he is running Crystal Reports chances are that he is already using really bad vanilla based SQL. I would be carefull about introducting more bad code into the database, that code is probably the reason behind the long running queries in the first place.

Or, there is the old stand by, SMACK THE DEVELOPERS!!
You can never go wrong with that.

MH

07-21-2004, 01:19 PM

slimdave

Quote:

Originally posted by JMac We used to have an average of 50 users = 50 Ora sessions at a time.
Now this could be 200 - 300 hundred???

This doesn't necessarily mean that you'd have more concurrent sessions, does it?

You might like to just watch the memory utilization, and look at some of the automatic pga tuning in Oracle -- in a real emergency you could consider switching to the shared server, but that would do nothing for performance -- it just chokes the maximum number of active sessions at a time, and limits memory usage somewhat.

07-21-2004, 05:48 PM

DaPi

Quote:

Originally posted by JMac
We used to have an average of 50 users = 50 Ora sessions at a time.
Now this could be 200 - 300 hundred???

I have a 3rd party application layer that is profligate with its connections to the db and usually forgets to close them - 300 sessions for 50 users is not unusual! The amount of free, real memory correlates (negatively) only with the number of users - it seems independant of sessions. So I conclude that the majority of the data in memory for inactive sessions gets paged out.

There must be some overhead, but nothing significant that I have noticed.