?
Solved

Oracle / SQL /ASP lockups

Posted on 2005-03-09
10
Medium Priority
?
177 Views
Last Modified: 2010-04-16
Hi All,

I have a application where all the users connect via a single oracle user to an oracle database (web based app)

What happens here is that I open a connection on app start and close it on app close, and every other web user that logs in uses this physical connection ( I let oracle handle the connection pooling)

The problem arises if one of the users inadvertantly runs a query which either hangs/takes long time, etc.
The CPU on the server also goes to 100% and eve if I close all the explorer windows, nothing happens as the asp process is still trying to make the oracle call ..

The only solution is then to kill the aspnet_wp.exe process and connect again.

This is obviously not a solution when the app goes out into the world. What is the correct way of handling problems like this?

Txs
Peter
0
Comment
Question by:Pete2003
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 3
10 Comments
 
LVL 14

Expert Comment

by:Thandava Vallepalli
ID: 13494504
You should not use one connectin for entire application.

You have to use one connection for one page... on page_load you create a connection, on page_unload close the connection.
it will improve the performance. Actually when u close the connection it will not be closed. It will be stored in pool.
Next time ( in next page page_load event ) it will get the the connection from pool.

itsvtk
0
 

Author Comment

by:Pete2003
ID: 13494547
Ok please explain this to me as this is quite important ...

Are you saying that if I do a database connect a dnt then disconnect it does not close the connection ?

How does that work .. and how can I verify that it actually is pooled ?

This is important as I'll have about 400 Intranet users on this site ...

Txs
0
 

Author Comment

by:Pete2003
ID: 13494554
Does this also sort out my 'hanging' problem ?

Will the other pages just open new connections if this one is broken ?

0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 14

Accepted Solution

by:
Thandava Vallepalli earned 2000 total points
ID: 13494611
>>Are you saying that if I do a database connect a dnt then disconnect it does not close the connection ?

Yes...

>>How does that work .. and how can I verify that it actually is pooled ?
You will see the performance while browsing the application...

>>This is important as I'll have about 400 Intranet users on this site ...
No problem we are implementing same kind of architecture in our web applications....

itsvtk
0
 
LVL 14

Expert Comment

by:Thandava Vallepalli
ID: 13494619
Does this also sort out my 'hanging' problem ?

Yes....

itsvtk
0
 

Author Comment

by:Pete2003
ID: 13496272
Will check out 1st thing in the morning .. and just made the Q 500 pts just in case :)
0
 

Author Comment

by:Pete2003
ID: 13504893
Ok this doesn't work , as if I have 10 concurrent users using the application oracle opens 10 connections.
0
 

Author Comment

by:Pete2003
ID: 13505042
Also what do I do if I have ascx components on my page ... they also access the database  ... do they open their own connections ? if not what do u recommend ?
0
 

Author Comment

by:Pete2003
ID: 13514528
itsvtk .. u still there :)
0
 

Author Comment

by:Pete2003
ID: 13516234
ok sorted it out myself .. but u pointed in right direction ... txs anyway
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Article by: Najam
Having new technologies does not mean they will completely replace old components.  Recently I had to create WCF that will be called by VB6 component.  Here I will describe what steps one should follow while doing so, please feel free to post any qu…
Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…
Suggested Courses

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question