Dispatcher process taking 99% of CPU on Oracle XE after an Apex request

Always check out the original article at http://www.oraclequirks.com for latest comments, fixes and updates.

If you are an enthusiastic user of Apex in combination with Oracle XE as i am, you may have encountered the following issue sometimes: suddenly a process called dispatcher (xe_dnnn) starts consuming 99% of CPU time without any apparent reason.
Unfortunately i failed to find a way to reproduce the problem systematically and, even in that case, Oracle XE is not supported, so there are no chances to get a patch from Oracle.

Although i said there is no apparent reason for this, i have the feeling this problem is triggered by some odd http request made by browsers like Safari and Google Chrome. I am saying this because i am quite sure i was using one of those browsers when this type of event occurred and it never occurred with Firefox.

Today i managed to find a sort of workaround that, at least, doesn't require to bounce the instance to get things back to normal.
I ignore if this problem affects the Windows version as XE is running on top of Ubuntu Linux in my case.

I am assuming that you have already realized that something odd is going on in the database server.
Looking at the running processes in the graphical system monitor of Ubuntu will just report some oracle process taking 99% of the CPU, but still there is no indication of which particular process is the culprit.
You must turn to the console to find out which one is actually consuming most of the CPU:

ps -eo "user,pid,ppid,pcpu,cmd" --sort pcpu | grep xe_

You can also run the following command to get an auto-refreshing snapshot of the situation:

top -u oracle

Now, assuming that the row with the highest CPU usage refers to the oracle process called xe_d000 and provided we have to live with this bug, what can we do to keep the ball rolling?

First of all, we (or the DBA) can kill the dispatcher process:

ALTER SYSTEM SHUTDOWN IMMEDIATE 'D000'; -- the number depends on the output of the ps command

Notice: if this command doesn't work, something that could happen if the process is unresponsive, on a linux/unix box you can the related o/s process.

KILL -9 pid

where pid is the oracle process id for D000.
I fear that on Windows you cannot kill a single thread as easily.

While there are no other active dispatcher processes available, all web clients will receive http 404 errors when attempting to load an Apex page.
Then we can issue the command to recreate at least one dispatcher process.

ALTER SYSTEM
SET DISPATCHERS =
'(PROTOCOL=TCP)(DISPATCHERS=1)(INDEX=0)';

The default number of dispatchers on Oracle XE is 1, but it can be increased if required, in order to support a higher number of concurrent connections.
You can find some information about shared server processes and dispatchers in the Administrator's Guide for Oracle 10R2, while waiting for Oracle to release the new express edition based on Oracle 11GR2, (hopefully soon?)

Updated March 9, 2010:
thanks to Jens who reported a missing parenthesis and provided a "roadmap" for restoring the service. See the comments section.