合 oratop工具--实时数据库性能监控工具

• 简介
• 安装
• 运行
• 解释
• 丰富的帮助系统
• 复杂应用
• 参数
• oratop - Utility for Near Real-time Monitoring of Databases, RAC and Single Instance (文档 ID 1500864.1)
• APPLIES TO:
• PURPOSE
• REQUIREMENTS
• CONFIGURING
• INSTRUCTIONS
• Quick Start Guide
• CAUTION
• Abstract
• INTRODUCTION
• ENHANCEMENTS
• SECTIONS
• CLIENT
• II. INTERACTIVE MODE
• 1. Interactive Toggle keys
• 2. Data Description
• 1. [ Auto Toggle ] – Section 4 EVENT/LATCH column
• Figure 15. Auto toggle between Event and latch name (process mode)
• 2. Data Guard
• Figure 16. Snapshot from monitoring a standby database
• 3. Database Service
• Figure 17. Snapshot from monitoring database via Service
• 4. 12c Multitenant Database
• Figure 18. Snapshot from a Multitenant Database connecting to root
• 4.2. Connection to a PDB[7](pluggable database)
• Figure 19. Snapshot from a tenant Database (no “-c”)
• 4.2.4. Connection with the “-c” command line option
• Figure 20. Snapshot from a tenant Database (service with “-c”)
• 5. Batch Mode
• Figure 21. Single iteration batch/SQL mode
• ? Exiting
• ? Unprivileged user
• TECHNICAL DETAILS
• Overhead
• ? The program is not portable; it runs on Linux platforms only
• Caveats
• Blank Screen
• Sluggish response to keyboard key press
• Abnormal termination
• Abnormal exit
• CONCLUSION
• 总结

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 in*tnsnames.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.

[

](http://images2017.cnblogs.com/blog/646850/201707/646850-20170726171257281-1357638666.gif)**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)

[

](http://images2017.cnblogs.com/blog/646850/201707/646850-20170726171300875-1736848683.gif)**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 1*st 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.

[1] Connecting “ / as sysdba“ is allowed only for bequeath connections.

[2] The remote database requires setting the “REMOTE_LOGIN_PASSWORDFILE” paired with the availability of an Oracle password file

[3] The asterisk (*) prefixing the latch name is used to distinguish it from the wait event name.

[4] Oracle? Database Reference, 11g Release 2 (11.2) E40402-10 [dba: _tablespaces, _data_files, _free_space, _undo_extents]

[5] Oracle? Database Reference, 11g Release 2 (11.2) E40402-10 [GV$ASM_DISKGROUP]

[6] Also referred to as Consolidated database.

[7]Also referred to as Tenant or a container

[8] Statistics_level cannot be set to “BASIC” since it will disable the required timed_statistics

总结

1、从Oracle 19c开始，默认自带，路径：$ORACLE_HOME/suptools/oratop/oratop

2、运行：

相关文章

• 在Oracle中，什么是orachk工具
• Oracle SQL Developer安装使用
• Oracle数据库一键升级工具AutoUpgrade介绍（Doc ID 2485457.1、1577660.1）
• 在Oracle中， IS NULL如何用到索引？
• 在Oracle的存储过程、函数或触发器中，如何将相关错误信息写入告警日志中
• 在物理 standby 数据库上如何解决 MRP 进程卡住的问题？ (Doc ID 2331842.1)(Doc ID 1221163.1)
• Oracle中的临时表空间简介和维护
• Oracle中，若临时表空间使用率过高有什么调优思路？
• Oracle环境中报错TNS-00511、TNS-12541、No listener，其实监听已启动
• DGMGRL ADD DATABASE Failing with ORA-16191 (Doc ID 3013838.1)
• ORA-16191 and ORA-1017 reported for Log Transport to Standby when using redo_transport_user (Doc ID 2848435.1)
• Troubleshooting ORA-16191 and ORA-1017 ORA-1031 in Data Guard Log Transport Services or Data Guard Broker (Doc ID 1368170.1)
• Oracle 12c的DG自动同步密码文件--ASM 新特性：共享密码文件
• Oracle DG中日志不同步的ORA-16191错误解决整理
• Oracle的DG环境中由于配置了登陆触发器导致日志不能同步报错ORA-16191的实验
• Oracle将秒转换为时分秒的格式
• 在Oracle的DG环境中，主库新增数据文件的过程
• Oracle对索引做monitoring和nomonitoring会导致游标失效
• 客户某套Oracle rac业务库出现严重的library cache等待排查
• Oracle中的临时表介绍