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 level:

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 within Oracle.)
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 monitoring strategy:

Proactive or reactive monitoring? - Do we collect real-time data for crisis management, or collect time-series snapshots for predictive analytics?
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 custom interface.

So, where do we start? Let's start with Oracle own metadata and cover these topics:

Inside Oracle's time-series monitoring infrastructure (AWR, STATSPACK and ASH)
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:

http://www.dba-oracle.com/oracle_monitoring_best_practices.htm

*****************************************

Need a Health Check?

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:

http://www.dba-oracle.com/bc-catalog.pdf