Diagnosing Oracle Performance Problems on *nix
Oct 5, 2006 / By Christo Kutrovsky
This post belongs in the “tricks” category.
Have you ever had a performance problem on your database server? Have you ever received a call to let you know that the database is slow, and when you login to the server via SSH it takes forever? (It’s funny that I even ask this, of course you have.) You run
top (it also takes forever) and you see a gazillion database sessions using up the CPU.
The next step? I usually login with some kind of GUI query tool such as TOra, Golden, or the new SQL Developer from Oracle, and run a few of my â€œscriptsâ€ to check what is currently running and what it is doing . I have a whole diagnostic suite. Unfortunately, there’s almost no CPU left for my session, so logging in takes forever, running my scripts is sluggish, and during such performance problems seconds just seem like minutes.
The story so far should be quite familiar. One day I came up with the following neat idea. Start a second listener, on a different port, calling it the emergency listener. Then
renice the listener process with higher priority. Now, every time I connect to the database via my emergency listener, my connection gets higher priority, and thus feels like there’s no problem with the database’s resource use.
There is one little caveat however. You need to either have access to
root, or have a nice SA that will add
renice to your
sudoers file .
Here are the commands to setup the â€œemergency listener, after the jump.
$ORACLE_HOME/network/admin/listener.ora and add (changing the hostname to that of your server):
EMERGENCY = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS= (PROTOCOL= IPC)(KEY=PNPKEY_EMERGENCY)) (ADDRESS= (PROTOCOL= TCP)(Host=THE_HOST_NAME)(Port= 1530)) ) ) )
Depending on whether you use dynamic listener registration (which I recommend) or not, you will have to either:
- add a
- or, modify the
local_listenerparameter of each instance running on the server
Since I use dynamic listener registration, I modified by the
local_listener parameter. It can be changed online on-the-fly, so you needn’t worry about needing to bounce the database.
Run this (change
THE_HOST_NAME to whatever your server is):
alter system set local_listener=â€™ (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = THE_HOST_NAME)(PORT = 1521))(ADDRESS = (PROTOCOL = TCP)(HOST = THE_HOST_NAME)(PORT = 1530)))
This way you are telling the database to register with 2 listeners.
- Start the listener:
lsnrctl start EMERGENCY
- Find the PID of the listener process via (usually second column):
ps â€“ef | grep EMERGENCY
- Grab its PID and
rootdirectly) replacing PID with the PID grabbed from the line above:
sudo renice -15 “p PID
- That takes care of the listener.
Now, when you connect via port 1530, the listener will spawn a new Oracle shadow process, which will inherit the
nice value of its parent and thus have higher priority, allowing you to quickly find out what’s wrong.
Some of you may say that changing a process’s priority is dangerous due to latching concurrency, and that the Oracle database been designed to run at the same priority. You shouldn’t worry about this — a process’s priority is constantly changing anyway. Just look at
PR (priority) column is different for most of your processes even with the same
NI (nice or priority adjustment) column.
A process that uses more CPU will be automatically have its priority decreased. A process not using CPU will have it increased. This is to ensure that quick tasks don’t get delayed because of long-running jobs. This happens in Windows. Have you noticed? When some application crashes in an infinite loop consuming the entire (single) CPU, everything slows down. This doesn’t happen with the Linux CPU scheduler, due to its dynamic prioritization.
One Response to “Diagnosing Oracle Performance Problems on *nix”
Leave a Reply