Oratop tool-real-time database performance monitoring tool
Oratop is a tool similar to top, which can do a comprehensive view of Oracle Database Performance. If used with top, you will get a more complete system and Database performance information.
Startup command:
./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
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
Also, 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.
NOTE: oratop on any platform can be used to monitor databases on other platforms.
Supported Applicable versions:
Oracle Database-Enterprise Edition-Version 11.2.0.3 to 11.2.0.4 [Release 11.2]
Oracle Database-Enterprise Edition-Version 12+ any DB Version.
Linux x86–64
Linux x86
From a functional point of view, Oratop has three main characteristics:
- Monitor current database activity
2. Monitor database performance;
3. Identify current blocked sessions and bottleneck sessions;
At present, Oratop is free to download on MOS , only Linux version, there are two versions of 32 -bit and 64 -bit.
Download the oratop executable for:
- 11gR2 on Linux x86–64 here.
2. 12c on Linux x86–64 for use with 12c clients here.
3. 11gR2 on Linux x86–32 here.
4. 12c on Linux x86–32 for use with 12c clients here.
5. 11gR2 for Solaris SPARC here.
6. 12c for Solaris SPARC here.
7. 11gR2 for Solaris x86 here.
8. 12c for Solaris x86 here.
9. 11gR2 for AIX here.
10. 12c for AIX here.
11. 11gR2 for HP-UX PARISC here.
12. 11gR2 for HP-UX IA here.
13. 12c for HP-UX IA here.
Instructions:
1. Syntax
$ oratop [Options] [Logon]
2. Options
a) Help, Displays usage or output information.
Default: N/A
$ oratop -h[elp] # runtime mode press h
b) Top 5 Wait Events
preset: Cumulative
option: instant presentation
$ oratop -d # runtime mode press d
c) Wait for the object
preset: Event/Latch
option: File#:Block#
$ oratop -k # runtime mode press k
d) Session behavior
preset: yes Username/Program
option: yes Module/Action
$ oratop -m # runtime mode press m
e) SQL mode
default: Process mode
option: SQL display
$ oratop -s # runtime mode press s
f) Database Service centric mode
default: Connection mode
option: N/A
$ oratop -c # runtime mode:N/A
g) Long format, (132 columns)
Default: short (80 columns)
Option: long format for header & process section.
$ oratop -f # runtime mode: press f
h) Database Process mode
preset: Process mode
option: process display
$ oratop -p # runtime mode: press p
i) Batch mode operation
preset: Text-based user interface
option: N/A
$ oratop -b # runtime mode: N/A
j) Maximum Number of iterations. (requires a number)
Default: infinite
Options: the maximum number of iterations, or frames
$ oratop -n # runtime mode: N/A
k) Displays tablespace information
preset: N/A
option: tablespace information
# runtime mode: Press t
l) Displays ASM diskgroup information. (no op for non-ASM)
Default: N/A
Option: ASM diskgroup information
# runtime mode: Press a
l) Displays SQL execution plan table. Requires input of the active SQL sql_id value
Default: 5 seconds
Options: the delay between update refresh
$ oratop -c # runtime mode: press
m) Interval delay time. (requires value in seconds)
Default: Connection mode
Options: N/A
$ oratop -v # runtime mode: N/A
Run oratop (updated every 10 seconds)
$ ./oratop -i 10 / as sysdba
Run oratop against the remote database (updated every 10 seconds)
$ ./oratop -i 10 system/manager@tns_alias
End oratop and
press “q”, or CTRL-C
Oratop Output results details:
Section 1- oratop and database/instance specifics
spid :oratop’s server SPID connected to instance,
SID :instance name (First 4 letters),
clock :Time of current stats collected (hh24:mi:ss)
time :uptime since this instance startup,
ins :Total number of active database instance(s),
MT :database total memory (sum Memory_Target),
ses :Total user’s sessions,
usr :Total distinct database foreground (F/G) users,
%DB :%database Active, 0 0ndicate idle db
Section 2- Cluster-wide “Top 5 instances activity” ordered by Wait Time
ID :instance id,
%HC :Host CPU Utilization, %(busy/busy+idle),
HLD :Current Host Load,
MBPS :throughput, ‘I/O Megabytes per Second’,
%FR :Shared Pool Free %,
PGAU :total PGA inuse,
IORL :Avg Synchronous Single-Block Read Latency,
PQS :PQ Slave Session Count,
ASC :Active Sessions on CPU,
ASI :Active Sessions waiting on user i/o,
ASW :Active Sessions waiting/sleeping,
AST :Total Active Sessions,
ASB :Active Sessions blocked,
AAS :Average Active Sessions,
CTPS :(CPS) User Calls Per Sec, OR, (TPS) User Transactions Per Sec (appears in RED),
SSRT :SQl service Response time,
DBC :database cpu time ratio,
DBW :database wait time ratio.
Section 3- Cluster-wide “Top 5 Timed Events” ordered by Wait Time
EVENT :database wait event,
WAITS :cluster average Total waits,
TIME(s) :cluster average Total wait time in seconds),
AVG_MS ::cluster Average wait time in milliseconds,
PCT :Percent of wait time,
WAIT_CLASS :Name of the wait class corresponding to the event.
Section 4- Session/process none-idle ordered by Wait Time desc.
ID :instance id,
SID :session id,
SPID :server process os pid,
USR :user name (abbreviated),
PROG :program name (abbreviated),
PGA :process pga used,
OPN :OPERATION name, e.g. SELECT,
SQLID :SQL identifier if no blocker, else,
/BLOCKR :final blocking instance:session:ospid,
E/T :Elapsed Time since the session has become active. Units: (s)econds, (M)inutes, (h)our, (d)ay.
STATUS :ACTIVE | INACTIVE | KILLED | CACHED | SNIPED,
STE :Process STATE: ON CPU or USR I/O or WAITING,
WAIT_EVENT :process wait event name,
W/T :Wait Time. Units: (u)-micro second, (m)illi second, (s)econds, (M)inutes, (h)our, (d)ay.
Reference documents:
1. oratop-Utility for Near Real-time Monitoring of Databases, RAC and Single Instance (Doc ID 1500864.1)
2. TFA Collector — TFA with Database Support Tools Bundle (ID 1513912.1)
Thanks for reading this post, please follow me up to get latest update for a new post.
Pankaj K.
www.pankajconnect.com
