Lesson 17

You can monitor several statistics on a regular basis to ensure that Oracle is operating efficiently, that it has enough disk space available, and that it has enough memory. In this module, you learned about some of the more important things to look at. Now that you've completed this module, you should be able to do the following.
In Oracle 19c, a Database Administrator (DBA) must perform the following modern tasks to monitor database statistics efficiently. These tasks leverage Oracle's advanced features and tools, focusing on current best practices:

1. Monitor System Performance with AWR (Automatic Workload Repository)
   • Generate AWR reports to analyze overall database performance.
     • DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT to create manual snapshots.
     • @?/rdbms/admin/awrrpt.sql to generate AWR reports.
   • Analyze key metrics such as CPU usage, wait events, and SQL execution statistics.
2. Monitor Active Sessions with ASH (Active Session History)
   • Use ASH Reports to identify real-time performance issues.
     • Run @?/rdbms/admin/ashrpt.sql to generate ASH reports.
   • Query views like V$ACTIVE_SESSION_HISTORY for detailed session-level statistics.
3. Use Performance Views for Real-Time Monitoring
   • Buffer Cache Monitoring:
     • Query V$DB_CACHE_ADVICE and V$BUFFER_POOL_STATISTICS to analyze buffer cache usage.
   • Shared Pool Monitoring:
     • Query V$LIBRARYCACHE and V$SHARED_POOL_ADVICE to check shared pool performance.
   • Redo Log Buffer Contention:
     • Check redo log statistics using V$LOG_BUFFER and V$LOGFILE.
4. Space Management and Storage Monitoring
   • Monitor free space in tablespaces:
     • Use DBA_FREE_SPACE, DBA_TABLESPACES, and DBA_DATA_FILES.
   • Identify segments nearing maximum size:
     • Query DBA_SEGMENTS and DBA_EXTENTS.
   • Use Oracle Enterprise Manager (OEM) for graphical insights into storage usage.
5. Monitor SQL Execution Statistics
   • Use SQL Monitoring in OEM or manually query:
     • V$SQLAREA and V$SQLSTATS to find high-resource SQL statements.
   • Enable Adaptive Query Optimization features (e.g., SQL Plan Baselines).
6. Monitor Wait Events
   • Identify database bottlenecks using wait event views:
     • Query V$SYSTEM_EVENT, V$SESSION_WAIT, and V$EVENT_HISTOGRAM.
   • Focus on key wait events like I/O contention, locking, and network delays.
7. Monitor Disk I/O and Resource Usage
   • Query V$IOSTAT_FILE and V$TEMPSTAT to monitor disk I/O distribution.
   • Use V$RESOURCE_LIMIT to ensure adequate resources for sessions, processes, and memory.
8. Monitor Undo and Temporary Tablespaces
   • Check undo tablespace usage:
     • Query DBA_UNDO_EXTENTS and V$UNDOSTAT.
   • Monitor temporary tablespace usage:
     • Query DBA_TEMP_FILES and V$TEMPSEG_USAGE.
9. Generate ADDM (Automatic Database Diagnostic Monitor) Reports
   • Run ADDM for detailed performance diagnostics.
     • Execute DBMS_ADDM.ANALYZE_DB to initiate ADDM analysis.
   • View recommendations in Oracle Enterprise Manager.
10. Monitor Database Health with Oracle Enterprise Manager (OEM)
    • Use OEM dashboards to monitor:
      • Real-time performance.
      • Space usage and growth trends.
      • SQL and I/O performance.
    • Set alerts for thresholds like CPU usage, tablespace fullness, and blocking sessions.
11. Monitor Cluster-Wide Statistics in RAC Environments
    • Use GV$ views (e.g., GV$SESSION, GV$SQLAREA) to monitor statistics across all nodes in a cluster.
12. Licensing and Feature Usage Monitoring
    • Check feature usage statistics:
      • Query DBA_FEATURE_USAGE_STATISTICS to monitor the use of licensed features.
    • Review DBA_LICENSE for license usage metrics.
13. Monitor Data Guard or High Availability Configurations
    • Query V$DATAGUARD_STATS for Data Guard replication performance.
    • Use Oracle Data Guard Broker for automated monitoring.
14. Backup and Recovery Monitoring
    • Monitor RMAN backups using:
      • V$RMAN_BACKUP_JOB_DETAILS and V$BACKUP_FILES.
    • Check recovery point objectives (RPO) and recovery status.
15. Leverage Real-Time SQL Monitoring
    • Use DBMS_SQLTUNE.REPORT_SQL_MONITOR to analyze high-impact SQL in real-time.

By focusing on these tasks, a DBA can effectively monitor and manage statistics in Oracle 19c, leveraging modern tools and practices for optimal database performance and reliability.

Ongoing monitoring is an important part of managing a database. By becoming familiar with your database's operating and growth characteristics, you can keep your database purring like a well-maintained Ferrari.

Click the Quiz link below to answer a few questions about disk I/O and redo contention.
Diskio Redo Contention - Quiz