Introduction
Are you drowning in AWR data, struggling to make sense of it all? I think the metaphor of taming the AWR tsunami is fitting, because, while Oracle's Automatic Workload Repository (AWR) is a treasure trove of performance metrics, it can be daunting to jump in and begin exploring this data. Thus, having some guidance on harnessing its power can be extremely helpful to build your skill, know-how, and experience in making the best use of this information. I have been going deep into Oracle database performance metrics found in the AWR for well over a decade and have used the AWR data for thousands of performance cases over the years. In this blog post series, I’ll explore common and more advanced techniques for evaluating Oracle DB performance using AWR data, helping you uncover hidden insights and optimize your database's performance and learn more about the behavior under stress of both your applications and the database itself.
Basic Places to Look in AWR
The AWR contains several key views that can provide valuable insights into your database's performance. These include dba_hist_active_sess_history, which records information about active sessions; dba_hist_sqlstat, which contains SQL statistics; and more. By querying these views effectively, you can detect blocking locks, find top PGA or TEMP tablespace consumers, and identify SQL execution plan flips, among other things.
Obscure Sources of Performance Metrics
In addition to the basic views, the AWR also includes several more obscure sources of performance metrics (that is, there is not a whole lot of documentation on these metrics, but since I’ve been working with these for well over a decade, I’ve learned how to use these metrics as well for performance diagnostics). Some of these more obscure sources of metrics include dba_hist_pgastat, which tracks information about the Program Global Area (PGA); dba_hist_osstat, which provides operating system statistics; dba_hist_sys_time_model, which shows time model statistics; and more. By tapping into these sources, you can gain deeper insights into your database's performance and scalability.
Why These Sources Are Relevant
Each of these sources of performance metrics plays a crucial role in Oracle performance and scalability assessments. For example, dba_hist_pgastat can help you identify memory-related issues that might be affecting performance, while dba_hist_osstat can provide insights into how your database is interacting with the operating system. By understanding and querying these sources effectively, you can gain a comprehensive understanding of your database's performance and make informed decisions about how to optimize it. As such, mastering the art of AWR analysis is key to effectively evaluating Oracle DB performance. By using clever queries and tapping into both basic and obscure sources of performance metrics, you can tame the AWR tsunami and unlock the full potential of your database.
Where to look in the AWR for relevant metrics.
What follows is a brief description of the content and usage of many AWR metric sources that I have found invaluable in the thousands of cases of Oracle database performance assessments that I have been involved with. These include:
· dba_hist_active_sess_history
· dba_hist_sqlstat
· dba_hist_pgastat
· dba_hist_osstat
· dba_hist_sys_time_model
· dba_hist_ sysmetric_sumary
· dba_hist_sysstat
· dba_hist_system_event
· dba_hist_bg_event
· dba_hist_event_histogram
· dba_hist_filestatxs
· dba_hist_seg_stat
· dba_hist_service_stat
· dba_hist_tbspc_space_usage
Of course, the AWR has hundreds of views containing lots of data, however, these are the key sources that I go to on a regular basis. Theoretically, you could instrument all the metrics and put them through anomaly detection such as I describe in my book on the topic, but , in my experience, the practicality/usefullness of instrumenting them all marginal/negligible.
· dba_hist_active_sess_history: This view contains historical information about active sessions in the database. It can be used to analyze workload patterns, identify resource-intensive queries, and detect blocking and contention issues.
· dba_hist_sqlstat: This view contains historical SQL statistics, including metrics like CPU time, elapsed time, and buffer gets for each SQL statement. It helps identify top SQL statements by various performance metrics, allowing for query optimization and workload tuning.
· dba_hist_pgastat: This view provides historical statistics about the Program Global Area (PGA), which is a memory region used by Oracle for sorts, hash joins, and other operations. Monitoring PGA usage can help identify memory-related performance issues and tune SQL operations that use PGA.
· dba_hist_osstat: This view contains historical operating system statistics, such as CPU and load, memory utilization (e.g. swapping), disk I/O, and network traffic. It provides insights into how the database interacts with the operating system and helps in diagnosing performance issues related to resource contention.
· dba_hist_sys_time_model: This view contains historical time model statistics, which represent the database's consumption of various types of system resources (e.g., DB CPU, DB time, PL/SQL execution elapsed time, hard parse elapsed time). It helps in understanding the resource consumption pattern of the database and identifying bottlenecks.
· dba_hist_sysmetric_summary: This view provides a summary of various system metrics collected by the database. It includes information on CPU utilization, memory usage, and I/O activity. It helps in monitoring the overall health of the database and identifying performance trends over time.
· dba_hist_sysstat: This view contains historical system statistics, such as the number of physical reads and writes, logons, rollbacks and commits, undo, redo, and the effects of compression. It helps in understanding the overall workload on the database and can be used to identify performance trends and anomalies.
· dba_hist_system_event: This view contains historical information about wait events experienced by the database. It helps in identifying performance bottlenecks by showing where the database is spending the most time waiting.
· dba_hist_bg_event: This view contains historical information about background process events in the database. It helps in monitoring the performance of background processes and identifying any issues that may impact overall database performance.
· dba_hist_event_histogram: This view contains histograms of wait events, showing the distribution of event durations. It helps in understanding the nature of wait events and identifying outliers that may indicate performance issues.
· dba_hist_seg_stat: This view contains historical segment-level statistics, showing a number of metric including: the amount of space used, chaining, ITL and buffer busy waits, physical reads/writes, and full table scans. It helps in monitoring important activity at the segment level to help in understanding the performance profile of the application workloads experienced on the database.
· dba_hist_service_stat: This view contains approximately 28 historical statistics for each database service, showing metrics like DB CPU, DB time, db block changes, parsing, I/O activity, logons, commits, and rollbacks. It helps in monitoring service performance by easily identifying resource usage at the service level.
· dba_hist_tbspc_space_usage: This view contains historical tablespace space usage statistics, showing the amount of space used and allocated for each tablespace. It helps in monitoring tablespace growth and identifying potential space-related issues.
Overall, these AWR sources provide a comprehensive view of the database's performance and resource utilization over time, helping database administrators diagnose and resolve performance issues, optimize resource usage, and improve overall database efficiency. In future posts, I’ll go through each of these to show how I use these views for evaluating the performance and scalability of an Oracle database workload.