Reducing impact on server load caused by long but non-priority adhoc queries

I have a couple of applications which do long queries in an OLTP database. They however have a significant impact on database server load.

Is it possible to run them with low priority? Is still intend to allow the user make adhoc queries, b开发者_Go百科ut response time is not critical. Please advice solutions for oracle and/or sqlserver.

If you're using 11g, then perhaps the Database Resource Manager will help you out. The resource manager allows you to change consumer groups based on I/O consumption, something that was unavailable in prior releases. If not, the best you can do is lower priority based on CPU use.

Place resource limits on their accounts via profiles. Here is a link: http://psoug.org/reference/profiles.html

For this you can use Oracle Resource Manager. Most important for this is that you need to have an idea how Resource Manager can pick out which session to throttle. You can have lots of criteria to assign a user to a resource consumer group. Often username is used but this can be a few other things like machine, module etc. See Creating Consumer Group Mapping Rules (http://download.oracle.com/docs/cd/B28359_01/server.111/b28310/dbrm004.htm#CHDEDAIB)

Specifying Automatic Switching by Setting Resource Limits (http://download.oracle.com/docs/cd/B28359_01/server.111/b28310/dbrm004.htm#CHDDCGGG) could be very useful for you since all users are starting in the same OLTP group. Some start long running adhoc queries. You want those sessions to switch to a lower priority group for the duration of that call.

There could be one little snag: if that throttled session has locks, those locks will stay longer and might cause problems elsewhere.