If you have been using Unix or Linux for a while, you are likely familiar with the command “top” which dynamically shows the system overall activities in real-time. When looking at the database monitoring, you might have your favorite tools — EM, ADDM, ASH, AWR etc. or even your home grown tools.

Starting from Oracle 11gR2, Oracle has introduced a Unix “top” like tool “oratop“. And from 12cR2, it’s a part of Trace File Analyzer Collector package; it’s also installed with the database by default.

oratop is a text-based utility provides a similar interface to “top”. It runs on a Unix/Linux platform, but can monitor a database on any platform remotely using an alias defined in tnsnames.ora.

From 12cR2, it’s installed under “$ORACLE_HOME/suptools/oratop”. If you are checking a local database, you can just run it like “oratop / as sysdba” using the Bequeath authentication. In my system I have created a symbolic link so that I don’t have to give the path all the time.

[oracle@joedb ~]$ ln -s /opt/oracle/190000/suptools/oratop/oratop /opt/oracle/190000/bin/oratop

You can get a brief help with “-h” option.

[oracle@joedb logs]$ $ORACLE_HOME/suptools/oratop/oratop -h
oratop: Release 15.0.0
Usage:
         oratop [  [Options] [Logon] ]

         Logon:
         o prompt/cmd-line:     {username[@connect_identifier] | / } [AS SYSDBA]
         o prompt:              {password[@connect_identifier] } [AS SYSDBA]

                connect_identifier:
                     o Net Service Name, (TNS) or
                     o Easy Connect (host[:port]/[service_name])

         Options:
             -r : real-time (RT) wait events. (sec 3, default: Cumulative)
             -k : FILE#:BLOCK# (sec 4/process mode, defaut: EVENT/LATCH)
             -m : MODULE/ACTION. (sec 4/process mode, default: USERNAME/PROGRAM)
             -s : SQL mode. (sec 4, default: process mode)
             -f : detailed format, 132 columns. (default: standard, 80 columns)
             -b : batch mode. (default is text-based user interface)
             -n : maximum number of iterations. (requires number)
             -i : interval delay, requires value in seconds. (default: 5s)
             -v : oratop release version number
             -h : this help

You can also use easy connect string to connect to a remote database like this:

[oracle@joedb]$ oratop system@192.168.69.11:1521/PROD

Once in the interactive mode, you will see the following screen which has 4 sections.

• Database — first line
• Instance — 2nd & 3rd line
• DB Wait Events
• Process mode/SQL mode

The tool queries Oracle internal views every 5 seconds by default and displays a snapshot of current database/instance activities. So the user needs the privilege “SELECT ANY DICTIONARY” if not connecting “as sysdba“. It runs with one of two modes: batch mode & interactive mode. In the interactive mode, presss the key “h” will enter the help mode which will show addtional detailed help. When in SQL mode (using “-s” when launching the tool or the key “s” when in interactive mode), the section 4 displays more information about top SQL statements. Chosing “4” in the help will also give you what columns stand for what.

oratop: Release 15.0.0

Interactive Keys: [default]
        f : toggle between [standard] & detailed format.
        r : toggle between [Cumulative (C)] & Real-Time (RT) (section 3)
        s : switch to SQL mode (section 4)
        p : switch to [process] mode (section 4)
        k : toggle between [EVENT/LATCH] & object FILE#:BLOCK# (process section 4)
        m : Toggle between [USERNAME/PROGRAM] & MODULE/ACTION (process section 4)
        t : tablespace information
        x : basic SQL plan table (requires sql_id input)
        i : refresh interval, requires value in seconds [5s]
        q : quit/ exit program

Symbols:
        (B)yte, (k)illo, (M)ega, (G)iga, (T)erra, [PEZY]
        (u)micro, (m)illi, (s)econd, minu(t)e, (h)our, (d)ay, (y)ear

Abbreviations Help Menu:
        Section 1 - DATABASE        .. [1]
        Section 2 - INSTANCE        .. [2]
        Section 3 - DB WAIT EVENTS  .. [3]
        Section 4 - PROCESS         .. [4]
        Quit Help                   .. (q|Q)

Enter selection Number:

