In Oracle 19c, the Automatic Workload Repository (AWR) is essential for managing performance in Real Application Clusters (RAC). AWR collects and analyzes performance data, helping to maintain database efficiency and reliability. This blog will explore how to use AWR in RAC, focusing on data collection, analysis, and performance optimization.
Understanding AWR in RAC
AWR in RAC is a repository that collects, processes, and maintains performance statistics used for problem detection and tuning. It automatically gathers data from all nodes in a RAC environment, ensuring comprehensive coverage of the system’s performance. This data includes information about wait events, system statistics, and SQL performance, which are critical for understanding and optimizing database operations.
Data Collection and Storage
The primary function of the Automatic Workload Repository in RAC is to collect performance data at regular intervals. This data is stored in the AWR tables within the SYS schema. The repository captures snapshots of the database’s state, which include various performance metrics and statistics. These snapshots are typically taken every hour but can be customized based on the specific needs of the database environment.
Example: Configuring Snapshot Interval
BEGIN
DBMS_WORKLOAD_REPOSITORY.modify_snapshot_settings(
interval => 30, -- Interval in minutes
retention => 1440 -- Retention in minutes (1 day)
);
END;
/
This code snippet configures the AWR to take snapshots every 30 minutes and retain the data for one day.
Generating AWR Reports
AWR reports can be generated in a RAC database using two scripts: awrrpt.sql and awrrpti.sql.
- awrrpt.sql: This script generates a single report for the database across all nodes (i.e., for all instances) for a particular snapshot range.
- awrrpti.sql: This script generates a report for a particular instance. For a 2-node RAC database, there will be two reports (one for each instance).
Example: Generating an AWR Report for All Instances
@?/rdbms/admin/awrrpt.sql
This script will prompt you for the start and end snapshot IDs and then generate a report for the entire RAC database.
Example: Generating an AWR Report for a Specific Instance
@?/rdbms/admin/awrrpti.sql
This script will prompt you for the instance number and snapshot range, generating a report for the specified instance.
Performance Analysis with AWR
Using AWR data, database administrators can perform detailed performance analysis. The reports provide insights into various performance metrics, helping identify bottlenecks and areas for improvement.
AWR in RAC – Analyzing Wait Events
AWR reports include sections on wait events, which are crucial for diagnosing performance issues. By examining the wait events, administrators can determine if the system is experiencing contention and take appropriate action to resolve it.
Example: Interpreting Wait Events
SELECT event, total_waits, time_waited
FROM dba_hist_system_event
WHERE snap_id BETWEEN :start_snap AND :end_snap
AND event NOT LIKE 'SQL*Net message%';
This query retrieves wait event data for a specified snapshot range, excluding idle events.
SQL Performance Analysis
AWR also tracks SQL execution statistics, enabling administrators to identify poorly performing SQL statements. By tuning these statements, significant performance gains can be achieved.
Example: Identifying High-Load SQL Statements
SELECT sql_id, elapsed_time, cpu_time, executions
FROM dba_hist_sqlstat
WHERE snap_id BETWEEN :start_snap AND :end_snap
ORDER BY elapsed_time DESC;
This query lists SQL statements by elapsed time, helping pinpoint which statements consume the most resources.
Using AWR in RAC Data for System Optimization
AWR in RAC data is not only useful for identifying issues but also for proactive system optimization. By regularly reviewing AWR reports, administrators can spot trends and make adjustments before problems escalate.
Analyzing System Load
SELECT snap_id, load_average
FROM dba_hist_sysmetric_summary
WHERE metric_name = 'Load Average'
AND snap_id BETWEEN :start_snap AND :end_snap;
This query helps in understanding system load over time, allowing for better capacity planning.
Proactive Tuning with AWR in RAC
Using the insights gained from AWR, administrators can implement changes to optimize system performance. This might include adjusting memory allocation, tuning SQL queries, or modifying indexing strategies.
Example: Tuning Memory Allocation
ALTER SYSTEM SET sga_target = 4G SCOPE=BOTH;
ALTER SYSTEM SET pga_aggregate_target = 2G SCOPE=BOTH;
This example shows how to adjust the memory allocation based on AWR findings to enhance performance.
Conclusion
Utilizing the Automatic Workload Repository in RAC environments is essential for maintaining optimal performance and stability. By regularly generating and analyzing AWR reports, database administrators can proactively address performance issues, ensuring smooth and efficient operation of Oracle RAC databases. This comprehensive approach to performance monitoring and tuning helps in achieving long-term reliability and efficiency.
See more on Oracle’s website!
Be Oracle RAC and GRID Certified Professional, this world is full of opportunities for qualified DBAs!
