SQL AWR Report: Automatic Workload Repository
Introduction
When people want their databases to run faster and smoother, they often turn to a handy tool called the Automatic Workload Repository (AWR)
in Oracle Database. AWR keeps track of a lot of information about how the database is performing, so users can quickly figure out what's causing any slowdowns and fix them.
One of the most crucial things AWR does is generate reports, and among them, the ones about SQL AWR Reports are really important.
These reports help identify and fix problems with specific pieces of code that interact with the database, making everything run more smoothly.
In this blog post, we'll dive into the details of SQL AWR reports. We'll explain why they're so important and show you how to create
and understand them.
What is an SQL AWR Report?
An SQL AWR report, as the name suggests, focuses specifically on the performance of SQL statements executed within an Oracle Database instance. It provides detailed insights
into the execution statistics, resource consumption, and potential bottlenecks associated with individual SQL statements over a specified period.
Generating AWR Reports
Before we dive into the details of interpreting an SQL AWR report, let's first understand how to generate one. Oracle provides SQL scripts that can be executed using SQL*Plus
to generate both HTML and text-based SQL AWR reports.
Find the Snapshot IDs, DatabaseID, and Instance Number
Before generating the AWR report, you need to identify the snapshot IDs for the period you are interested in. Run the following query to list available snapshots:
SQL> select
snap_id,
dbid,
instance_number,
begin_interval_time,
end_interval_time
from
dba_hist_snapshot
order by
snap_id desc;
SNAP_ID DBID INSTANCE_NUMBER BEGIN_INTERVAL_TIME END_INTERVAL_TIME
---------- ---------- --------------- ----------------------------- ----------------------------
3 3358061257 1 01-JUN-24 04.47.23.000 AM 01-JUN-24 04.58.22.669 AM
2 3358061257 1 01-JUN-24 03.58.37.000 AM 01-JUN-24 04.09.38.007 AM
1 3358061257 1 31-MAY-24 11.15.18.000 AM 31-MAY-24 11.58.51.326 AM
SQL>
Execute the Script
Save the following SQL as awrsqrpt_sql.sql and use SQL*Plus to execute the script with the appropriate arguments
(DBID, instance ID, begin snapshot ID, end snapshot ID and SQLID).
For example:
--DBID: 3358061257
--instance ID:1
--begin snapshot ID:2
--end snapshot ID:3
--SQLID: dbdxr98c0pqsq
sqlplus system@dbdocs awrsqrpt_sql.sql 3358061257 1 2 3 dbdxr98c0pqsq
Below is a SQL*Plus script that can be utilized to generate an SQL AWR report. Save the following SQL as awrsqrpt_sql.sql :
-- SQL*Plus script to generate an HTML and text-based AWR SQL report.
-- Usage: sqlplus username@database @awrsqrpt_sql.sql dbid inst bid eid sqlid
set feedback off
set heading off
set linesize 10000
set pagesize 0
set termout off
set trimspool on
set verify off
define dbid=&1
define inst=&2
define bid=&3
define eid=&4
define sqlid=&5
column awr_html_file new_value awr_html_file noprint
column awr_text_file new_value awr_text_file noprint
select 'awr_sql_report_' || &&dbid || '_' || &&inst || '_' || &&bid || '_' || &&eid || '_' || '&&sqlid' || '.html' awr_html_file from dual;
select 'awr_sql_report_' || &&dbid || '_' || &&inst || '_' || &&bid || '_' || &&eid || '_' || '&&sqlid' || '.txt' awr_text_file from dual;
set termout on
prompt Generating HTML AWR SQL report
set termout off
spool &awr_html_file
select
output
from
table(
dbms_workload_repository.awr_sql_report_html(
l_dbid => &&dbid,
l_inst_num => &&inst,
l_bid => &&bid,
l_eid => &&eid,
l_sqlid => '&&sqlid'
)
);
spool off
set termout on
prompt Generating text AWR SQL report
set termout off
spool &awr_text_file
select
output
from
table(
dbms_workload_repository.awr_sql_report_text(
l_dbid => &&dbid,
l_inst_num => &&inst,
l_bid => &&bid,
l_eid => &&eid,
l_sqlid => '&&sqlid'
)
);
spool off
quit
HTML and Text Reports
The script generates two files: an HTML report and a text report in the same location where the awrsqrpt_sql.sql file is located.
The file names are in the format awr_report_dbid_inst_bid_eid_sqlid.html and
awr_report_dbid_inst_bid_eid_sqlid.txt.
This script takes several arguments including dbid (Database ID), inst (Instance ID), bid (Begin snapshot ID), eid (End snapshot ID), and sqlid (SQL ID).
It utilizes Oracle's built-in DBMS_WORKLOAD_REPOSITORY package to generate both HTML and text-based SQL AWR reports.
SQL AWR Report Analysis
Once the SQL AWR report is generated, it's crucial to understand how to interpret the information it provides. Here are some key components to focus on:
- SQL Statement Details: The report typically includes details about the SQL statement under analysis, such as its
SQL ID, SQL Text, and Execution Plan.
- Performance Metrics: Various performance metrics are provided, including
CPU Time, Elapsed Time, Buffer Gets, Disk Reads, and Rows Processed. These metrics offer insights into the resource consumption and efficiency of the SQL statement.
- Execution Statistics: Detailed execution statistics help in understanding how the SQL statement performed over the specified period. This includes metrics like
Executions, Parses, and Fetches.
- Wait Events: If the SQL statement encountered any wait events during execution, they are usually listed in the report along with their respective durations and counts. Identifying significant wait events can help pinpoint performance bottlenecks.
- Recommendations: Some SQL AWR reports may include recommendations for optimizing the SQL statement's performance based on the observed metrics and statistics. These recommendations can be invaluable for tuning SQL queries and improving overall database performance.
Conclusion
SQL AWR [Automatic Workload Repository] reports are indispensable tools for diagnosing and optimizing SQL-related performance issues within Oracle Databases. By generating and interpreting these reports
effectively, database administrators and developers can identify inefficiencies, address bottlenecks, and enhance the overall performance and scalability of their database
systems. Utilizing the insights gained from SQL AWR reports, organizations can ensure optimal utilization of resources and deliver consistent and reliable performance
to their users.
Related content