How to generate an AWR report for a specified SQL statement

Source: Internet
Author: User

1. Generate a statistical report for the specified SQL statement
To compare databases with multiple instances, use the $ Oracle_HOME/rdbms/admin/awrddrpi. SQL script. The operation of this script is basically the same as that of a single instance. We will not describe it here. If you are interested, you can test it on your own.

2. Generate a statistical report for the specified SQL statement
This statistic is used to analyze a specified SQL statement through awrsqrpt. the SQL script and awr can generate the execution plan, consumed resources, and other information of the specified SQL statement (the SQL statement that has been executed), which helps the DBA to optimize the SQL statement.
[Oracle @ even ~] $ Sqlplus/as sysdba

SQL * Plus: Release on Sun Dec 23 11:39:53 2012

Copyright (c) 1982,200 5, Oracle. All rights reserved.

Oracle Database 10g Enterprise Edition Release
With the Partitioning, Oracle Label Security, OLAP and Data Mining options

SQL> exec dbms_workload_repository.create_snapshot;

PL/SQL procedure successfully completed.

SQL> @ $ ORACLE_HOME/rdbms/admin/awrsqrpt. SQL

Current Instance

DB Id DB Name Inst Num Instance
2100083002 TEST 1 test

Specify the Report Type
Wocould you like an HTML report, or a plain text report?
Enter 'html' for an html report, or 'text' for plain text
Ults to 'html'
Enter value for report_type: html

Type Specified: html

Instances in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~~~~

DB Id Inst Num DB Name Instance Host
* 2100083002 1 TEST even. oracle.

Using 2100083002 for database Id
Using 1 for instance number

Specify the number of days of snapshots to choose from
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~~~~~~~~~~~~~~
Entering the number of days (n) will result in the most recent
(N) days of snapshots being listed. Pressing <return>
Specifying a number lists all completed snapshots.

Enter value for num_days: 1

Listing the last day's Completed Snapshots

Instance DB Name Snap Id Snap Started Level
Test TEST 34 23 Dec 2012 1
35 23 Dec 2012 1
36 23 Dec 2012 1


Specify the Begin and End Snapshot Ids
Enter value for begin_snap: 34
Begin Snapshot Id specified: 34

Enter value for end_snap: 36
End Snapshot Id specified: 36


Specify the SQL Id
Note: You need to specify the SQL _ID for analysis. You may want to ask where the SQL ID to be analyzed comes from? In general, SQL _ID can be used through V $ SQL (and other related views) or AWR/STATSPACK or other tools.
Enter value for SQL _id: 4vsh055snc3du
SQL ID specified: 4vsh055snc3du

Specify the Report Name
The default report file name is awrsqlrpt_000034_36.html. To use this name,
Press <return> to continue, otherwise enter an alternative.

Enter value for report_name:/home/oracle/awr_20121231.html

Using the report name/home/oracle/awr_20121231.html

<HTML> <HEAD> <TITLE> awr SQL Report </TITLE> <style type = "text/css"> body. awr {font: bold 10pt Arial, Helvetica, Geneva, sans-serif; color: black;
Report written to/home/oracle/awr_20121231.html

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.