Monday, June 26, 2006
Oracle Performance Monitoring on Windows
Performance monitoring is a regular task for most Oracle DBAs. For Oracle on Windows this is no exception. The only difference are the tools that are available to you. With Oracle on Windows you still have all of the tools available to you that are available on other platforms, such as the Automatic Workload Repository, but in addition, you can view Oracle statistics via the Windows Performance Monitor (perfmon).
There are several advantages to monitoring Oracle via perfmon. By using perfmon, you can collect Oracle data as well as OS data that might be important to your overall performance monitoring plan. By consolidating your data collection, you will find it easier to analyze data. In addition, perfmon data can be collected and analyzed via the Microsoft Operations Manager (MOM) product. MOM allows you to collect and analyze long term data as well as to configure alerting functions that can let you know that there is a problem.
By default, Oracle statistics are not collected for use by perfmon. By following the steps outlined here you can quickly and easily configure Oracle for perfmon data collection. By default, you still have many ways of monitoring Oracle. Among these are the following:
- Oracle Automatic Workload Repository. The Automatic Workload Repository (AWR) is performance data that is automatically collected by Oracle for performance and trouble analysis.
- Event Viewer Alerts. The event viewer alerts are events that are automatically logged directly into the Windows alert log.
- Oracle Alert Log. The Oracle alert log is the log where Oracle logs status and error messages.
- Trace Analyzer. Oracle sessions can be traced and that trace data analyzed via the Oracle trace analyzer.
The Oracle Counters for Windows Performance Monitor package is not installed by default. In order to install them when you install Oracle, select the custom install option. You can also install this option later via the Oracle installer. Select custom installation and select the Oracle for Windows Performance option. This will install this package.
Once Oracle Counters for Windows Performance Monitor has been installed, you must perform one more piece of setup. The Oracle performance counters are set up to monitor one Oracle instance. Information about this instance must be configured in the registry. In order to do this, from a command prompt run orafcfg.exe with a username, password and Oracle net service name as follows:
operfcfg –U system –P password –D orcl
This will update the registry. You should now be able to monitor Oracle via perfmon. Some of the things that you can monitor are:
- The Oracle Buffer Cache. Here you can see the cache miss ratio.
- Shared Pool Stats. This collection includes the data dictionary cache, and the library cache.
Log Buffer. Provides information on log space requests.
- Database Data Files. This object provides physical read and write per second counters.
- DBWR stats. Provides information on the DB Writer processes.
- Miscellaneous. Other statistics include dynamic space management, free lists and dynamic sorts.
By taking advantage of Oracle Counters for Windows Performance Monitor you can easily and efficiently monitor Oracle along with monitoring the OS. As I mentioned earlier, perfmon provides valuable performance data that is easily collected and analyzed. Some of the most important and first counters that I look at when performance monitoring a system are:
- Processor: %Processor Time. This gives me a quick look at how busy the system is.
- Physical Disk: Avg. Disk sec/Read, Avg. Disk sec/Write. This provides me with an overview of how well the I/O subsystem is doing.
When first looking at a system I am actually more interested in disk latencies than throughput. The Avg. Disk sec/Read and Avg. Disk sec/Write should be in the range of 5-15 ms (0.005 – 0.015). Anything higher than this indicates a problem. Once I have detected a problem, I can try to determine its cause.
Of all of the platforms I work on; Linux, AIX, HP/UX, and Solaris, I find that Windows provides the best performance monitoring tool. Perfmon is easy to use, quite intuitive and easy to view and save the results in a number of formats. Oracle Counters for Windows Performance Monitor is not an end-all, but in conjunction with AWR stats and tracing will help to provide a complete picture.
Came across your site while browsing around…cool stuff u have going on here. Also I thought I’d tell u about something I came across, thought u might find it useful, bcoz ur in Technology…it’s this site called Myndnet…u should check it out..the link is here http://www.myndnet.com/login.jsp?referral=alpa83&channel=al450
It’s this cool place where u get paid for responding to queries…very cool stuff!! http://www.myndnet.com/login.jsp?referral=alpa83&channel=al450
Sign up n lemme know what u think…my mail id is firstname.lastname@example.org
This is great article. I have been struggling to monitor oracle statistics. I have used perfmon for monitoring MS SQL Server 2000. When I installed Windows Performance Monitor package it showed me only 14 counters/parameters. How do I monitor rest of the counters using perfmon.
Thanks in advance
operfcfg does not seem to work for 11g. all the time the command gets kicked off it responds as if some syntax error had been detected (but i'm shure there is none). however, with 11g, it is also possible to use administration assistent for windows to set the values in question. this works.
I want to monitor database counters on ORACLE db server to identify any deadlocks. System is X86, Oracle version 10g.
Can anybody please help or tell how can i do the same. I using Jmeter for performance testing.
Can I use windows PERFMON tool on the database server which on UNIX to monitor the same.?
sap technical upgrade
sap technical upgrade