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 . . .

So you want to store URLs in MySQL, and the URLs have those annoying %20%27%7C%26%5E%2B%2D%25 symbols? And you want to be able to show your users some kind of human-readable information. You might want to consider using this trick. Take this list of commonly escaped characters as an example:

%20 - space
%27 - '
%7C - |
%26 - &
%5E - ^
%2B - +
%2D - -
%25 - %

So, how about we do some search’n'replace on that?

mysql> SET @url:='%20%27%7C%26%5E%2B%2D%25';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @url as original,
    ->        REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
    ->        REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
    ->        @test,'%20',' '),
    ->        '%27','\''),
    ->        '%7C','|'),  -- REPLACE() is case sensitive
    ->        '%7c','|'),  -- so we have
    ->        '%26','&'),
    ->        '%5E','^'),
    ->        '%5e','^'),  -- to repeat
    ->        '%2D','-'),
    ->        '%2d','-'),  -- ourselves
    ->        '%2B','+'),
    ->        '%2b','+'),  -- sometimes
    ->        '%25','%') as replaced;
+--------------------------+----------+
| original                 | replaced |
+--------------------------+----------+
| %20%27%7C%26%5E%2B%2D%25 |  '|&^+-% |
+--------------------------+----------+
1 row in set (0.01 sec)

mysql>

We can easily turn this into a function: Read the rest of this entry . . .

I found myself, as a fresh member of The Pythian Group, losing precious moments just to change a few standard administrative settings on my new laptop with Microsoft Windows Vista. Having found the answers, I’m sharing them with you so that you can save some time, or spend it more pleasurably out in the summer.

User Account Control (UAC)

Too many prompts and confirmations for admin tasks? Set “Elevate without prompting” for “User Account Control: Behavior of the elevation prompt for administrators in Admin Approval Mode” under Start -> Settings -> Control Panel -> Administrative Tools -> Local Security Policy -> Security Settings -> Local Policies -> Security Options. Ref: UAC on Wikipedia.

Windows Vista Features

By default, telnet is not installed with Windows Vista. To (re-)enable it, select the Telnet Client check box in Start -> Settings -> Control Panel -> Programs and Features -> “Turn Windows features on or off”. Then, Windows Features dialog -> Telnet Client. Now you may use telnet from the command prompt as you are used to in XP. Ref: FAQ on windowshelp.microsoft.com

Read the rest of this entry . . .