We help IT Professionals succeed at work.

Oracle sessions and processes generated by a .NET web app

miyahira
miyahira asked
on
Our .NET web app uses ODP.NET for connections and its Oracle User for connecting to database is "webuserOracle". That web app always close connections and dispose connections.

However, on our Oracle 10g database, we see that sessions and processes values of Oracle User "webuserOracle" is always high, as they woudn't close or die.

We have decided to set up on Oracle profile for "webuserOracle" in order to limit the connect time up to 5 minutes.

CREATE PROFILE profile_webuserOracle LIMIT CONNECT_TIME 5;

ALTER USER webuserOracle PROFILE profile_webuserOracle;

Open in new window

Question:

For a web app, limiting connection to 5 minutes, means that the user could interact, say, 2 hours with the web app. The limit of 5 minutes is only for events triggered (like clicking a button) to connect to database. 5 minutes for everything that happened between Con.Open and Con.Dispose:

Dim con As OracleConnection = oraConexion()
con.Open()
''' There'll be a limit of 5 minutes to run the code here
con.Close()
con.Dispose()

Open in new window


A Session in Oracle is allways an open and close connection?
Comment
Watch Question

Helena Markováprogrammer-analyst
CERTIFIED EXPERT
Commented:
"CREATE PROFILE profile_webuserOracle LIMIT CONNECT_TIME 5" means that
a single session cannot last for more than 5 minutes.

I hope this documentation will help you, especially explanation in examples:
http://docs.oracle.com/cd/E11882_01/server.112/e26088/statements_6010.htm#i2065930
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019
Commented:
Do your processes keep increasing or do they stay pretty constant?

You might be seeing the connection pool:
http://docs.oracle.com/html/E10927_01/featConnecting.htm#i1006393

Tripple check the code to make sure all connections are always disposed.  There have been bugs in the past where Oracle thought the connection was still active even though the web app closed it.  We found that manually disposing the connection solved this issue.

Author

Commented:
Thanks for your responses, but I don't undertsand yet the difference between a session and connection for a web application.

For a web application, what is an Oracle session?

Example:
User access through login.aspx and then autenticate it against database.
Then go to WebForm1.aspx, fill data and save.
Then go to WebForm2.aspx and check out some reports.

A database session would start when user make first connection in Login.aspx? After that, he/she will make a number of connections to database in the same session that started at Login.aspx?

Example of connection:
Dim con As OracleConnection = oraConexion()
con.Open()
''' Code to run
con.Close()
con.Dispose()

Open in new window


Or an Oracle session is a connection as described?
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019
Commented:
>>Or an Oracle session is a connection as described?

A session is a connection to the database.

>>A database session would start when user make first connection in Login.aspx?

I hope not.  I would hope the app opens a connection, does what it needs to do then closes the connection.

Author

Commented:
A user interacting with a web app will make tons of connections to database: to login user, to populate comboboxes, to fill forms, to validate input, etc. Those connections and queries to database will last less than 5 minutes.

If an Oracle session is a connection, then it doesn't matter if I setup profile to limit connect time to 5 minutes or 1 minute.

Example:
- Web app makes a connection to database to authenticate user. Open, run code, close and dispose connection. (less than 5 minutes).

- Web app makes a connection to database to fill comboxes. Open, run code, close and dispose connection. (less than 5 minutes).

- Web app makes connection to database to validate a textbox input. Open, run code, close and dispose connection. (less than 5 minutes).
 
and so on...
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019
Commented:
>>then it doesn't matter if I setup profile to limit connect time to 5 minutes or 1 minute.

Correct unless there is an issue with Oracle thinking the connection is still active.  Then Oracle will create a new session in the connection pool when the web app requests one.

This is why I asked if you see connections rise or if they stay pretty steady.

If Oracle is constantly creating new connections there are only a few things that can cause it:
- the app just needs that many
- bad configuration
- you are missing a close/dispose and Oracle is not cleaning it up
- a bug

>>Example:  (less than 5 minutes).

I would sure hope so or I would find another developer and DBA.  They should a few seconds at worst.

What versions are you running?
-IIS
-.Net
-Oracle database (all 4 numbers please)
-ODP.Net on the web server (all 4 numbers please)

Explore More ContentExplore courses, solutions, and other research materials related to this topic.