Oratop工具——实时数据库性能监控工具
 

在任何系统优化过程中，“80/20原则”是我们一定要关注的问题。简单的说，就是我们系统80%的性能问题、现象，都是有少数几个甚至一个问题造成的。这就需要我们面对复杂的系统性能问题的时候，要学会“拨开云雾、蛇打七寸”。

落实在Oracle优化过程，同样如此。从10g开始，我们开始频繁接受“Top-xxx”的概念，无论是从OEM（Oracle Enterprise Manager）还是借助AWR事后报告库，解决主要矛盾，发掘主要Top Events就成了我们日常工作不可缺少的部分。

实际工作中，对Top的发掘和Oracle动态信息的收集，很多时候还是有点力不从心和滞后之感强烈。Oracle官方的客户服务网站MOS，经常有一些有用的小工具，提供给我们使用。本篇中，我们主要来介绍下Oratop工具的特点和使用。

1、Oratop工具简述

Oratop源自MOS的一篇文章和工具链接（oratop - utility for near real-time monitoring of databases, RAC and Single Instance [ID 1500864.1]）。从名称上看，Oratop最大特点是支持实时轻量级监控。在Oracle 12c的EM Express之前，OEM还是一个重体积的组件。相比之下，oratop完全适合那些想使用数据库实时监控功能，但是又不愿意启动OEM的用户需要。

从功能上看，Oratop主要有三大特点：

ü  监控当前的数据库活动；

ü  监控数据库性能；

ü  识别当前阻塞会话和瓶颈会话；

目前，Oratop是在MOS上免费下载，分别有32位和64位两个版本。

2、软件安装初启动

从网站上下载软件之后，是一个命名为oratop.v13.2.1_X8664的可执行程序，注意要区分32位版和64位版。

首先，判断适合的Linux版本，并且创建程序目录。Oratop是一个典型的“绿色”软件，不需要安装，只需要给一个运行目录即可。

--64位操作系统

[root@localhost ~]# uname -a

Linux localhost.localdomain 2.6.32-279.el6.x86_64 #1 SMP Wed Jun 13 18:24:36 EDT 2012 x86_64 x86_64 x86_64 GNU/Linux

[root@localhost ~]# mkdir /oratop

[root@localhost ~]# cd /oratop

[root@localhost /]# cd oratop/

[root@localhost oratop]# ls -l

total 0

[root@localhost oratop]#

进行目录的权限转换和程序拷贝。为了方便执行，我们最好还要进行一下重命名工作。

[root@localhost /]# chown -R oracle:oinstall /oratop

[root@localhost /]# su - oracle

[oracle@localhost ~]$ cd /oratop/

[oracle@localhost oratop]$ ls -l

total 92

-rw-r--r--. 1 oracle oinstall 93640 May 17 13:39 oratop.v13.2.1_X8664

[root@localhost oratop]# ls -l

total 92

-rw-r--r--. 1 root root 93640 May 17 13:39 oratop.v13.2.1_X8664

[oracle@localhost oratop]$ mv oratop.v13.2.1_X8664 oratop

[oracle@localhost oratop]$ ls -l

total 92

-rw-r--r--. 1 oracle oinstall 93640 May 17 13:39 oratop

注意：使用oratop在环境变量上需要一些额外的配置内容。主要是对于变量LD_LIBRARY_PATH的配置。我们一般都不会去配置这目录，但是oratop要求必须进行配置。

配置方法是修改根目录上的.bash_profile文件。

[oracle@localhost ~]$ vi .bash_profile

export ORACLE_BASE=/u01/app/oracle

export ORACLE_HOME=$ORACLE_BASE/product/11.2.0/dbhome_1

export ORACLE_SID=isis2db

export PATH=$PATH:$ORACLE_HOME/bin

（篇幅原因，掠过部分内容……）

export LD_LIBRARY_PATH=$ORACLE_HOME/lib

".bash_profile" 36L, 901C written                            

[oracle@localhost ~]$

[oracle@localhost ~]$ env | grep LIB

LD_LIBRARY_PATH=/u01/app/oracle/product/11.2.0/dbhome_1/lib

下面我们来试着执行以下命令。

[oracle@localhost oratop]$ ./oratop -i 10 / as sysdba

oratop: Release 13.2.1 Production on Fri May 17 13:43:34 2013

Copyright (c) 2011, Oracle.  All rights reserved.

Processing ...（等待片刻……）

参数-i表示数据刷新间隔，也就是多长实际更新一下页面数据信息。/ as sysdba表示登录本机连接。注意，这也就意味着我们可以远程使用oratop连接到其他服务器上面去。更进一步说，通过远程连接的方法，可以连接到任何版本操作系统上的Oracle服务器的。

出现字符界面显示。

oratop 1:  131 isis 13:44:24 up  0.0h,  1 ins,   3G mt,  41 sn,  3 us,   0% db

