Oracle Monitoring Best Practices
All DBA's are required to set-up an Oracle monitoring infrastructure and the
architecture of the monitoring system is directly dependent on the demands of
management. Oracle system cost the end-user community millions of dollars,
and the end-user often demand service-level agreements (SLA's) that impose
strict monitoring tasks for the Oracle professional.
In many shops, the
end-user community is demanding Oracle performance statistics at a detailed
- A financial services company demands that 95% of their Oracle
transactions complete within one wallclock second. The DBA most
develop a monitoring strategy to ensure compliance. (Note: there are
special techniques that can be employed to get end-to-end response time
- A stock brokerage demands that the DBA write a sophisticated real-time
proactive monitor. This monitor will quickly diagnose and pinpoint
Oracle bottlenecks, but getting the data is a problem. It requires
real-time access to all internal Oracle control structures. Running
the monitor imposes a measurable burden on overall system load.
- A manufacturing plant requires measuring response time by
hour-of-the-day to ensure fast throughput for all shifts.
These are just a few examples of the business demands that drive the Oracle
professional to create sophisticated monitoring infrastructures. Let's
example some of the most important issues when making these decisions.
The art of gathering Oracle performance data
There are several choices that need to be made when architecting an Oracle
- Proactive or reactive monitoring? - Do we collect real-time data
for crisis management, or collect time-series snapshots for predictive
- Collect external statistics? - Oracle does not run in a
vacuum, and the speed of disk, network and CPU have a profound influence on
overall response time.
- What statistics? - Oracle provides hundreds of statistics that
are cryptic and undocumented (i.e.
calls to kcmgas), and
you must decide what metrics are best suited to your business need.
For example, a requirement to measure end-to-end response time would benefit
from monitoring network latency (SQL*Net Roundtrips), but only tangentially
from monitoring data buffer cache efficiency.
- What level of granularity? - What are the tradeoffs between
super-details statistics and system performance? How much data is
required for a statistically significant sample size?
- Build or Buy? - While the structures of Oracle performance
collections are well documented, the Oracle professional needs to determine
whether to purchase off-the-shelf Oracle monitoring tools or build their own
So, where do we start? Let's start with Oracle own metadata and cover
- Inside Oracle's time-series monitoring infrastructure (AWR, STATSPACK
- Inside the Oracle v$ performance monitoring views
- A review of extra cost monitoring tools and options
- Building your own monitoring architecture
Read more about Oracle monitoring best practices here:
Oracle is the worlds most
complex and robust database and there are hundreds of sub-optimal setting that
can cripple your database performance.
Burleson Consulting has a great Oracle health
check where we identify all database bottlenecks to ensure that your mission-critical
system is running at optimal speeds.
Just call 800-766-1884 to
schedule your health check.
Need Oracle Training?
The very best Oracle training
comes from Burleson Consulting, where you get an on-site visit by an experienced
Oracle expert and author. Whether it's one-on-one mentoring or getting a
customized on-site Oracle training class, there is no substitute for BC Oracle
training. Just call 800-766-1884 for details, and check-out our
on-site Oracle training catalog at the following link: