Too many sessions - performance issues!
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 9 of 9

Thread: Too many sessions - performance issues!

  1. #1
    Join Date
    Aug 2000
    Posts
    236
    I have a 10GB database with around 30 concurrent users. Using 8.1.6 on NT (Dont laugh!)

    With just 15 connected users, i find that there are 150 sessions and the performance has slowed down considerably.
    I just exp/imped from 7.3.4 to 8.1.6.

    How can I go about tuning this? When it 7.3.4 I has an acceptable response time.

    Do you thing switching to MTS will help?

    Nizar

  2. #2
    Join Date
    Oct 2000
    Location
    Saskatoon, SK, Canada
    Posts
    3,925
    MTS would help you only to multiple connection requests to be served by one server thread. You can set limits on that too. If your config was on a dedicated server mode, I'ld suggest changing to shared server by using MTS. There could be some dead sessions that could hold some time in your case. To get rid off them automatically, use the profile to set some idle time.

    For more details on setting up the MTS, refer the sqlnet or Net8 docs. If you have any questions, pleas feel free to ask, we would be glad to help you.

    Sam
    Thanx
    Sam



    Life is a journey, not a destination!


  3. #3
    Join Date
    Aug 2000
    Posts
    236
    Sam,

    Thanks. I can go about changing the server mode from dedicated to MTS.

    But I need to decide whether this will solve the problem of excessive sessions.

    Thanks,
    Nizar

  4. #4
    Join Date
    Jul 2000
    Posts
    243
    Hi

    look at v$session, there you will find the os user, db user and terminal. this way you will know wich user in wich terminal has created a session. better, when you run
    select ...
    from v$session
    where user_... not in ('SYS'...) sorry i can not help you more but i have no access to a client with sql on it.

    any way , this way you can see, for example, if any user is connected more the once.

    hope this helps.

  5. #5
    Join Date
    Aug 2000
    Posts
    236
    Shawish,

    I have looked at it and know for sure that the application from each client machine is creating almost 15 sessions.

    10 users and the total session is 150. I dont understand why this is happening and how to solve it.

    Nizar

  6. #6
    Join Date
    Jul 2000
    Posts
    243
    Hi Nizar

    what sort of application are you running? is it a java application calling the db? are opening a deddicated session for every requset to the database?

  7. #7
    Join Date
    Aug 2000
    Posts
    236
    Jurij/Shawish,

    Yes, there was an application upgrade also. This is a VB driven application for a telecom company handling all their work orders for an entire country (Saudi Arabia).

    The applications guys tell me that the upgrade is supposed to better performance. I honestly doubt it.

    Well, I am gonna do some more testing to see what is causing these sessions to spawn.

    Thanks guys,


  8. #8
    Join Date
    Jul 2000
    Posts
    243
    Hi Nizar

    i talk to a freind and he told me that they had this problem when upgrading from 7.3 to 8.0, but he did not remmember how and why (it was 1.5 years ago). i looked at the metalink and found no reference. i think you better ask oracle support on this one.

  9. #9
    Join Date
    Aug 2000
    Posts
    236
    Shawish,

    I ran some tests on the server and found out that the data hit rate for the block buffers in memory is 55%

    This is totally unacceptable. I plan to increase the size of data buffers, but my question for now is:

    Most of the sessions which are waiting are a SQL clients waiting for server? Do you think, I might have SQL Net incompatibility problems. Server is 8.1.6, Clients are all 7.3.4.

    Thanks,
    Nizar

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