Diagnosing Oracle Performance Problems on *nix

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.

Edit $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 SID_LIST to your listener.ora file
• or, modify the local_listener parameter 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 renice it via sudo (or as root directly) 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 top, the 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.