SQL AWR Report: Automatic Workload Repository

Introduction
What is an SQL AWR Report?
Generating an SQL AWR Report
SQL AWR Report Analysis
Conclusion

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 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



Rate Your Experience

: 89 : 1


Last updated in July, 2024

Online Tests
Read more

Cloud Technology
Read more

Oracle Database
Read more

MSSQL Database
Read more

PostGres Database
Read more

Linux
Read more

ASP/C#
Read more

Quick Access

All Rights Reserved @2024 | www.dbdocs.net | Privacy Policy | Disclaimer | Cookie Policy