DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 17

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

  1. #1
    Join Date
    Jan 2000
    Location
    Chester, England.
    Posts
    818

    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?

  2. #2
    Join Date
    Feb 2000
    Location
    Singapore
    Posts
    1,758
    There is a profle parameter sessions_per_user BUT I doubt there is any such parameter like sessions_per_machine.
    Sanjay G.
    Oracle Certified Professional 8i, 9i.

    "The degree of normality in a database is inversely proportional to that of its DBA"

  3. #3
    Join Date
    Jan 2000
    Location
    Chester, England.
    Posts
    818
    Aha!
    That might do it actually, Sanjay, thanks.

  4. #4
    Join Date
    Jan 2000
    Location
    Chester, England.
    Posts
    818
    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?

  5. #5
    Join Date
    Feb 2000
    Location
    Washington DC
    Posts
    1,843
    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.
    Reddy,Sam

  6. #6
    Join Date
    Jan 2000
    Location
    Chester, England.
    Posts
    818
    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?

  7. #7
    Join Date
    Feb 2000
    Location
    Washington DC
    Posts
    1,843
    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.
    Reddy,Sam

  8. #8
    Join Date
    Jan 2001
    Posts
    3,134
    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
    I remember when this place was cool.

  9. #9
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    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.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  10. #10
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    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.

    As Slim says, watch the free memory.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width