Reprint: AWR Introduction using

reprinted from Http://www.cnblogs.com/lanzi/archive/2011/03/07/1975096.html Automated Workload Library (Automatic Workload repository,awr)

The origin of Awr:
Oracle before 10g: The user's connection will generate a session, the current session record is saved in V$session, and a waiting session is copied to the v$session_wait. When the connection
After disconnection, the original connection information will be deleted in V$session and v$session_wait, and oracle10g and later leave the v$session_wait of this information, and more
The V$active_session_history (ASH) view, which records the last 10 wait events in v$session_wait for each active session.

The sample data for ash is stored in memory. The amount of memory allocated to Ash is limited, and when the allocated space fills up, the old records are overwritten, and after the database restarts, all of these
Ash information will disappear. This is not possible for long-term detection of Oracle performance. In oracle10g, the method of permanently preserving ash information is provided, which is awr.

Since it is time-consuming and space-intensive to save all the information in Ash, AWR takes a policy of sampling v$active_session_history every hour and saving the information to disk
and is retained for 7 days, and the old record is overwritten after 7 days. These sampling information is saved in the view wrh$_active_session_history. And this sampling frequency (1 hours) and retention time
(7 days) can be adjusted according to the actual situation, which gives DBAs more effective system monitoring tools.

1. The enablement of Awr

By default, Oracle enables database statistics to collect this feature (that is, enable AWR). Whether the AWR is enabled is controlled by the initialization parameter statistics_level. Through the show parameter command

You can display the current value of Statistics_level:

sql> SHOW PARAMETER Statistics_level

The execution result of the SQL statement is:
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
Statistics_level string Typical

If the value of Statistics_level is typical or all, AWR is enabled, and if the value of Statistics_level is basic, AWR is disabled.

Initialization Parameters Statistics_level Introduction:

The behavior of AWR is affected by the parameter statistics_level. This parameter has a value of three:

*basic:awr statistics are computed and derived values are closed. Only a small amount of database statistics is collected.

*typical: Default value. Only part of the statistical collection. They represent the behavior of the typical monitoring Oracle database that is needed.

*all: All possible statistics are captured. And there are some information about the operating system. This level of capture should be used only in rare cases, such as when you want more SQL diagnostic information.


2. Snapshot (SNAPSHOT)

Every hour, the memory monitoring process (Mmon) automatically collects statistics and stores the information in the load library, where a single sample is a snapshot. In order to save space, the collected data is

Automatically clears after 7 days. The frequency and retention time of the snapshot can be modified by the user.

--View the frequency and retention time of the snapshot (the default is to sample every 1 hours and the sampling information is retained for 7 days)
SELECT * from Dba_hist_wr_control;
Select DBID, Snap_interval, Snapint_num, RETENTION from Wrm$_wr_control;

--Change the frequency and retention time of the snapshot (in minutes)
exec dbms_workload_repository.modify_snapshot_settings (interval=>60, retention=>7*24*60);

Users can also manually sample (generate snapshots manually) using the following command:
BEGIN
Dbms_workload_repository. Create_snapshot ();
END;

Manually delete a snapshot of a specified range
Begin
Dbms_workload_repository.drop_snapshot_range (low_snap_id = 3965, high_snap_id = 3966, dbid = 3437504306);
End

--See how many snapshots there are
Select COUNT (1) from Wrh$_active_session_history;
Select COUNT (1) from Dba_hist_active_sess_history;

By querying the view dba_hist_snapshot, you can know which snapshots are generated in the system.
SELECT * from Dba_hist_snapshot;

3. Where to store the sampled data

These sampled data are stored in the Sysaux table space and are named in the format wrm$_* and wrh$_*. The previous type stores metadata information, such as checked databases and captured snapshots,
The latter type holds the actual collected statistics.
Select table_name from dba_tables where table_name like ' wrm$% ';

table_name
-----------------------
Wrm$_wr_control
Wrm$_snap_error
Wrm$_snapshot
Wrm$_database_instance
Wrm$_baseline

When the Sysaux table space is full, AWR automatically overwrites the old information and logs a message in the warning log:
Ora-1688:unable to extend table SYS. Wrh$_active_session_history partition wrh$_active_3533490838_1522 by Tablespace Sysaux

Select table_name from dba_tables where table_name like ' wrh$% ';

4. Set up baselines
4.1 Creating baselines
A baseline (baseline) is a mechanism so that you can mark the snapshot information set at important times. A baseline is defined between a pair of snapshots, and the snapshot is identified by their snapshot sequence number. each
The baseline has only a couple of snapshots. A typical performance tuning practice starts with collecting baseline collections of metrics, making changes, and then capturing another set of baselines. You can compare these two collections to check
The effect of the changes made. In AWR, the same type of comparison can be performed on an existing captured snapshot.

Suppose a name called Apply_interest runs between 2:00 and 4:00, corresponding to snapshot ID 4150 to 4151. We can define for these snapshots
A baseline called apply_interest_1:

