Killing users
August 15th, 2006 by Peter Scott
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.
August 16th, 2006 at 1:06 pm
and why do they always something like all the general ledger detail records for the last three years and then complain when it takes time to return millions of rows?
i tride to put in the package thing, but got shot down.
August 16th, 2006 at 4:11 pm
Doesn’t resource manager have something that would help with this? I don’t recall exactly. Maybe there was an issue with PQ also though.
August 16th, 2006 at 9:33 pm
There are indeed a number of features in Resource Manager 9i and up which will prevent this sort of thing from happening in the first place, or dealing with it when it does.
For example, you can set a maximum number of users who can be active in each consumer group by specifying the singularly unattractive parameter ACTIVE_SESS_POOL when invoking dbms_resource_manager. That won’t stop a user firing off a silly query, but it will mean there’s a limit on the number of silly users who can do so simultaneously.
Then there’s MAX_ESTIMATED_EXECUTION_TIME: if a query is deemed by the optimiser to be likely to take longer than this amount of seconds, the query doesn’t start.
And there’s also SWITCH_GROUP and SWITCH_TIME parameters, which will switch a user whose query runs for longer than a specified time into a different consumer group (and thus scale back that user’s possible consumption of CPU, for example). Throw in an SWITCH_ESTIMATE=>TRUE, and the switch to the lower group will happen before the query runs at all just because the optimiser thinks it will be excessive. Set ESTIMATE=>FALSE, and the switch happens only after the specified time has actually elapsed.
Resource Manager is one of those technologies which has been around for a while, and whilst it was perfectly OK to ignore it for a few versions because it was so limited and/or buggy, it’s now getting to the point where you can’t afford to ignore it any longer. RMAN was one such technology. Resource Manager is, to my mind, definitely another.
August 17th, 2006 at 6:42 am
Howard, Dave: thanks for the input about resource manager, a good reminder of something I always forget.
The biggest problem for this customer is that all users use a web based tool that validates user identity through LDAP and then connects them to the database through a single Oracle account. Polictically it would be folly to deny access to the CEO whilst silly people are thrashing the machine – but that is not a technical problem. The idea of estimated execution time and switch groups has some attarction and worth a closer look. Thanks!
Dave – Doug mentioned this back in May on his blog but I think his problem was more around Resource Profiles.
Shrek – what can I say?
August 18th, 2006 at 8:56 pm
Bear in mind, Pete, that 10g lets you implement Resource Manager by ‘consumer group mapping’. That is, I don’t need to know who you are to be able to throttle you or promote you: if I can see (for example) your IP address, or if I know the program or module you are using, your O/S username and many others, that is sufficient to say ‘you go in the resource intensive group’, ‘you go to the light users group’.
The big new feature of Resource Manager in 10g is that it is finally n-tier aware, and the presence or absence of a middle tier apps server doing all the connections as a single application user is no longer the problem that it used to be.