ID %CU HLD MBPS IORL %FR PGAU ASC ASI ASW ASP   AAS USN  TPS UCPS ***T DBC DBW

 1  12   0   71   0m  18 161M   1   0   0   0   0.1  41    0   24   3m  94   6

EVENT     (Cumulative)      AVG: TOT WAITS  TIME(s)  AVG_MS PCT  WAIT_CLASS  

DB CPU                                       112868          75              

SQL*Net message from dblink       16794060    14852     0.9  10  Network     

SQL*Net more data from clien        193150     8260    42.8   5  Network     

log file parallel write            1025524     7308     7.1   5  System I/O  

enq: TX - row lock contentio          1289     7248  5622.9   5  Application 

ID   SID  SPID USR PROG  PGA OPN SQLID/BLOCKER  E/T STATUS STE WAIT_EVENT  W/T

3、初识Oratop

从字符界面上看，oratop结果集合分为四个部分，分别为：Header Section、Database Section、DB Events Section和Processes Section。

Header Section中，包括了连接数据库的总体信息，包括运行多长时间、实例个数（for RAC）、内存大小和数据库占据CPU时间的比例。

Database Section中，包括了各个实例（for RAC）下，每个实例的基本参数和复杂信息。如每个实例活动会话个数、每秒的事务数量和内存使用情况等。

DB Events Section是从Wait Event角度进行评估。评估排在头几位的等待事件信息，默认情况下，这个度量是累计的，也就是从启动数据库到当前时间。我们可以切换到当前时间模式下。

Processes Section是记录下处在Block和Contention状态的连接会话信息。当数据库出现有会话被阻塞的情况，就会记录在这个Section里面。

4、丰富的帮助系统

Oratop结果显示中，大量使用的简写和缩写，这在一定程度上阻碍我们阅读结果。为此，Oratop开发人员设计了完备功能的帮助体系。

在使用-h参数，或者运行过程中按下键盘h，就可以切换到帮助界面。

使用h按键

oratop: Release 13.2.1

------------------------------------------------------------------------------

Dynamic Keys

        d : Toggle Top 5 Wait Events between Real-Time and Cumulative

        f : Toggle between short & long format (for header & Process sections)

        i : Interval Delay time (requires value in seconds)

Nomenclature:

        [M] : Signify 1 minute averaged value, else, Real Time

        [F] : denote a Forground (FG) value, else, Background (BG)

------------------------------------------------------------------------------

Acronym Help Menu:

HEADER Section    .. [1]

DATABASE Section  .. [2]

DB EVENTS Section .. [3]

PROCESSES Section .. [4]

Quit Help         .. (Q|q)

Enter selection Number:

动态按键（Dynamic Keys）实际上就是在运行过程中，可以动态实时点击，查看不同维度结果的案件。

笔者比较喜欢d按键，这样可以从累计Event信息到实时Event信息，把握最新的数据库等待时间状态。

(d) –使用了d动态按键

oratop 1:  376 NBST 14:31:22 up  5.1d,  1 ins,   2G mt, 135 sn,  7 us,   4% db

ID %CU HLD MBPS IORL %FR PGAU ASC ASI ASW ASP   AAS USN  TPS UCPS ***T DBC DBW

 1   2   0    0   0m  17 394M   1   0   0   0   0.0 135    4   71 297u  85  15

EVENT     (Real-Time)       AVG: TOT WAITS  TIME(s)  AVG_MS PCT  WAIT_CLASS  

DB CPU                                         1867         100              

ID   SID  SPID USR PROG  PGA OPN SQLID/BLOCKER  E/T STATUS STE WAIT_EVENT  W/T

另一部分帮助就是显示对四个section的解释，解释的非常详细，值得读者学习。篇幅原因，这里只显示一份结果。

Section 1- HEADER (Global information)

   "oratop"  : program name

         : instance_id to which oratop is connected to

        : oratop's SID

     : DB_UNIQUE_NAME

    [M]: Time of most recent stats (hh24:mi:ss)

         : Database Uptime

         : Total number of instance(s)

         : Total DB memory cluster wide (SGA+PGA)

      [F]: User sessions (idle & active)

      [F]: Number of distinct users (idle & active)

   <%db>     : %database busy, 0% indicate idle db

5、复杂应用

Oratop的本质和优势在于轻量型和实时。一个很小的程序，可以在自身消耗较小的情况下发现问题。

Block and Contentio是Process Section部分重点问题。阻塞是数据库内部资源征用的一种结果，及时发现Blocking和Contention是非常重要的。

下面片段是笔者模拟的一个两个会话同时修改一行数据，之后一个会话被阻塞时，oratop监控到的情况。

锁定等待

