Are multiple sessions very dangerous ? — oracle-tech

    Forum Stats

  • 3,716,128 Users
  • 2,242,960 Discussions
  • 7,845,836 Comments

Discussions

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Are multiple sessions very dangerous ?

LuckyLuke82
LuckyLuke82 Member Posts: 92
edited August 2019 in ODP.NET

Hi,

I couldn't find anywhere answer for this. I'm asking this because I use same connection for multiple things. One of them is that I export data into Excel without blocking UI thread - in new thread.

Meanwhile users can open another window and export data into Excel there as well, simultaneously. Code works and I tested with 10+ windows opened (+ exporting in all),

but from Toad client I noticed that 5 more session were opened in Session browser.

Is that dangerous ? - I'm looking for some simple answer, thank you.

Best Answer

  • AndrewSayer
    AndrewSayer Member Posts: 12,998 Gold Crown
    edited August 2019 Accepted Answer
    LuckyLuke82 wrote:Hi,I couldn't find anywhere answer for this. I'm asking this because I use same connection for multiple things. One of them is that I export data into Excel without blocking UI thread - in new thread. Meanwhile users can open another window and export data into Excel there as well, simultaneously. Code works and I tested with 10+ windows opened (+ exporting in all), but from Toad client I noticed that 5 more session were opened in Session browser. Is that dangerous ? - I'm looking for some simple answer, thank you.

    Simple answer: Not dangerous.

    You would normally expect many hundreds (or thousands) of sessions to be connected at any one point in time in a live DB and there's no problem there.

    It becomes an issue when you don't manage the connections properly and forget that you've left some with open transactions that can block others. ODP.net doesn't really let you do this very easily - if you've opened a transaction then you will keep working in that session.

    Default connection pooling will result in a new session being created for separate calls up to a limit and then existing sessions will just be reused - it is up to you to make sure you clean up these sessions and don't leave them with state (global variables etc).

Answers

  • AndrewSayer
    AndrewSayer Member Posts: 12,998 Gold Crown
    edited August 2019 Accepted Answer
    LuckyLuke82 wrote:Hi,I couldn't find anywhere answer for this. I'm asking this because I use same connection for multiple things. One of them is that I export data into Excel without blocking UI thread - in new thread. Meanwhile users can open another window and export data into Excel there as well, simultaneously. Code works and I tested with 10+ windows opened (+ exporting in all), but from Toad client I noticed that 5 more session were opened in Session browser. Is that dangerous ? - I'm looking for some simple answer, thank you.

    Simple answer: Not dangerous.

    You would normally expect many hundreds (or thousands) of sessions to be connected at any one point in time in a live DB and there's no problem there.

    It becomes an issue when you don't manage the connections properly and forget that you've left some with open transactions that can block others. ODP.net doesn't really let you do this very easily - if you've opened a transaction then you will keep working in that session.

    Default connection pooling will result in a new session being created for separate calls up to a limit and then existing sessions will just be reused - it is up to you to make sure you clean up these sessions and don't leave them with state (global variables etc).

  • LuckyLuke82
    LuckyLuke82 Member Posts: 92
    edited August 2019

    What do you mean by cleaning up sessions ? .... If you mean connections - I close all connections properly, my code Is in Using block + I call connection.Close() inside It. But session is still open after query result, at least that Is how I see It in Toad.

    And - does ODP.NET provide any feature (method etc.) in which I could determine how many connection pools I use at the moment ?

  • Alex Keh-Oracle
    Alex Keh-Oracle Posts: 2,753 Employee
    edited August 2019

    In general, all ODP.NET public static methods are thread-safe. Instance methods do not guarantee thread safety.

    You can use performance counters to track ODP.NET connection pools. Here's a video on how to set that up: https://www.youtube.com/watch?v=mNwaYIazxlw

    LuckyLuke82
  • LuckyLuke82
    LuckyLuke82 Member Posts: 92
    edited August 2019

    Thanks Alex, I'll take a look at the video.

Sign In or Register to comment.