When in the Process mode, you can use the key “k” to toggle between [EVENT/LATCH] & object FILE#:BLOCK# (process section 4). The key “m” to toggle between [USERNAME/PROGRAM] & MODULE/ACTION (process section 4).

The key “t” will show tablespace level information.

The key “f” to toggle between standard (80 columns) & detailed format (132 columns).

The key “r” to toggle between [Cumulative (C)] & Real-Time (RT) for the section 3.

To change the refresh interval, use the key “i“.

If ASM is used, the key “a” will show ASM disk group information.

Another handy thing coming from the tool is to show the execution plan of a query using the key “x” with the basic plan information.

You may see some information in red color which means a warning or reminder. To exit the program, type “q” or Ctrl+c.

This tool also has a batch mode (“-b” option) which you can wrap the tool in another shell script so that you can review the results later if you don’t want to watch the system all the time.

Note:

1. The server parameter statistics_level needs to be set to TYPICAL which in turn set timed_statistics to TRUE.
2. When the database is very busy (overloaded), the tool might not be able get the information from the database, you will see a blank screen. Or it just responds slowly like it’s hanging.
3. If TFA is installed, it can be invoked from the TFA CLI for local database(instance) monitoring.
4. If you run it from the database server itself, usually you don’t have to set up anything else than ORACLE_SID. But if you run it from another Unix/Linux system with Oracle client installed, you want to make sure your environment variables ORACLE_HOME, LD_LIBRARY_PATH are set up properly.
5. If it connects to a RAC, you will see multiple lines (up to 5 nodes) in the instance section when using the interactive mode. But in the batch mode, it will show all availabe nodes.

Reference:

oratop – Utility for Near Real-time Monitoring of Databases, RAC and Single Instance (Doc ID 1500864.1)

Update on 2021.09:

I’ve run into an interesting issue — on secured Oracle Linux 8 systems, oratop doesn’t responds to any key stroke but only CTRL-C to exit. Using oratop from a remote server which is on Oracle Linux 7 works.

I’ve tried to disable fapolicyd and it didn’t help.

I’ve tried to use the root user to launch oratop, still not responding. To be able to use oratop from the root user, I had to set:

1. export LD_LIBRARY_PATH=/usr/lib::/opt/oracle/190000/lib
2. export ORACLE_SID=PROD
3. export ORACLE_HOME=/opt/oracle/190000
4. chmod 6755 /opt/oracle/190000/bin/oracle

For the item 4, the executable oracle has the permission by default:

root@joedb:~# ls -lart $ORACLE_HOME/bin/oracle
-rwsr-s---. 1 oracle oinstall 448515032 Sep 17 11:51 /opt/oracle/190000/bin/oracle

I had to add ‘r-x’ for other user:

root@joedb:~# ls -lart $ORACLE_HOME/bin/oracle
-rwsr-s---. 1 oracle oinstall 448515032 Sep 17 11:51 /opt/oracle/190000/bin/oracle
root@joedb:~# chmod 6755 /opt/oracle/190000/bin/oracle
root@joedb:~# ls -lart $ORACLE_HOME/bin/oracle
-rwsr-sr-x. 1 oracle oinstall 448515032 Sep 17 11:51 /opt/oracle/190000/bin/oracle
root@joedb:~# chmod 6750 /opt/oracle/190000/bin/oracle
root@joedb:~# ls -lart $ORACLE_HOME/bin/oracle
-rwsr-s---. 1 oracle oinstall 448515032 Sep 17 11:51 /opt/oracle/190000/bin/oracle

Otherwise, I will get the error “ORA-12546: TNS:permission denied” after I enter the database user name and password and oratop tries to connect.

Tried to use strace to track system calls, didn’t find anything significant.

Tried to disable SELinux and it didn’t help either.

I’ve tried to copy oratop from Linux 7 to 8 because they have different hash code (they are from different 19c patchset), although their versions are the same 15.0.0. It didn’t help.

On the contrary, oratop from Linux 8 works on Linux 7 after I copied it over.

It seems something specific to my Linux 8 servers which blocks keystroke. However, top works on those servers which is interesting. I’ve tried to create a symbolic link for oratop under /usr/bin with no luck.

• Oracle