Oracle Disk Monitoring
Oracle Tips by Burleson Consulting
Oracle Disk Monitoring
Everyone has better things to do than sit around and run disk
performance scripts. The process can be automated using scripts and
cron tab in UNIX
and Linux. A simple web search using any search tool of choice, one
can find any number of shell, perl and other scripts to monitor
Rather than recreating the wheel, the web can be used to find
examples of scripts. Mike Ault’s book,
Oracle Disk I/O Tuning (2004, Rampant TechPress), also
provides a plethora of scripts and examples.
Tuning professionals have noted that the primary bottleneck is disk
I/O for the vast majority of non-scientific systems. Back in the
days before RAID and giant
db_cache_size, the DBA had to manually load balance the disk I/O
subsystem to relieve contention on the disks and the disk
However, is it really important to find a hot disk? Unless the
system is running in single disk sets (jbod
technology), specific knowledge about a hot disk is not quite as
important. However, the DBA should still be interested in disk
performance and tracking data file and other I/O involved in
databases. The database administrator and system manager must still
monitor I/O, either through Oracle, through the OS, or via
performance monitoring tools provided by the disk array or storage
system software vendors such as Veritas.
Many DBAs would like to believe that this disk technology has
changed. Unfortunately, the only major changes to disk technology
since the 1970s are these hardware and software changes:
Today the DBA can cache large portions of the data blocks in the
reducing disk I/O.
Many of the newer disks have an on-board RAM cache to hold the most
frequently referenced data blocks.
of data blocks with RAID 1+0 and RAID 5 for low-write systems has removed the need for disk load balancing
by scrambling the data blocks across many disk spindles. In
Oracle10g, the Automatic Storage Management (ASM) feature requires
Stripe and Mirror Everywhere (SAME) which is essentially
RAID 1+0 and RAID-10.
Other than these advances, basic disk technology has not changed
since the 1970s. The Oracle DBA must remember that disk I/O remains
an important issue and understand the internals of disk management
to maximize the performance of their I/O-bound systems.
A vendor-specific disk-level I/O monitor
When Oracle monitoring is used, at least in 9i and lower versions,
DBAs were somewhat limited in what monitoring could be performed.
Total I/O since startup and I/O/second since startup can be
captured. This information could be acquired on a per datafile or
tempfile basis. The
v$tempstat dynamic performance views
provide the I/O statistics from Oracle’s point of view.
SEE CODE DEPOT FOR FULL SCRIPTS
This is an excerpt from my latest book "Oracle
Tuning: The Definitive Reference".
You can buy it direct from the publisher for 30%-off and get
instant access to the code depot of Oracle tuning scripts: