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