Question: How can I
monitor Oracle performance in Windows? There are tools for monitoring
Oracle in Enterprise Manager, but I want a way to monitor Oracle performance in
Windows. What options are
available for Oracle Windows performance monitoring?
Answer: Oracle 10g has views in 10g that capture server-side information
on CPU and RAM consumption. Get the
Oracle 10g performance poster
for descriptions of these views like
dba_hist_osstat that display Windows CPU, RAM and disk
performance details. Oracle 10g OEM also
displays Windows server data on CPU, RAM and disk usage.
I
also have Oracle scripts that display Windows server performance information in
my book
"Oracle
Tuning: The Definitive Reference", and you can get
instant access to the code depot of Oracle scripts.
You can also enhance the Windows Performance Monitor to include
Oracle data, as noted by
Ed Whalen:
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.
See this note on
Oracle 9i Windows
performance monitoring. Here is a script to monitor Oracle disk performance
in Windows:
@ECHO OFF
REM +-------------------------------------
REM | Set up client specific variables
REM +-------------------------------------
set BC_DIR=C:\BC
REM +------------------------------------------------------------
REM | Define minimum space value in floating point format
REM | Example: 1E5=100,000 (100K) 1.75E5=175,000 (175K)
REM | 2E6=2,000,000 (2MB)
REM +------------------------------------------------------------
set MINSPACE=1.75E5
ucd %BC_DIR%\script
REM +------------------------------------------------------------
REM | Now let's go get disk space information for all disks and
REM | remove commas from the byte values
REM +------------------------------------------------------------
df
-a -t | sed -e "s/,//g" > df.txt
REM +------------------------------------------------------------
REM | Select lines for disks that fall within the critical free
REM | space range and send them to the ALERT.TXT file
REM +------------------------------------------------------------
getrng "-d)" -f2 -r 0,%MINSPACE% df.txt > alert.txt
REM +------------------------------------------------------------
REM | If we found disks critically low on space, send an e-mail
REM | alert to the DBA staff
REM +------------------------------------------------------------
test -s1 alert.txt run: sendalrt.bat alert.txt
rm
-s df.txt
rm
-s alert.txt
exit
and include the URL for the page.
