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 10.2.0.1.0-Production on Sun Dec 23 11:39:53 2012

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


Connected:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0-Production
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.
Com

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

Snap
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;
......
......
.....
</BODY> </HTML>
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: info-contact@alibabacloud.com 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.