ID   SID  SPID USR PROG  PGA OPN SQLID/BLOCKER  E/T STATUS STE WAIT_EVENT  W/T

 1   187 25971 SYS DEDI 908k DEL 1:193:25968    20s ACTIVE WAI enq: TX -   20s

在信息中，等待Session信息、SQL信息和等待事件信息都是我们进行进一步调优的依据。

另外，我们可以在一台Linux上安装oratop，监控连接另一台远程数据库服务器，比如AIX系统。

首先，我们需要建立本地连接名。

[oracle@localhost oratop]$ netca

Oracle Net Services Configuration:

Default local naming configuration complete.

    Created net service name: nbstest

Oracle Net Services configuration successful. The exit code is 0

[oracle@localhost oratop]$ tnsping nbstest

TNS Ping Utility for Linux: Version 11.2.0.3.0 - Production on 17-MAY-2013 14:06:38

Copyright (c) 1997, 2011, Oracle.  All rights reserved.

Used parameter files:

Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.1.15.66)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = nbstest)))

OK (0 msec)

监控时候，就是用对应的用户名密码和连接名进行连接。

[oracle@localhost oratop]$ ./oratop -i 10 sys/sys@nbstest as sysdba

oratop: Release 13.2.1 Production on Fri May 17 14:07:39 2013

Copyright (c) 2011, Oracle.  All rights reserved.

6、形色参数

最后我们介绍一下oratop可以使用的参数。在示例中，我们见到了-i参数。

-i参数表示监控间隔。后面一般跟着一个数字，表示每隔几秒钟进行一次数据收集。这个参数要进行选择的，如果太大，很多关键数据不能收集查看。如果设置太少，oratop自身也会造成性能瓶颈。

-b批量运行模式。实时数据如果需要输入到一个结果集合文件，供下一步处理使用，可以使用b模式参数。当设置b参数之后，oratop会持续运行，或者在事先设计好的次数后结束。

-n设置最大迭代操作次数。

-d一改默认是累计事件的统计情况，采用实时统计。

[oracle@localhost oratop]$ ./oratop -i 5 -b -d / as sysdba

oratop: Release 13.2.1 Production on Fri May 17 14:59:32 2013

Copyright (c) 2011, Oracle.  All rights reserved.

Processing ...

oratop: Release 13.2.1 Production on Fri May 17 14:59:32 2013

ID   SID  SPID USR PROG  PGA OPN SQLID/BLOCKER  E/T STATUS STE WAIT_EVENT  W/T

oratop: Release 13.2.1 Production on Fri May 17 15:00:17 2013

oratop 1:  202 isis 15:00:09 up  0.0h,  1 ins,   3G mt,  67 sn,  3 us,   3% db

ID %CU HLD MBPS IORL %FR PGAU ASC ASI ASW ASP   AAS USN  TPS UCPS ***T DBC DBW

 1   0   0    0   0m  17 183M   1   0   0   0   0.0  67    0    6 286u  60  40

EVENT     (Real-Time)       AVG: TOT WAITS  TIME(s)  AVG_MS PCT  WAIT_CLASS  

DB CPU                                         1944         100              

（有省略…..）

oratop: Release 13.2.1 Production on Fri May 17 15:00:27 2013

7、结论

Oratop是一个非常出色的轻量型监控工具，条件运行的话可以尝试一下。

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

类型:  状态:  上次主更新:  上次更新:  语言:

SCRIPT  PUBLISHED  2017-4-19  2017-4-19  English日本語???

In this Document

APPLIES TO:

Oracle Database - Enterprise Edition - Version 11.2.0.3 to 11.2.0.4 [Release 11.2]  
Oracle Database - Enterprise Edition - Version 12.1.0.1 to 12.1.0.2 [Release 12.1]  
IBM AIX on POWER Systems (64-bit)  
HP-UX PA-RISC (64-bit)  
HP-UX Itanium  
Linux x86-64  
Linux x86  
Oracle Solaris on x86-64 (64-bit)  
Oracle Solaris on SPARC (64-bit)  

PURPOSE

oratop is a utility similar to the unix OS utility top which gives a overview of database performance and can be used in combination with top to get a more complete overview of system performance 

REQUIREMENTS

Supported Platforms

Linux x86

Linux x86-64

Solaris SPARC-64

Solaris x86-64

AIX (PPC)

HP-UX PARISC*

HP-UX IA

* 11gR2 only

NOTE: oratop on any platform can be used to monitor databases on other platforms.

To monitor databases on other platforms simply define an alias in tnsnames.ora of the Oracle client and connect to the databases remotely as you would with sqlplus

Supported Oracle Releases

11gR2 (11.2.0.3, 11.2.0.4)

12cR1 (12.1.0.1, 12.1.0.2)

CONFIGURING

oratop is now distributed and integrated with Trace File Analyzer Collector (TFA) along with all the other tools that are a part of the Support Tools Bundle.

