| Stop Running on DB after X number of Minutes [message #293908] |
Tue, 15 January 2008 10:26  |
faiz_hyd
Messages: 294
Registered: February 2006
|
Senior Member |
|
|
Hi,
Oracle 10g rel 2
we have an OLTP application using C# .Net ,
Sometimes what happens is when Application Connects to DB (Using connect String from App layer ) and runs some long running reports, if the reports runs for more than 10 min the application times out and through the error to the Browser this is Valid since we have given it in code, But in Database that Query / Report submitted continues to run for ever there by consuming resources,
What we want is ,IS there any setting or Oracle Parameters which says that after X number of minutes if some thing is still running Oracle Should stop it or basically kill and through what ever ORA- error or exception to the browswer , we don't want it run furthur more on the Database.
Please let me know any options to handle this.
thanks
|
|
|
|
|
|
| Re: Stop Running on DB after X number of Minutes [message #293920 is a reply to message #293911] |
Tue, 15 January 2008 10:50  |
faiz_hyd
Messages: 294
Registered: February 2006
|
Senior Member |
|
|
thanks Michel for Quick Response,
That link was informative and this was the point of my intrest
| Quote: |
If a user exceeds the CONNECT_TIME or IDLE_TIME session resource limit, then the database rolls back the
current transaction and ends the session. When the user process next issues a call, the database returns an error.
RESOURCE_PARAMETERS
CONNECT_TIME
Specify the total elapsed time limit for a session, expressed in minutes.
A single session cannot last for more than X minutes.
|
The Problem is we have a connection pool and a connection will be reused and if we keep this setting it will take logon time
from the First time login in this case a connection might be done 43 min before and if a small query that runs for 3 min
and if we set connection time to 45 min it will kill this session also which is wrong,
it should either take the time from last Inactive State to Active State and take this CONNECTION TIME parameter into effect
that will help, Hope i was able to clear my doubt.
Thanks
[Updated on: Tue, 15 January 2008 11:06] by Moderator Report message to a moderator |
|
|
|
| Re: Stop Running on DB after X number of Minutes [message #293928 is a reply to message #293920] |
Tue, 15 January 2008 11:10 |
 |
Michel Cadot
Messages: 68683
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
You can also limit on logical_reads_per_call or cpu_per_call if your report contains a big statement.
Or you can include in your report a check of spent time and then it can decide to make suicide if it exceeds the threshold.
But I always wonder. If the report is necessary then it must go to its end or if it is not necessary why executing it? Just assume it is always too long and never start it.
Regards
Michel
[Updated on: Tue, 15 January 2008 11:11] Report message to a moderator |
|
|
|
| Re: Stop Running on DB after X number of Minutes [message #294223 is a reply to message #293928] |
Wed, 16 January 2008 21:46 |
mkbhati
Messages: 93
Registered: February 2007
Location: Mumbai
|
Member |
|
|
While using connection pooling features in Data providers like OO4O or ODP.Net please do not set CONNECT_TIME or IDLE_TIME limits in Oracle profile. Advisable to use default profile which has very few limits set)for user id used by your application for making connection.
There is no logic to use profile based limitations in a pooled connection. Connection pooling features of data providers are are meant for high throughput handling thousands of application requests/connections via a single database connection thus saving precious database resources.The pooling happens at middle Tier in client used by your application server/web server.I hope its clear.
One more point, please ensure you use a dedicated server for your pooled connection this will give you a good response time. This you can do so by requesting a dedicated connection in TNS connect Identifier used by your application (in tnsnames.ora at your middle Tier client).
By your post it appears like your are using .Net provider. Please use ODP.Net (the .Net provider from Oracle, its part of Oracle client)and do not use Microsoft's .Net provider. You will gain heavily on performance & stability side. The rule is simple while connecting to oracle from any middle Tier always use Oracle's providers. Oracle has providers for almost every scenario and they all are part of oracle client installations but some installs by default while some are optional during Oracle Client installations.
Regards
Manjit Kumar (mkbhati)
[Updated on: Wed, 16 January 2008 22:07] Report message to a moderator |
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
