Oracle AWR reports

The AWR ( Automatic Workload Repository) Report is an important means of evaluating Oracle performance and discovering problem SQL statements.

awr The principle of reporting is based on Oracle The database's timed mirroring function. By default, oracle The database background process collects the system's current state image at a certain interval (one hour) and is saved in the database. Generate awr When you report, you only need to specify the time period for the analysis (the starting mirror number and the end mirror number) to generate the performance analysis for that time period. awr The time that the image was saved in the database is about one months.  you can also generate a snapshot manually:sql>dbms_workload_repository.create_snapshot ();The steps to generate the awr manually are as follows: 1. Log in to Oracle Database 2, enter the directory where you want to generate the report Cd/test 3, enter the database Sqlplus/as SYSDBA 4, execute build statement sq L> @?/rdbms/admin/awrrpt.sql 5, enter the format of the report sql> HTML or text 6, enter the view days of the Awr snapshot (return all snapshots) sql> 10 7, the output       Into the beginning, the end of the snap_id, it is important to note that the beginning and end of the input snap_id must be in a group (intuitively, between 2 blank lines), otherwise it will be directly error. Sql> sql> 18 8, enter the name of the report (enter the trial default name) There are also reports of ash and ADDMThe main difference between awr and Ash is that AWR is flat, comprehensive, ash is stereoscopic, more focused on session event tracking, and because the event wait for a database with large volumes of traffic is constantly changing, awr is likely to be monitored, in order to compensate for this deficiency, Ash is able to track the event of the session. the difference between ash and ADDM is that the ADDM is based on the analysis of the current state of the database, providing guidance on the existing problems, it can be said that ASH,ADDM is the addition of AWR, AWR comprehensively collects the state of the databases, but ash/ ADDM is focused on analyzing the collected data and providing some useful advice.  

sql> @?/dbms/admin/addmrpt.sql

I. Generating an AWR report

[Ractest@sun880-1/U01/App/Oracle/Orahome/Rdbms/admin]$ CD$ORACLE _home/Rdbms/Admin
[Ractest@sun880-1/U01/App/Oracle/Orahome/Rdbms/admin]$ Sqlplus/As Sysdba
Sql*Plus:Release10.2.0.4.0-Production on Sun Jan2720:47:382008
Copyright (c)1982,2007,Oracle.All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release10.2.0.40 - 64bit production
With the partitioning,< Span style= "COLOR: #000000" > real application clusters, olap, data mining
And real application testing options
Sql>  @awrrpt
...
// Generate reports based on Oracle prompts Span style= "COLOR: #000000" > ...

Note : The report is generated in the $oracle_home/rdbms/admin/directory.

Two. Modify the default settings for AWR

half an hour to collect information to save 3 days.
SQL>exec dbms_workload_repository. Modify_snapshot_settings (Interval=Retention =3*60 );

Three. Turn off AWR automatic collection

SQL>exec dbms_workload_repository. Modify_snapshot_settings (interval=0,retention =*);

Note : 10g default is automatically open AWR information collection, will have a certain impact on the system (very small); If you want to turn off AWR information collection, simply set the interval parameter to 0. However, the AWR report cannot be generated after interval is set to 0.

Lab Environment:

Operating system:Win7 flagship version 64bit

Database:Oracle 11G R2 64bit

Step 1: Login to sqlplus

C:\users\administrator>sqlplus/as SYSDBA

Step 2: Run the script

Sql> @d:\soft\oracle\product\11.2.0\dbhome_1\rdbms\admin\awrrpt.sql

Step 3: Set up report acquisition conditions

Set the record for a few days ago, if set to 1, from today's :00, if set to 2, starting from yesterday 00:00, the default value is 7, the following uses the default value, carriage return to the next step.

Set the start ID of the snapshot as follows:

In particular, the name of the AWR can be self-named, if the default value, click Enter directly.

For example, theAWR report is generated successfully.

To the specified path (typically generated reports are stored by default in the System user directory) to find the AWR report to view.

At this point, the introduction of the generated AWR report is complete (Linux method is the same)

Oracle AWR reports