How to limit the number of simultaneous queries for a given User
13030Sep 9 2010 — edited Sep 13 2010I have 1 user that is used for a client app to make a query. Sometimes this user/app submits 1 query at a time, sometimes 100.
I want a way to scale this user back so that only 10 queries execute at a given time. If they submit 100, then the other 90 should just sit and wait in a queue.
I have heard of advanced queues, but I think this would require the client application to rewrite its queries as plsql API calls, and make a second call to get the result. I still only want the client application to make 1 call to make the query and get the results.
Is there a way to do this with some simple database option?
I have also heard of Oracle Resource Manager, but I don't want the queries to time-out or raise an Oracle error when there are too many -- I just want the queries to queue up and return when they can. I also don't want to use only CPU-limits per user, because can you imagine how long it will take if 100 queries run at the same time using a limited amount of CPU!?!?
What if I changed the scenario from a single SQL query to a single PLSQL function call?
On Oracle 10g.