Monitoring Methodologies
- Reactive
- Proactive
Reactive monitoring is not a recommend approach but at times it becomes inevitable. Oracle 10g provide tools that aid in proactively monitoring database. Server-generated alerts and Automated Database Diagnostic Monitor(AADM) are going to be discussed in another post.
Database and Instance Metrics
Performance measurements are commonly referred as databases metrics which is basically the rate of change of cumulative statistic that need to be monitored. Metrics are indicators of the health of various database targets. Excerpt from Oracle Database 10g New Features by Rampant TechPress
“The Metrics are the statistics derived from base statistics. They represent the delta values between the snapshot periods. Metrics are used by internal components (clients) for system health monitoring, problem detection and self-tuning. There are hundreds of different measurement points available to monitor database system.
Each metric is also associated with a metric name. You can query the view v$metricname to find the names of all the metrics.
SQL> select METRIC_NAME, METRIC_UNIT from v$metricname; "
| Metric | Description |
| Event Class Metrics | Metrics collected on the wait event class level. e.g. DB_TIME_WAITING |
| Event Metrics | Metrics collected on various wait events |
| File Metrics Long Duration | Metrics collected at the file level. e.g. AVERAGE_FILE_WRITE_TIME |
| Service Metrics | Metrics collected at the service level. e.g. CPU_TIME_PER_CALL |
| Session Metrics Short Duration | Metrics collected at the session level. e.g. BLOCKED_USERS |
| System Metrics Short Duration | Metrics collected at the system level |
| Tablespae Metrics | Metrics collected at the Tablespace level e.g. TABLESPACE_PCT_FULL |
Viewing Metrics
The new MMON background process collects database metrics continuously and automatically saves them in the SGA for one hour.It also transfers the memory version of AWR stats to disk on regular basis(in snapshots). Performance metrics are available through
- Data Dictionary
- Dynamic Performance Views
- Optimizer Statistics
Data Dictionary Metrics
Data dictionary metrics provide information about space consumption and object status. Database indexes and stored procedures both need to be VALID to be used.
Unusable indexes always require DBA intervention to restore them to a valid state. Invalid
PL/SQL objects will normally recompile automatically the first time they are called, but
sometimes require DBA intervention in cases where the automatic recompilation fails.
Compiling PL/SQL procedures and Packages
Indexes can become unusable due to normal maintenance operations on tables. Unusable indexes can be rebuild to make them valid.
Rebuilding Indexes
Viewing In-Memory Metrics
All system related metrics saved in memory and they can be viewed using dynamic performance views. Most of the cumulative statistics can be viewed through following:
- V$SYSSTAT
- V$SESSSTAT
- V$SYSMETRIC
- V$SYSMETRIC_HISTORY
Example of few system metrics maintained in V$SYSMETRIC view:
- Buffer Cache Hit Ratio
- CPU Usage Per Sec
- Disk Sort Per Sec
- Host CPU Utilization (%)
- Library Cache Hit Ratio
- SQL Service Response Time
- Shared Pool Free (%)
Viewing Saved Metrics
After every 60 min, MMON places metric info from SGA to disk through AWR snapshot mechanism. The metric data that AWR collects through MMON is permanently stored in DBA_HIST_* views like
- DBA_HIST_SERVICE_NAME
- DBA_HIST_SESSMETRIC_HISTORY
Optimizer Statistics
Optimizer statistics for tables and indexes are stored in the data dictionary. These statistics are not intended to provide real-time data. They provide the optimizer a statistically correct snapshot of data storage and distribution which the optimizer uses to make decisions on how to access data.
Metrics collected include:
- Size of the table or index in database blocks
- Number of rows
- Average row size and chain count (tables only)
- Height and number of deleted leaf rows (indexes only)
As data is inserted, deleted, and modified these values change. The performance impact of
maintaining real-time data distribution statistics would be prohibitive, so these statistics are updated by periodically gathering statistics on tables and indexes.
Mohib Alvi 