Avoiding The Query From Hell
The “query from hell” is the DBA’s nightmare. It is a query that occupies the entire resource of a machine and effectively runs for ever, never finishing in any time-scale that is meaningful. Naturally, it is a query to be avoided. Given the vast quantity of data in a data warehouse, such queries are all too possible to generate. The problem is that it may be impossible to tell the difference between an acceptable query that takes up to 48 hours to run and one that will run for much longer and effectively never finishes. Unless it is possible to measure exactly how far a query has progressed and how much more processing it has left to do, it will be impossible to predict when a query will end. The only way of preventing such queries is by controlling exactly how much resource a query can have.
This can be done in two ways. First, profiles can be used to limit the amount of resource a user process can use. When that amount is exceeded, the query will be terminated automatically. This will not prevent a query running, but it will stop it running for ever using vast amounts of resources. The advantages of profiles are that they are automatic, and they require no external control. The disadvantage is that they kick in only after a lot of resource has already been wasted. Second, queuing of queries can be used to control their resource usage. If all . queries must be submitted via the query manager, then the degree of parallelism and other resources of the query can be controlled. Any query that is likely to run for extended periods of time can be limited in what resource it will use. This will mean that the query will take longer to run, but it will stop a single query interfering with the other processing necessary to maintain and run the data warehouse.