So rather than downloading oratop separately the preferred method for obtaining oratop is to download the latest version of TFA.  This integration means 

that anytime TFA is upgraded (PSUs, download from MOS, etc) the latest version of oratop and the other Support Tools of the bundle will also be obtained.

If your Grid Infrastructure version is 11.2.0.4 or 12.1.0.2 then TFA will automatcially be installed.  In either case we recommend that the TFA installation be

upgraded to the latest version available from TFA Collector - Tool for Enhanced Diagnostic Gathering (Doc ID 1513912.1) in order to obtain the Support Tools 

Bundle which was first bundled with TFA version 12.1.2.3.0, as well as various other new features, bug fixes and optimizations.

Download the oratop executable for:

11gR2 on Linux x86-64 here.

11gR2 on SUSE10 Linux x86-64 here.

12c on Linux x86-64 for use with 12c clients here.

11gR2 on Linux x86-32 here.

12c on Linux x86-32 for use with 12c clients here.

11gR2 for Solaris SPARC here.

12c for Solaris SPARC here.

11gR2 for Solaris x86 here.

12c for Solaris x86 here.

11gR2 for AIX here.

12c for AIX here.

11gR2 for HP-UX PARISC here.

11gR2 for HP-UX IA here.

12c for HP-UX IA here.

Please download and review the oratop Users Guide for specific instructions on how to configure and run oratop.  A Quick Start Guide is provided in the Instructions section of this Note.

INSTRUCTIONS

Quick Start Guide

Note:   For detailed instructions on how to run oratop please download and review the latest oratop Users Guide.

Running oratop from within TFA Collector  

Use the tfactl CLI to set the database context for oratop, ie., the target database that oratop should connect to, eg.,

$ $TFA_HOME/bin/tfactl db orcl       <== set the target database context in TFA for oratop to use

$ $TFA_HOME/bin/tfactl oratop        <== oratop will connect to the orcl database, no need to worry about any of the oratop pre-reqs, TFA takes care of all that

OR

Use the tfactl shell to set the database context for oratop, ie., the target database that oratop should connect to, eg.,

$ $TFA_HOME/bin/tfactl              <== invoke the tfactl shell prompt

tfactl> db orcl                     <== set the target database context in TFA for oratop to use 
Set db to orcl
orcl tfactl> oratop

To run oratop in "batch mode" for a spoecific number os snapshots

$ $TFA_HOME/bin/tfactl

tfactl> db orcl
Set db to orcl
orcl tfactl> oratop -bn10         <== this example would run 10 snapshots at the default 5 second interval unless the interval had been changed eg. oratop -i 10 -bn10

Once oratop is invoked and running within the TFA framework its operation is exactly the same as if run standalone with the exception that the database must be running on the node from which the "tfactl oratop" command is executed.  If the database is running remotely on one or more nodes of a RAC cluster, log in one of those nodes and run the "tfactl oratop" command on there after setting the database context.  In other words when oratop is run from within TFA there is no remote database access, a bequeath connection is used instead.  

Another thing to be aware of is that when oratop is run from within the TFA framework in batch mode as in the above example the ouput is automatically redirected to the oratop specific directory in the TFA repository location $TFA_REPOSITORY/supptools/oratop for automatic collection by TFA Collector.  In standalone mode you would need to use the redirection symbol ">" and specify the path and file name for re-direction and TFA would not necessarily know where to collect the resultant file unless it is redirected to one of the know diagnostic directories.

Running oratop standalone

1. Log in to the system as the Oracle RDBMS software installation owner
2. Stage the oratop executable on the server on which the tool will be executed.  On a RAC system it is only necessary to stage the executable on one node as it is RAC aware.
3. Change the name of the executable, eg.

4. $ mv oratop* oratop    

    

5. Validate the permissions for oratop are 755 (-rwxr-xr-x). If the permissions are not currently set to 755, set the permissions on oratop as follows: 
   

   $ chmod 755 oratop    

6. Configure the execution environment as follows (if not already set accordingly)

7. $ export TERM=xterm  #or vt100      

   $ export ORACLE_HOME=<11.2 database home>      

   $ export LD_LIBRARY_PATH=$ORACLE_HOME/lib      

   $ export PATH=$ORACLE_HOME/bin:$PATH      

   $ export ORACLE_SID=  #only needed if connecting to a local database      

8. Invoke the tool to monitor a local database as follows (interval can be any interval in seconds):
   

   $ ./oratop -i 10 / as sysdba    

9. Non-dba privileged user requires specific grants to use the tool.  See User Guide for grants required.
10. Databases can be monitored remotely using a tns alias as described in the oratop Users Guide.

11. $ ./oratop -i 10 username/password@tns_alias    

    $ ./oratop -i 10 system/manager@tns_alias    

     

