Monitoring Oracle database using Perfmon

Performance monitoring is the essential part of performance tuning exercise. Oracle database monitoring is no exception to this rule. As such Oracle database provides various ways to monitor such as statspack, AWR, tracing etc., Some people might be quite interested in seeing numerical values such as hit ratios, DBWR statistics etc., so that they can plot graphs against load and see how the trend is. Oracle running on Windows based Operating System provides features which help to monitor the system via perfmon (click here to know more about perfmon).

By default Oracle database related counters are not exposed to perfmon. But there are couple of ways to make it appear on perfmon. 

1. By using custom installation

2. By using operfcfg utility

3. By using administrative Assist on Windows

4. By adding couple of values in registry

First three ways indirectly adds the values to registry (hides complexity from end user). Oracle recommends user to user operfcfg utility. I am going to explain how to use operfcfg to add Oracle database counters in perfmon.

operfcfg is an Oracle Database tool that you run from the command prompt. Use this syntax:

operfcfg [-U username] [-D TNS_Alias_for_database] [-P password]

username is the username registry parameter value that Oracle Counters for Windows Performance Monitor uses to log in to the database. You must have a DBA privilege on this database.

TNS_Alias_for_database is the net service name that Oracle Counters for Windows Performance Monitor uses to connect to the database. It affects the Hostname registry parameter. The net service name corresponds to the SID of the database that you want to monitor. The -D command can be specified without providing a database name value.

password is the password registry parameter value for username.

If we omit –D & -P, then utility will take the default database on that system and it will interactive asks for password to connect to that user.

Debugging: 

If you are not able to see Oracle database counter after running through operfcfg utility, then

1. If you are using TNS alias then try to ping the database using tnsping utility and check whether you are able to reach the database. Click here to know more about tnsping utility

2. Try to connect to the Oracle database using sqlplus with the username, password provided for operfcfg utility.

3. Make sure the username provided has DBA privilege.

Once you got the counters, then enjoy the performance monitoring & tuning excerise. As usual, you comments are always welcome regarding my performance tuning hints.

Cheers,

Thiru

OS Monitoring

Monitoring a server isn't something you should do chaotically. You need to have a clear plan—a set of goals that you hope to achieve. Troubleshooting server performance problems is a key reason for monitoring. Not just to plot good looking graphs and show it to superiors. Without monitoring, tuning is almost an impossible activity.

Basically monitoring is done for 2 purposes

1. Benchmark
2. Tuning

While monitoring OS, following are the basic things need to be considered regardless of OS platform.

1. CPU Utilization
2. Memory, paging
3. Throughput & retransmission statistics
4. TCP statistics
5. Disk statistics

Not all things need to be presented in report. Presenting 10-20 page report containing only relevant is always better than presenting a 100 page report (album). If you want to show your hard work, create annexure section and attached to it (I may not call it album here. After-all we are showing our hard work at relevant place. He he).

Linux Monitoring

Monitoring Linux based systems are not as complex as many people thing. All we need to know is just simple, basic shell scripting and few basic commands.

To know the version of Linux kernel
uname –a

vmstat command
vmstat reports information about processes, memory, paging, block IO, traps, and CPU activity.

Basic syntax
vmstat [[delay] count]

Example
vmstat 10 7

The first statistics that are printed are averaged over the system uptime.Don’t consider this unless it really make sense.

See man page for more information

iostat command
iostat displays kernel I/O statistics on terminal, device and cpu operations.

Basic syntax
iostat [[delay] count]

Example
Iostat 10 7

The first statistics that are printed are averaged over the system uptime.Don’t consider this unless it really make sense.

netstat command

netstat prints network connections, routing tables, interface statistics, masquerade connections, and multicast memberships. There are a number of output formats, depending on the options for the information presented.

See man page for more information

Nmon utility

Quote from IBM
“This free tool gives you a huge amount of information all on one screen. Even though IBM doesn't officially support the tool and you must use it at your own risk, you can get a wealth of performance statistics. Why use five or six tools when one free tool can give you everything you need?”

The nmon tool runs on:

1. AIX® 4.1.5, 4.2.0 , 4.3.2, and 4.3.3 (nmon Version 9a: This version is functionally established and will not be developed further.)
2. AIX 5.1, 5.2, and 5.3 (nmon Version 10: This version now supports AIX 5.3 and POWER5™ processor-based machines, with SMT and shared CPU micro-partitions.
3. Linux® SUSE SLES 9, Red Hat EL 3 and 4, Debian on pSeries® p5, and OpenPower™
   Linux SUSE, Red Hat, and many recent distributions on x86 (Intel and AMD in 32-bit mode)
4. Linux SUSE and Red Hat on zSeries® or mainframe

Click here for more information.

Windows Monitoring
Windows provide huge set of monitoring counters to end users. Not all need to be monitored always, but all counters might be useful at one point or another. All windows OS related counters can be monitoring using perfmon (just goto run and type perfmon).

I am planning to write Step by step instruction on how to monitoring windows using perfmon later in as a separate blog.