Lesson 2 | What to monitor? |
Objective | List 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:
-
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.
-
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.
-
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.
-
Unified Auditing
- Monitor audit trail growth and ensure compliance with auditing policies.
- Check for unauthorized access attempts or unusual patterns in user activity logs.
-
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).
-
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.
-
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.
-
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.
-
Scheduler Jobs
- Monitor Oracle Scheduler for job failures, especially those critical for backups or maintenance tasks.
- Track job history for performance and trends.
-
Automatic Workload Repository (AWR) and ADDM Reports
- Regularly review AWR snapshots for performance bottlenecks.
- Analyze findings from the Automatic Database Diagnostic Monitor (ADDM).
-
Flashback Features
- Monitor Flashback Recovery Area usage and ensure it has sufficient space.
- Verify that Flashback logs are being generated and used as expected.
-
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.
-
Partitioned Tables and Indexes
- Monitor partition maintenance operations and space usage.
- Check for unbalanced partitions or issues with query performance on partitioned tables.
-
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.
-
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?
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.