12. To exit the program simply press the keyboard key "q".  To abort, CTRL-C.

Troubleshooting:  

If you receive an error similar to   

./oratop: error while loading shared libraries: libclntsh.so.11.1: cannot open shared object file: No such file or directory  

that is an indication that libclntsh.so.11.1 was not found in the path defined in LD_LIBRARY_PATH similar to   

$ export LD_LIBRARY_PATH=$ORACLE_HOME/lib  (ie., the location of libclntsh.so.11.1)  

CAUTION

This sample code is provided for educational purposes only, and is not supported by Oracle Support. It has been tested internally, however, we do not guarantee that it will work for you. Ensure that you run it in your test environment before using.  

SCRIPT

The tool is a compiled c program.

SAMPLE OUTPUT

Sample workload

REFERENCES

NOTE:1594347.1    - RAC and DB Support Tools Bundle  

• oratop 14.1.2 - AIX-PPC64 for 11.2.0.3 and 11.2.0.4(178.85 KB)
• oratop 14.1.2 - HPUX-IA64 for 11.2.0.3 and 11.2.0.4(337.45 KB)
• oratop 14.1.2 - HPUX-PARISC64 for 11.2.0.3 and 11.2.0.4(254.54 KB)
• oratop 14.1.2 - Linux32 for 11.2.0.3 and 11.2.0.4(119.33 KB)
• oratop 14.1.2 - Linux64 for 11.2.0.3 and 11.2.0.4(131.57 KB)
• oratop 14.1.2 - Solaris x64 for 11.2.0.3 and 11.2.0.4(208.7 KB)
• oratop 14.1.2 - Solaris SPARC64 for 11.2.0.3 and 11.2.0.4(202.34 KB)
• oratop 14.1.2 - AIX-PPC64 for 12c(179.02 KB)
• oratop 14.1.2 - HPUX-IA64 for 12c(337.45 KB)
• oratop 14.1.2 - Linux32 for 12c(119.36 KB)
• oratop 14.1.2 - Linux64 for 12c(131.64 KB)
• oratop 14.1.2 - Solaris x64 for 12c(209.28 KB)
• oratop 14.1.2 - Solaris SPARC64 for 12c(202.63 KB)
• oratop User Guide(669.36 KB)

oratop User Guide

Abstract

oratop can be used to monitor Oracle databases (release 11.2 onwards) on any platform but the executable must run on Linux with an Oracle compatible client.  To monitor databases on other platforms simply define an alias in tnsnames.ora of the Linux client and connect to the databases remotely as you would with sqlplus.

INTRODUCTION

The Oracle program named oratop allows users with select privilege to a set of Oracle’s views to monitor 11.2 or higher version of oracle database activities.  It runs dynamically in near real time and provides a live window to a running database.  It is Single instance/ RAC/ ASM aware utility. (see sample snapshot shown in Figure 1 below) Key motivations include:

?         Monitoring current database activities,

?         Database performance,

?         Identifying contentions and bottleneck

Figure 1. oratop text-based user interface (default mode) 

ENHANCEMENTS

? SQL Monitoring

? Database Service

? Multitenant Database  (12c oracle  CDB/PDB)

? Active Data Guard

SECTIONS  

The “oratop” displays relevant database activity information presented in four sections.

? Section 1 - HEADER: Global database information

? Section 2 - DATABASS: Database instance Activity

? Section 3 - DB EVENTS: AWR like “Top 5 Timed Events“

? Section 4 – PROCESS/SQL: Processes/SQL information

Specific detail of the various sections data can be obtained using the interactive help (see

Runtime usage Sections Detail below)

CLIENT

Connection method is largely similar to sqlplus.  If it works for sqlplus, then it should work the same for oratop.

Shell environment settings

$ export ORACLE_HOME=

$ export LD_LIBRARY_PATH=$ORACLE_HOME/lib

$ export PATH=$ORACLE_HOME/bin:$PATH

Bequeath[1]

$ export ORACLE_SID=

Remote server[2]

Options:

?        TNS:  

$ export TNS_ADMIN

-   connect with a TNS alias name as defined intnsnames.ora, or

-   set TWO_TASKto the tns alias and connect without alias name

?        EZConnect

I.  COMMAND LINE MODE

Command line synopsis can be obtained by invoking the utility with “-h” option as shown in Figure 2.

Figure 2. Command line help

Logon

Invocation:

$ oratop [Options] [Logon] Example,

Using Easy Connect (EZConnect) ?    Non-default port oratop system/manager@dbhost:4800/db1.domain.com ?    Default port (1521):

oratop system/manager@dbhost/db1.domain.com

Options

d :  Real-Time Top 5 Wait Events, section 3.

Specifies Wait Events display mode.

Default:  Cumulative

To override the default value

? Command line:  -d ? Runtime:  d

k : “FILE#:BLOCK#” for objects being waited on, process section 4.

Specifies “FILE#:BLOCK#” display mode.

Default:  EVENT/LATCH

To override the default value

? Command line:  -k ? Runtime:  k

m : “MODULE/ACTION”, process section 4. Specifies “MODULE/ACTION” display mode.

Default:  USERNAME/PROGRAM

To override the default value

? Command line:  -m ? Runtime:  m

s :  SQL mode of section 4. Specifies SQL display.

Default: Process mode

To override the default value

? Command line:  -s ? Runtime:  s

c :  Database Service centric mode.

Displays information based on the database service specified in the connect string.

Default: Connection mode

To override the default value ? Command line:  -c ? Runtime:  N/A

f :   Long format, (132 columns) 

Specifies long format for header & process section.

Default: short (80 columns)

To override the default value

? Command line:  -f ? Runtime:  f

p :  Process mode of section 4. process display. Default: Process mode

To override the default value

? Command line:  -p

? Runtime:  p

b :  Batch mode operation.

Runs in sequential frames.  It is useful for sending output to a file. In this mode, oratop will run continuously interrupted only by the user (CTRL-C) or until the iterations limit is reached if it is set with the '-n' command-line option. Default: Text-based user interface

To override the default value ? Command line:  -b ? Runtime:  N/A

n :  Maximum Number of iterations.   (requires a number)

Specifies the maximum number of iterations, or frames, oratop should produce before ending.

Default: infinite

To override the default value ? Command line:  -n ? Runtime:  N/A

t:  Displays tablespace information.

Default: N/A

? Command line:  N/A ? Runtime:  t

a:  Displays  ASM diskgroup information.  (no op for non-ASM)

Default: N/A

? Command line:  N/A ? Runtime:  a

x:  Displays SQL execution plan table.  Requires input of the active SQL sql_id value.

Default: N/A

? Command line:  N/A ? Runtime:  x

i :   Interval delay time. ( requires value in seconds)

Specifies the delay between update refresh.  A short interval delay (<10s) is not guaranteed  for the specified delay.  This is particularly true for a loaded system. 

Default: 5 seconds.

To override the default value

? Command line:  -i < number in seconds> ? Runtime:  i

v :  oratop version number.

Displays version number

Default: N/A

? Command line:  -v ? Runtime:  N/A

h :  Help.

Displays usage or output information.

Default: N/A

? Command line:  -h[elp]

? Runtime:  h

II.  INTERACTIVE MODE

Runtime interactive and detail options can be seen after starting the utility followed by pressing the keyboard key ‘h’.  (See Figure 3)

Figure 3. Runtime Options using help

1.  Interactive Toggle keys

      1.1.       [ d ]  - Section 3 “DB EVENTS” Cumulative( C)/ Real-Time (R/T) modes

See header starting with “EVENT (c)” in Figure 4 below.

Figure 4. Runtime toggle Option ‘d’ (Cumulative/ Real-Time)           1.2.       [ k ]  - Section 4 Process “EVENT/*LATCH “ and “FILE#:BLK#” columns  
 Figure 5 below shows column toggling in section 4 when keyboard key “k” is pressed.  

Figure 5. Runtime switch Option ‘k’ (process,“EVENT/[3]LATCH “ & “FILE#:BLK#”)  

Usefulness

The “FILE#:BLOCK#” values are significant when there is a long wait on an object.  It is possible to identify the reason for a bottleneck (e.g. concurrency, user_io, etc.) by correlating with the wait class and the wait event in the detailed (long) format.

         1.3.        [ m   - Section 4 Process Module and Action columns  

Figure 6 below shows section 4 column changes.

Detailed format

?         “USERNAME”, ”PROGRAM “ and“MODULE”, ”ACTION”column toggle

.

Default standard format

?         “USR”, ”PROG” and “MOD”, ”ACTN”. (default short  format)

Figure 6. Runtime switch Option ‘m’ (process,“USR”, “PROG” and“MOD”, “ACT”)  

      1.4.        Section 4 modes

? [ p ] - Process mode (default) ? [ S ]  - SQL mode

key press ‘p’ for process, ‘s’ for SQL switch (Section 4)  

Figure 7. Runtime switch Options ‘p’ (process) and ‘s’ (SQL)    

Notes.

1.     The SQL appearing in the SQL mode are for those with executions > 0.

2.     Buffer gets to disk reads Hit ratio (BH%) is calculated from:

100*(buffer_gets/(buffer_gets_disk_reads))

         1.5.            f   - Short (standard)/ long (detailed) format  

Changes back and forth, the format between the default standard short format and the detailed long format.

Figure 8. Runtime toggle Option ‘f’ (format)

         1.6.            t   - Tablespace information Context  

Figure 9. Runtime Tablespace information Option ‘t’  

Where,

        Column                                                 Description[4]

       CONTENTS                                           Tablespace contents: UNDO, PERMANENT, TEMPORARY

       EXTENT_MANAGEMENT                    Extents  are (DICTIONARY) managed or locally managed (LOCAL)

       SEGMENT_SPACE_MANAGEMENT    Tablespace is managed using free lists (MANUAL) or bitmaps (AUTO)

RETENTIONUndo tablespace retention: GUARANTEE, NOGUARANTEE, NOT APPLY ENCRYPTED         Tablespace is encrypted (YES) or not (NO)

    Table 1. Tablespace information header description (shaded for the default format)           1.7.            a   - ASM disk group information Context  

Figure 10. Runtime ASM diskgroup information Option ‘a’  

Where,

        Column                                                 Description[5]

VOTING_FILES    Indicates whether the disk contains voting files (Y) or not (N)  (red if ‘Y’) ASM_COMP     Minimum S/W version required for ASM instance to mount this DG DB_COMP        Minimum S/W version required for a DB instance to use files in this DG

    Table 2. ASM disk group information header description (shaded for the default format)           1.8.            x   - SQL Execution Plan table context  

Basic SQL execution plan table option for active SQL with SQL_ID.

Pressing the keyboard key ‘x’ will prompt the user to input the SQL_ID value. (see

Figure 11)

Figure 11. Runtime SQL Plan table Option ‘x’ (SQL mode)

      1.9.        [ i   - Delay interval input option

Pressing the keyboard key “i” at run time prompts the user to input the desired interval  in seconds. (see Figure 12)

Figure 12. Runtime refresh interval Option ‘i’

2.  Data Description

The run time help menu is driven by the preselected option(s).

The following sections will show help for section 4 of the SQL mode only.  Help for all the other sections follow in the the same manner.

2.1. Help SQL mode (Default, short format)

keyboard key “s” was pressed to select SQL mode for section 4 prior to pressing the help menu key “h”.

Figure 13 below shows description of section 4 columns while in SQL mode and default format.

Figure 13. Runtime Data Description (Section 4 SQL mode short format)

2.2.  Help SQL mode (Detailed, long format)

Here, the keyboard keys “s” and “f” were pressed to select SQL mode for section 4 in the detailed format  prior to pressing the help menu key “h”.

Figure 14 below shows detail of section 4 in SQL mode and detailed format.

Figure 14. Runtime Data Description (Section 4 SQL mode detailed format)

SNIPPETS

1.  [ Auto Toggle ] – Section 4 EVENT/LATCH column

“EVENT/*LATCHcolumn auto toggle across refreshes.  The auto toggle is activated whenever there is a wait on a latch and potential process hang or spin is detected.  It provide a useful information if such hang or spin last for a long duration.

Figure 15. Auto toggle between Event and latch name (process mode)

2.  Data Guard

Significant data guard indicators are highlighted in yellow as shown Figure 16 below.   In particular, the value for the apply rate (ar) is highly desired.

Figure 16. Snapshot from monitoring a standby database

3.  Database Service

Figure 17 below shows an example of oratop started with the “-c” option connecting via the user defined service named “sro”.  Service centric values are highlighted in light brown. Values not highlighted are database/ instance overall values.

Note. when in service mode, the database name in the header section will be replaced by the service name and highlighted in red.

Figure 17. Snapshot from monitoring database via Service

4.  12c Multitenant Database

4.1. Connection to CDB[6] root 

Relevant indicators to the Multitenant database are highlighted in yellow.  At CDB root, the header (section 1) will show the number of tenants housed in this Multitenant database.  For example, Figure 18 shows 3 pdbs (not including seed database).  

The “CID” column in the SQL mode stands for Container_ID.

Figure 18. Snapshot from a Multitenant Database connecting to root

4.2. Connection to a PDB[7](pluggable database)

Indicators to a tenant database are highlighted in a yellow for the next two subjects. 

4.2.3.  Connection without the “-c” command line option

Figure 19 shows a non-service based connection  (not using  “-c” flag) to a tenant having database name “pdb1” with container id (cid) 3 (which is the 1st pdb).  Columns with values being dimmed are not pdb specific, they are generic root level (CDB) unless they are enabled in the database.

Figure 19. Snapshot from a tenant Database (no “-c”)

4.2.4.  Connection with the “-c” command line option

Figure 20 shows shows relevant stats when connecting via service and using the“-c” flag to the same tenant “pdb1”. 

Highlighted values in red color shows significant differences with the previously shown Figure 19.

Figure 20. Snapshot from a tenant Database (service with “-c”)

5.  Batch Mode

Figure 21 below shows a snapshot from a batch run with output redirected to standard system file.

Figure 21. Single iteration batch/SQL mode

Note.

In RAC environment, while section 2 of oratop (Database section) is restricted to the top 5 instances in the text-based user interface, all of the available instances will be listed in the batch mode. 

Miscellaneous

? RED

Values flagged and highlighted in red color are merely a warning or to emphasize importance.

        ?     Exiting

To quit the program, user may press any of the following keyboard keys:

?     Character "q" or "Q", or Esc key

?     Ctrl+c  (to abort)

In all cases, proper OCI session cleanup and logout is performed.

        ?    Unprivileged user

To allow the non-privileged user to use oratop, the system administrator with DBA privileges may issue the following grants:

grant select on to ;

v$active_services v$instance v$latchname v$parameter V$PDBS (12c) v$recovery_file_dest v$sqlcommand v$system_event

[g]v$servicemetric

[g]v$session

[g]v$sql

[g]v$sysmetric

GV$ASM_DISKGROUP

GV$DIAG_INFO

GV$PGASTAT

GV$PROCESS

GV$OSSTAT

GV$RECOVERY_PROGRESS

GV$SESSION_WAIT_CLASS

GV$SESSTAT

GV$SERVICE_EVENT

GV$SERVICE_STATS

GV$SGA

GV$SORT_SEGMENT

GV$SQLAREA

GV$SQL_PLAN

GV$SYSTEM_EVENT

GV$SYSSTAT dba_data_files dba_free_space dba_tablespaces dba_temp_files dba_undo_extents sys.ts$

Table 3. internal views to be granted to the unprivileged user Note.  Typical error for a non-granted user may receive upon connection is,

ERROR CODE = 942

ORA-00942: table or view does not exist

TECHNICAL DETAILS

The “oratop” program is written in C program, using OCI API.  It is not intrusive to a database server, and it leaves small footprints on the server, namely the sqls used by the program.  It employs Unix “termio, “ioctl” libraries, and VT100 escape characters to achieve cursor control, font colors, program exit control and terminal (xterm) resizing capability.  

Overhead

On the client side, the oratop program is a very light process that uses minimal CPU and Memory.  For the server process, its resources are server dependent.

Remark.

oratop’s specific queries are intentionally filtered out from the SQL section to reduce noise.  Information regarding these queries can be found on the server using AWR.

The intention of using this utility is to get a quick glance at the database.  The oratop SQL statements will show up in the top SQL section of AWR due to the high execution rate when running for a long duration and a short interval. Like any other SQL, oratop SQL performance will be impacted on over loaded systems.  

Limitations

        ?      The program is not portable; it runs on Linux platforms only

?         The program is compatible with oracle client version11.2 and 12cR1

?         Requires server to have been started with the following parameter statistics_level=TYPICAL[8]

Caveats

The program may exhibit anomalies at run time, most of which are expected since an event may occur while the program is in the middle of executing/ fetching operations. 

Blank Screen

On a busy server, the following operations may leave the terminal blank for a short period:

?       A program initialization

?       A terminal resizing (shrink/expand the terminal)

?       An instance joining/leaving the cluster

?       Quitting interactive keys menus

?       Upon exiting the program

Sluggish response to keyboard key press

Pressing a keyboard key to interact with the program may appear to have slow response on a busy server.  Key press response time depends on the interval/execution state.

Abnormal termination

Like any SQL session, the program may fail due to a server error, and the particular error will be displayed.

Abnormal exit

In some situation (corner cases) the program may leave the terminal (xterm) SHELL environment in an undesirable setting.  To restore the terminal to its original settings, he user may issue the Linux command “reset”.

CONCLUSION

The oratop utility provides dba user a quick overview of a running database status and activity.  It aggregates useful diagnostics such as overall database performance degradation, bottleneck, identifies potential blocker(s), and helps to detect memory leak from processes.  The utility also reports badly performing SQL operations as well.

Furthermore, it helps in tuning some aspects of the database like user concurrency.

Modifications:

Version 14.1.2 – May 2014, bug fix for batch mode. (SR 3-89597485410)

Version 14.1.1 – Apr 2014, Fix few minor bugs,

Version 14.1.0 – Mar 2014, New release with enhancement for sql, data guard, Multitenant database, Version 13.3.4 – Aug 2013, display oratop version/uptime as xterm title, also added a new key press ‘g” toggle, Version 13.2.4 – Jul 2013, Top waits change for cpu stats by stats name and waits by sum. Version 13.2.3 – Jun 2013, All instances in section 2 will be listed implicitly in batch mode.

Version 13.2.2 – Jun 2013, Introduced new switches, IOPS/IORL, MOD,ACTN/USR,PROG.

Version 13.2.0 – May 2013, Long format and Top 5 dynamic wait events.

Version 13.1.0 – Mar 2013, sqlplus connection like and the doc were updated from June 2011.

Version 12.1.0 – Dec 2012, added help key “h”. Versions 1-12  – Mar 2010, created.