sql> exec dbms_workload_repository.create_baseline (4150, 4151, ' apply_interest_1 ');

This operation numbers the snapshot from 4150 to 4151 as part of the baseline specified above. To view an existing baseline:
sql> Select *from dba_hist_baseline;

DBID baseline_id baseline_name start_snap_id start_snap_time end_snap_id end_snap_time
---------- ----------- -------------------- ------------- --------------------------------  ----------- -------------- -----------------
3437504306 1 apply_interest_1 4150 July-March-11 03.00.47.627 am 4151 July-March-11 04.00.12. 567 a.m.

sql> Select *from wrm$_baseline;

DBID baseline_id baseline_name start_snap_id end_snap_id
---------- ----------- ------------------------------  -------------  -----------
3437504306 1 apply_interest_1 4150 4151


After some adjustment steps, we can create another baseline-assuming the name is apply_interest_2 (2 o'clock in the afternoon to 4 o'clock), and then compare metrics only for those snapshots that are related to these two baselines.
sql> exec dbms_workload_repository.create_baseline (4162, 4163, ' apply_interest_2 ');

Separating snapshots in just a few sets like this helps to study the effects of adjustment on sexual energy.

4.2 Deleting baselines
After analysis, use Drop_baseline () to delete the baseline, and the snapshot will be retained (or cascade deleted). Also, when the purge routine begins to delete the old snapshot, the snapshot associated with the baseline is not
Be cleared, allowing for further analysis.
If you want to delete a baseline:
sql> exec dbms_workload_repository.drop_baseline (baseline_name=> ' apply_interest_1 ',cascade=>false);

Sql> Select *from wrh$_active_session_history where snap_id in (4150,4151);

snap_id DBID instance_number sample_id sample_time session_id ...
4150 3437504306 1 14900840 July-March-11 02.55.02.038 AM 162 ...
4150 3437504306 1 14900200 July-March-11 02.44.21.942 am 165 ...
....
4151 3437504306 1 14901980 July-March-11 03.14.02.213 am 165 ...
4151 3437504306 1 14901790 July-March-11 03.10.52.183 am 165 ...
4151 3437504306 1 14901490 July-March-11 03.05.52.138 am 167 ...

--Cascade Delete (baseline and snapshot piece delete)
sql> exec dbms_workload_repository.drop_baseline (baseline_name=> ' apply_interest_2 ',cascade=>true);

Sql> Select *from wrh$_active_session_history where snap_id in (4162,4163);
Row not selected

5. Generate AWR reports

Oracle can produce two types of AWR reports: Text format and HTML format. The HTML-formatted reporting interface is more user friendly. The AWR report is used to display two snapshots or two point-in-time snaps to a

The data. The AWR report is actually a database health check sheet that shows the metrics for database health.

There is an AWR report for the entire database, an AWR report for an instance (in a clustered environment), and an AWR report for a single SQL statement. Generated by running Oracle's own SQL script

AWR reports, just generate different awr reports and need to run different scripts.

--Generate an AWR report for the entire database and run the script Awrrpt.sql.

@ $ORACLE _home/rdbms/admin/awrrpt.sql

--Generate an AWR report for an instance and run the script Awrrpti.sql.

@ $ORACLE _home/rdbms/admin/awrrpti.sql

--Generates an AWR report of an SQL statement, running script Awrsqrpt.sql.

@ $ORACLE _home/rdbms/admin/awrsqrpt.sql

--Note: $ORACLE _home represents the home directory of ORACLE.

Example: an AWR report that produces an entire database. In this example, Oracle's home directory is/oracle/product/10.2.0/db_1/rdbms/admin

Run the script Awrrpt.sql.

Sql> @/oracle/product/10.2.0/db_1/rdbms/admin/awrrpt.sql

Select the type of report.

1. Enter the value of the Report_type: HTML

In this example, select HTML.

List the snapshots generated in the last two days (mostly get snapshot IDs)

1. Enter the value of the Num_days: 2

Snap
Instance DB Name snap Id snap Started level
------------ ------------ --------- ------------------ -----
TELEMT telemt 4123 2011 00:00 1
4124 April 2011 01:01 1
....
4160 2011 13:00 1
4161 2011 14:00 1

Specifies the ID of the starting snapshot and the ID of the end snapshot.

1. Specify the Begin and End Snapshot Ids
2. ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
3. Enter the value of the Begin_snap: 4123
4. Begin Snapshot Id specified:4123
5.
6. Enter the value of the End_snap: 4161
7. End Snapshot Id specified:4161

The ID of the starting snapshot in this example is 4123, and the ID of the ending snapshot is 4161.

Specifies the name of the report.

Enter the value of the Report_name: myreport.html

Note: Enter the report name after carriage return appears ORA-00904:: Invalid identified value
WORKAROUND: Use the SYS user to execute or authorize the user grant all on dbms_workload_repository to Perfstat;

Reprint: AWR Introduction using