Monitoring Oracle with oratop

Intro

I recently had a situation where I was troubleshooting a system that had fallen victim to some intermittent and very high load averages.  At first, there were no apparent reasons for the high load averages, so that sent me searching for some monitoring tools to help identify the issue.  Besides using OSWBB and dstat, I also used the oratop utility which turns out to be a great way to get a quick overview and monitor active sessions in near real time in Oracle 11g and 12c databases.  It is also RAC and ASM aware.

 

References

Here’s the MOS note for more info and downloads.
oratop – Utility for Near Real-time Monitoring of Databases, RAC and Single Instance (Doc ID 1500864.1)

 

Supported Oracle Releases

11gR2 (11.2.0.3, 11.2.0.4)

12cR1

oratop in Action

There is a user guide available for download on the MOS note, but I will show my favorite options.

 

Startup

> ./oratop -f  -d -i 10 / as sysdba
The switches:
f – detailed format option
d – real time wait events ( the default is cumulative )
i – update interval in seconds

The remaining part of the string is the login.  Basically, any string that you use for sqlplus should work with oratop.

oratop_df

 

Output Sections

Here’s an explanation of the sections.  These menus can be found when pressing “h” when running oratop in interactive mode.

 
1 Database
Section 1 - database
        Global Database information

   Version        : Oracle major version
   role           : database_role
   db name        : db_unique_name
   time        [s]: time as of the most recent stats (hh24:mi:ss)
   up          [T]: database uptime
   ins         [N]: total number of instance(s)
   sn        [c,N]: total user sessions (active/inactive)
   us        [c,N]: number of distinct users
   mt        [s,N]: global database memory total (sga+pga)
   fra         [N]: flashback recovery area %used, (red > 90%)
   er          [N]: diag active problem count (faults)
   % db      [s,N]: database time as %(dbtime/cpu) (red if > 99%)

 

2 Instance

Section 2 - instance
        Top 5 Instance(s) Activity
        o Ordered by Database time desc

   ID        [c,N]: inst_id (instance id)
   %CPU      [m,N]: host cpu busy %(busy/busy+idle). (red if > 90%)
   LOAD      [m,N]: current os load. (red if > 2*#cpu & high cpu)
   %DCU      [m,N]: db cpu otusef as %host cpu. (red if > 99% & high AAS)
   AAS       [s,N]: Average Active Sessions. (red if > #cpu)
   ASC       [c,N]: active Sessions on CPU
   ASI       [c,N]: active Sessions waiting on user I/O
   ASW       [c,N]: active Sessions Waiting, non-ASI (red if > ASC+ASI)
   ASP       [m,N]: active parallel sessions (F/G)
   AST       [c,N]: Active user Sessions Total (ASC+ASI+ASW)
   UST       [c,N]: user Sessions Total (ACT/INA)
   MBPS      [m,N]: i/o megabytes per second (throughput)
   IOPS      [m,N]: i/o requests per second
   IORL      [m,T]: avg synchronous single-block read latency. (red > 20ms)
   LOGR      [s,N]: logical reads per sec
   PHYR      [s,N]: physical reads per sec)
   PHYW      [s,N]: physical writes per sec
   %FR       [s,N]: shared pool free %
   PGA       [s,N]: total pga allocated
   TEMP      [s,N]: temp space used
   UTPS      [s,N]: user transactions per sec
   UCPS    [c,m,N]: user calls per sec
   SSRT    [c,m,T]: sql service response time (T/call)
   DCTR      [m,N]: database cpu time ratio
   DWTR      [m,N]: database wait time ratio. (red if > 50 & high ASW)
   %DBT      [s,N]: instance %Database Time (e.g. non-rac shows 100%)

 

3 Wait Events

Section 3 - db wait events
        Top 5 Timed Events
        o Cluster-wide, non-idle
        o Ordered by wait time desc

  EVENT      : wait event name. (red if active)
        (RT) : Real-Time mode
  WAITS      : total waits
  TIME(s)    : total wait time in seconds)
  AVG_MS     : average wait time in milliseconds
  PCT        : percent of wait time (all events)
  WAIT_CLASS : name of the wait class

 

 

4 Processes

Section 4 - process
        o Non-Idle processes
        o Ordered by event wait time desc

   ID          [N]: inst_id. (red if blocking)
   SID         [N]: session identifier. (red if blocking)
   SPID        [N]: server process os id
   USERNAME       : Oracle user name
   PROGRAM        : process program name
   SRV            : SERVER (dedicated, shared, etc.)
   SERVICE        : db service_name
   PGA         [N]: pga_used_mem. (red if continuously growing)
   SQL_ID/BLOCKER : sql_id or the final blocker's (inst:sid, in red)
   OPN            : operation name, e.g. select
   E/T         [T]: session elapsed time (active/inactive)
   STA            : ACTive|INActive|KILled|CAChed|SNIped
   STE            : process state, e.g. on CPU or user I/O or WAIting
   WAIT_CLASS     : wait_class for the named event
   EVENT/*LATCH   : session wait event name. Auto toggle with *latch name.
                    (red if process is hung/spin)
   W/T         [T]: event wait time. (red if > 1s)

 

 

Blocking Sessions

If there are blocking sessions present, the instance and sid will be shown in the SQL ID/BLOCKER column.

oratop_blocking_session

 

Stopping

To quit the program, user may press any of the following keyboard keys:
Character “q” or “Q”, or Esc key
Ctrl+c (to abort).

 

Execution Plans

By pressing “x”, and then giving the sqlid, the explain plan of the sql in question will be displayed.  Although this is a nice and quick feature, it does not show as much detail about the plan as the display_cursor procedure will as shown below.  As far as I can tell, it does show the actual plan and not the estimated ones that are displayed without acttually running the query, but I have not thorougly tested this.
select * from table(dbms_xplan.display_cursor(‘&sql_id’,’&child_no’,’typical’));

 

Space

The “a” option can be used to show info on the disk groups.
The “t” option can be used to show info on the tablespaces.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s