Oracle 11g awr performance report

One, automatic workload library (Automatic Workload Repository,awr)

The Automated Workload library (Automatic Workload repository,awr) is a tool provided by Oracle Corporation. AWR can automatically collect, process, and save performance statistics, which are stored in memory and then saved in a database that can be displayed in a report or queried through a view. can help us identify performance bottlenecks for Oracle.

The statistics collected and processed by AWR include:

• Object statistics, used to determine database segments (tables, indexes ... ) of the Access situation
• Time-based statistics: Database activity Statistics, these statistical parameters through V$sys_time_model and V_sess_time_model query;
• System statistics: Based on system and session statistics, these statistics can be queried by V$sysstat and V$sesstat.
• SQL statistics: SQL that causes high load in the system, which uses a long execution time and CPU time;
• Activate session history (ASH) Statistics: Activates the history of session information and records the most recent session activity history

AWR automatically fetches one-time statistics per hour and records the captured performance snapshot in the proxy table of the work load record. An awr snapshot can also be created manually, but it is not usually necessary to do so. After the snapshot is fetched, the results of the fetch are analyzed by the automated Database Diagnostic monitor (ADDM).

When AWR fetches the SQL information, AWR compares the performance impact of the previously fetched SQL statements and captures the SQL statements for purpose, thus reducing the SQL statements that need to be captured.

An automated workload library (AWR) snapshot is stored in the database, which requires storage space, and its storage footprint depends on several factors:

• The greater the number of active sessions currently connected to Oralce, the more awr information will be;
• AWR snapshot crawl interval, default one hour crawl. As the crawl frequency increases, the more storage space is consumed.
• Retention period for historical data. Obviously, the longer the retention time, the more space is occupied.

The default snapshot capture interval is once per hour and records are saved in the database for 7 days. When you adjust the snapshot interval and retention period for the automatic Workload Library (AWR), its space consumption decreases. However, when you reduce the retention period, such as automatic Database Diagnostic monitor (ADDM), SQL Tuning Advisor, Redo Advisor, segment advisor, and so on.

For statistical results to be as objective as possible and to reflect the true business load distribution, we'd better extend the awr retention period to a complete cycle of business system operations. For example, if the operational characteristics of the business system are Monday to Friday business, week Six, the report operation, then 7 days is your complete work cycle. In the same vein, your work cycle may be one months and a quarter. Because the cost of the storage medium is a small part of the current database system environment, the storage cost of AWR statistics is not very high to store at least one business cycle.

1, the activation of AWR

By default, Oralce enables the database Statistics collection feature, which can be used

Sql> Show parameter Statistics_level    NAME                    TYPE         valuestatistics_level        string        Typical

If the value of Statistics_level is typical or all, it means that AWR is enabled; If the value of Statistics_level is basic, the AWR is disabled

2. View current AWR save policy

Select *  from Dba_hist_wr_control;dbid,snap_interval,retention,topnsql 860524039,+,xx:00.000000,+ xx:00.000000,DEFAULT

The above results indicate that one snapshot per hour is generated and retained for 7 days

Adjust the frequency and retention policies that AWR generates snapshot, such as changing the collection interval time to 30 minutes. and keep for 5 days (note: units are minutes):

Dbms_workload_repository.modify_snapshot_settings (Interval=retention= 5 *  - * );

3. Create a snapshot manually

exec Dbms_workload_repository. Create_snapshot ();

4. View Snapshots

Select *  from Sys.wrh$_active_session_history

5. Generate AWR Report

The AWR report is actually a database health check sheet that shows the database health metrics require SYSDBA permissions

1. Generate an AWR report for the entire database: @ $ORACLE _home/ Rdbms/admin/awrrpt.sql 
 2. Generate oracle RAC  environment: @ $ORACLE _home/rdbms/admin/awrgrpti.sql 
  5. Generate an AWR report for SQL statements: @ $ORACLE _home/rdbms/admin/awrsqrpt.sql 
 
  6. Generate an AWR report for an SQL statement on a specific DB instance: @$ oracle_home/rdbms/admin/awrsqrpi.sql--Generate AWR time Comparison report 
 7. Generate Single instance AWR time Comparison report @ $ORACLE _home/rdbms/admin/awrddrpt.sql 
 9. Generate oracle RAC  AWR window Comparison report @ $ORACLE _home/rdbms/admin/awrgdrpt.sql 
 
 
 the entire process of generating awr: