Killing users
No mater what data warehouse designers do, data warehouses are manifestly slower than transactional systems or at least they seem so to users. Queries that can be quickly expressed in words such "How many bottles of ketchup did I sell in Seattle last week?" or "Which oil stocks moved more 15 points today?" can involve large amounts of fetching data, summing it, sorting it and all manner of other operations to get it back to user. With luck (I meant design) the right summaries and indexes are in place and the minimum of work is required to get the result back, but other times the hour glass pops up on to the screen and the user is in for a wait whilst all those ones and zeros are dragged from the deepest recesses of the disk, massaged through the processors and served up the user on some pretty screen.
The real problem is that some users don't have the ability to wait for something to comeback; the instant messaging, instant coffee culture has eroded their patience. So what do they do, they shutdown their web client and think that is the end of it. Sadly many web based reporting tools just carry on with their query in the background - they do not know the human at the other end has the attention span of a goldfish and is no longer there. There are also the impatient users that stop their slow query session and then fire off the same query in a new session in the hope that it has become quicker, perhaps this is the same as shouting to get something done sooner? But all of these orphaned queries drain resource, they soak up IO, and for parallel queries, using up query slaves that could be doing useful things for others.
I have seen users forgetting to select the date in a query predicate and setting off a massive scan of the database (why to they always look for low level data when that happens - so get the biggest tables?) get bored and then try it again, twice. There seems little to do but kill that user's database session.
Killing users (in the database sense and not the capital sense) is a privileged task - in Oracle you need ALTER SYSTEM - and that's a right that should not be devolved to anyone. But having a DBA available to stop rouge sessions is not always an option. There is a good workaround in that you could write a package to kill specific user sessions (add lots of safeguards to ensure only user query sessions can be killed), the packaged should be owned by a user with ALTER SYSTEM privileges (a direct grant and not by role) Finally grant execute on the package to the user that is allowed to use it. And there we have devolved but controlled authority.