• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 644
  • Last Modified:

Oracle sessions and processes generated by a .NET web app

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?
0
miyahira
Asked:
miyahira
  • 3
  • 2
4 Solutions
 
Helena Markováprogrammer-analystCommented:
"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
0
 
slightwv (䄆 Netminder) 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.
0
 
miyahiraAuthor 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?
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
slightwv (䄆 Netminder) 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.
0
 
miyahiraAuthor 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...
0
 
slightwv (䄆 Netminder) 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)
0

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now