Pythian Group

Recently, a customer request came in to limit the runtime of a query in a 10g database for a certain user. But since connection pooling was being used, the session itself should NOT be killed.

Now, why would somebody ask for that?

I think a reason to implement such a restriction is, for example, if a website application requests data from the database and the query takes too long, then the user sitting in front of the web browser cancels the request, and so it would be beneficial to also cancel the query and not have it still running in the background. There might be quite a few other reasons why such restrictions should be implemented.

Anyway, so what would be the best solution for this? The Resource Manager, a feature not too often used in Oracle.

Actually, “overlooked” might be the better word—overlooked when investigating performance problems. I imagine it must be a new DBA’s nightmare to be confronted with end user complaints of “bad performance”, only to find after a long search that the Resource Manager has restricted the CPU for a specific user.

But back to the topic. Resource Manager not only allows one to define how much CPU a certain user or group gets, it also lets one switch into lower priority groups and kill a query while leaving the session running. It is like running a SELECT statement in sqlplus, and pressing ctrl-c. The session is still alive, yet the query is canceled.

Sure, the Resource Manager is documented by Oracle, but when I started to test this feature, I came across a few very interesting things that are not as well documented, and that’s what prompted me to write this post:

Read the rest of this entry . . .

By “Good SQL Querying”, I am not referring to “how to make your queries more perfomant.” I am about to go on a mini-rant about how to make readable and self-documenting SQL queries.

One practice that will get me instantly going on a rant is using a comma join. There is NO reason to do the following:

-- uses the sakila sample database
SELECT first_name, last_name, address
FROM customer,address;

What kind of join did the original author intend? A CROSS JOIN? Or did they really want an INNER JOIN and forget the WHERE clause?

The answer: you do not know for sure; you can only guess. Had the query been

SELECT first_name,last_name,address
FROM customer INNER JOIN address;

you would know that the author intended an INNER JOIN; had the query been

SELECT first_name,last_name,address
FROM customer CROSS JOIN address;

you would know that the author intended a CROSS JOIN. I advocate using INNER JOIN instead of JOIN because like the comma, JOIN is ambiguous.

For full disclosure, the MySQL EXPLAIN plan is exact same for the above queries. The difference is purely semantic, but I think it a very important difference, because it makes the query author’s intentions clear. There are issues with the comma join not having the same precedence as INNER JOIN, but that is for more complex queries. To wit:

SELECT first_name,last_name,address
FROM customer, address INNER JOIN city;

will actually do address INNER JOIN city first, then join with customer. This was changed to be more like the SQL standard, so it’s only in MySQL 5.0.12 and higher that it acts this way.

My feeling is that a comma join should NEVER be used in MySQL. There is no such thing as a “comma join”, really what you mean is an “inner join” or a “left/right join” or “cross join” (or “natural join”).

I know that naysayers will say that

SELECT first_name,last_name,address
FROM customer INNER JOIN address;

actually does a CROSS JOIN (Cartesian product), so it is folly to rely only on the name of the join. And I agree. However, if you name your joins appropriately for what you want and intend them to be, it is much easier to catch mistakes.

And while we are on the topic of good SQL query techniques…..I would like to rant a bit about join criteria. Read the rest of this entry . . .