Thursday, March 5, 2009

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

5 comments:

Anonymous said...

Hi,

I am glad to introduce a new TechNet forum for Perfmon.
http://social.technet.microsoft.com/Forums/en-US/perfmon/threads/

Thanks.

Unknown said...

Hi
I followed the above steps but while launching perfmon from Oracle Counters for Windows Performance Monitor, I got this error:

Unable to add these counters:

Oracle 11 Sorts(0)\sorts in memory /sec
Oracle 11 Sorts(0)\sorts on disk/sec

The perfmon appears but no oracle counters are available. Please help!

Logan Seth said...

same issue here:

Oracle 11 Sorts(0)\sorts in memory /sec
Oracle 11 Sorts(0)\sorts on disk/sec

Was a solution found?

Anonymous said...

You need to run this first - replace -U -P and -D arguments as necessary:
operfcfg -U system -P systemPassword -D myDbSID

rmouniak said...

It's a very nice post, Thanks For sharing
Oracle SOA Online Course Bangalore