Database Monitoring   «Prev  Next»
Lesson 2 What to monitor?
ObjectiveList terms to monitor on a regular basis.

What to monitor

When managing an Oracle database, you should be monitoring a variety of items on a regular basis. The table below lists the items and the reasons they need to be monitored:
Item What to monitor
Database alert log This is a text file to which Oracle writes serious error messages. Oracle also generates log entries for significant events such as database startup and shutdown.
License compliance Most Oracle licenses specify a maximum number of users. Are you exceeding that number?
Disk space How much are you using currently? How much free space do you have? When will you run out of space?
Buffer cache hit ratio Do you have enough memory allocated to the database buffer cache?
Dictionary cache and library cache hit ratios Do you have enough memory allocated to the shared pool?
File I/O statistics Is your I/O load evenly distributed across multiple disks?
Redo logs Do you have enough redo log files so that users aren't forced to wait while one is archived? Are you experiencing buffer contention?

Items to monitor for an Oracle 12c Database

When managing an Oracle 12c database, you should also monitor the following additional elements to ensure optimal performance, availability, and compliance:
  1. Automatic Storage Management (ASM)
    • Monitor the status of ASM instances and disk groups.
    • Ensure disk space availability in ASM disk groups.
    • Check for rebalance operations and any ASM-related alerts.
  2. Pluggable Database (PDB) and Container Database (CDB) Architecture
    • CDB Health:
      • Monitor the root container for resource contention or issues.
    • PDB State:
      • Ensure PDBs are in the correct state (OPEN/READ ONLY/STARTED).
      • Check resource usage and quota limits per PDB.
    • PDB-Specific Performance:
      • Track SQL execution, sessions, and resource consumption within each PDB.
  3. Resource Manager (12c Enhancements)
    • Monitor Resource Manager plans to ensure resource allocation among PDBs aligns with business requirements.
    • Verify that inter-PDB resource utilization adheres to predefined policies.
  4. Unified Auditing
    • Monitor audit trail growth and ensure compliance with auditing policies.
    • Check for unauthorized access attempts or unusual patterns in user activity logs.
  5. Adaptive Query Optimization[1]
    • Track and analyze execution plans that use adaptive query plans.
    • Monitor automatic query performance adjustments (e.g., statistics feedback and runtime adaptations).
  6. In-Memory Column Store
    • Monitor memory allocation and usage for the in-memory column store feature.
    • Analyze performance improvements and any issues with in-memory queries.
  7. Data Guard and High Availability
    • Monitor Data Guard configuration for standby database synchronization and latency.
    • Check for any gaps in archived logs or issues in redo transport services.
    • Ensure failover readiness.
  8. Real Application Clusters (RAC) (if applicable)
    • Monitor interconnect performance and communication between RAC nodes.
    • Ensure load balancing among nodes.
    • Track Clusterware and Grid Infrastructure health.
  9. Scheduler Jobs
    • Monitor Oracle Scheduler for job failures, especially those critical for backups or maintenance tasks.
    • Track job history for performance and trends.
  10. Automatic Workload Repository (AWR) and ADDM Reports
    • Regularly review AWR snapshots for performance bottlenecks.
    • Analyze findings from the Automatic Database Diagnostic Monitor (ADDM).
  11. Flashback Features
    • Monitor Flashback Recovery Area usage and ensure it has sufficient space.
    • Verify that Flashback logs are being generated and used as expected.
  12. SQL Plan Baselines
    • Check for changes in SQL plan baselines that could impact performance.
    • Ensure that critical SQL queries are using the optimal execution plans.
  13. Partitioned Tables and Indexes
    • Monitor partition maintenance operations and space usage.
    • Check for unbalanced partitions or issues with query performance on partitioned tables.
  14. Security and Patching
    • Monitor for critical patch updates (CPUs) and apply them timely.
    • Track security configuration changes, especially for Transparent Data Encryption (TDE) and network encryption.
  15. Multitenancy-Specific Parameters
    • Monitor multitenant parameters, such as PDB_OS_CREDENTIAL, to ensure compliance and optimal operation.
By monitoring these additional elements specific to Oracle 12c features, you can ensure your database runs efficiently and adheres to modern best practices.

What are Dynamic Performance Views in Oracle?

In Oracle database, dynamic performance views (also known as "v$ views") are a set of views that provide real-time information about the current state and performance of the database. These views are built on top of memory structures and background processes in the database, and they are updated continuously as the database changes.
There are many different dynamic performance views in Oracle, and they provide a wide range of information about the database. Some examples of information that you can get from dynamic performance views include:
  1. Current sessions and active transactions
  2. Wait events and locks
  3. Memory usage and other resource utilization statistics
  4. Performance statistics for various database components, such as the buffer cache, the redo log buffer, and the shared pool
Dynamic performance views are an important tool for database administrators, as they allow you to monitor the performance and health of the database in real-time. They are often used in conjunction with other monitoring tools and techniques, such as the Oracle Enterprise Manager console or custom scripts and tools.

Dynamic Performance Views

Monitoring many of these items involves selecting data from Oracle's dynamic performance views. Dynamic performance views are the ones owned by the user named SYS and have names that begin with V$. In fact, DBAs commonly refer to these as the "V$ views" (vee-dollar). Oracle implements a large number of V$ views, and the information that they return constantly changes to reflect changes in the state of the database over time.
In the next lesson, I will describe the contents of the alert log.

[1] Adaptive Query Optimization: Adaptive Query Optimization is a feature in Oracle Database that allows the query optimizer to make run-time adjustments to execution plans based on the actual data encountered during execution. This helps to improve query performance, especially in situations where the initial estimates used to generate the plan were inaccurate.

SEMrush Software 2SEMrush Software Banner 2