Oracle AWR Introduction

Oracle Database 10 Gb provides a significantly improved tool: Automatic Workload information library (AWR: Automatic Workload Repository ). Oracle recommends that you replace Statspack with this. AWR is essentially an Oracle built-in tool that collects performance-related statistics and exports performance measurements from those statistics to track potential problems. Unlike Statspack, snapshots are automatically collected hourly by a new background process called MMON and its slave processes. To save space, the collected data is automatically cleared 7 days later. You can modify the snapshot frequency and retention time. It generates two types of output: The text format (similar to the text format of the Statspack report but from the AWR Information Library) and the default HTML format (with all the hyperlinks to the part and sub-part ), this provides very user-friendly reports.

AWR uses several tables to store collected statistics. All Tables are stored in the SYS mode in a specific tablespace named SYSAUX, it is named in the format of WRM $ _ * And WRH $. The previous type stores metadata information (such as checked databases and collected snapshots), and the latter type stores actual collected statistics. H Represents "historical" and M represents "metadata )". Several views with DBA_HIST _ prefix are built on these tables. These views can be used to compile your own performance diagnostic tools. The view name is directly related to the table. For example, the view DBA_HIST_SYSMETRIC_SUMMARY is built on the WRH $ _ SYSMETRIC_SUMMARY table.

Note:

Statistics_level is typical by default, and table monitoring is activated in 10g. It is strongly recommended that the value of this parameter be typical in 10g. If STATISTICS_LEVEL is set to basic, not only tables cannot be monitored, but also the following 10g new functions are disabled:
ASH (Active Session History)
ASSM (Automatic Shared Memory Management)
AWR (Automatic Workload Repository)
ADDM (Automatic Database Diagnostic Monitor)

I. AWR usage
SQL> @? /Rdbms/admin/awrrpt. SQL

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 the value of report_type:
Type Specified: html
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 the value of num_days: 1
Listing the last day's Completed Snapshots
Snap
Instance DB Name Snap Id Snap Started Level
--------------------------------------------------------
Orcl10g ORCL10G 142 03 July 20 1
143 03, January 1, July 20
144 03 July 20 1
145 03 July 20 1
146 03 July 20 1
Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Value of begin_snap: 142
Begin Snapshot Id specified: 142
Input end_snap value: 146
End Snapshot Id specified: 146
Specify the Report Name
~~~~~~~~~~~~~~~~~~~~~~~
The default report file name is awrrpt_1_142_146.html. To use this name,
Press <return> to continue, otherwise enter an alternative.
Input report_name value: D: \ awrrpt_1_142_146.html
Report written to D: \ awrrpt_1_142_146.html

2 AWR operations
1. view the current AWR Storage Policy
SQL> col SNAP_INTERVAL format a20
SQL> col RETENTION format a20
SQL> select * from dba_hist_wr_control;
DBID SNAP_INTERVAL RETENTION TOPNSQL
------------------------------------------------------------
262089084 + 00000 01:00:00. 0 + 00007 00:00:00. 0 DEFAULT
The above result indicates that a SNAPSHOT is generated every hour and is retained for 7 days.
2. Adjust AWR Configuration
AWR configurations are all configured through the dbms_workload_repository package.
2.1 adjust the frequency and retention policy of AWR snapshot generation. For example, change the collection interval to 30 minutes. And keep the time for 5 days (in minutes ):
SQL> exec dbms_workload_repository.modify_snapshot_settings (interval => 30, retention => 5*24*60 );
2.2 disable AWR and set interval to 0 to disable automatic snapshot capturing.
SQL> exec dbms_workload_repository.modify_snapshot_settings (interval => 0 );
2.3 manually create a snapshot
SQL> exec DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT ();
2.4 view snapshots
SQL> select * from sys. wrh $ _ active_session_history
2.5 manually delete snapshots of a specified range
SQL> exec DBMS_WORKLOAD_REPOSITORY.DROP_SNAPSHOT_RANGE (low_snap_id => 973, high_snap_id => 999, dbid => 262089084 );
2.6 create a baseline and save the data for future analysis and comparison.
SQL> exec dbms_workload_repository.create_baseline (start_snap_id => 1003, end_snap_id => 1013, 'apply _ interest_1 ');
2.7 Delete baseline
SQL> exec DBMS_WORKLOAD_REPOSITORY.DROP_BASELINE (baseline_name => 'apply _ interest_1 ', cascade => FALSE );
2.8 export and migrate AWR data to other databases for later analysis
SQL> exec DBMS_SWRF_INTERNAL.AWR_EXTRACT (dmpfile => 'awr _ data. dmp ', mpdir => 'dir _ BDUMP', bid => 1003, eid => 1013 );
2.9 migrate AWR data files to other databases
SQL> exec DBMS_SWRF_INTERNAL.AWR_LOAD (SCHNAME => 'awr _ test', dmpfile => 'awr _ data. dmp ', dmpdir => 'dir _ BDUMP ');
Transfer AWR data to SYS mode:
SQL> exec DBMS_SWRF_INTERNAL.MOVE_TO_AWR (SCHNAME => 'test ');

Iii. AWR Report Analysis
3.1 SQL ordered by Elapsed Time
Top SQL statements that record the total execution Time (note that the total execution Time of the SQL statement within the monitoring range, rather than the execution Time of a single SQL statement Elapsed Time = CPU Time + Wait Time ).
Elapsed Time (S): the total duration of SQL statement execution. This sorting is performed according to this field. Note that this time is not the time for running a single SQL statement, but the total time of the number of SQL statements executed within the monitoring range. Unit: seconds. Elapsed Time = CPU Time + Wait Time
CPU Time (s): The total CPU usage Time during SQL statement execution. This Time is less than or equal to the Elapsed Time. Unit: seconds.
Executions: Total number of SQL statement Executions within the monitoring range.
Elap per Exec (s): average time for executing an SQL statement. Unit: seconds.
% Total DB Time: the percentage of Elapsed Time of SQL to the Total Time of the database.
SQL id: ID of the SQL statement. Click it to navigate to the SQL details list below. Click IE to return to the current SQL ID.
SQL Module: shows how the SQL statement is connected to the database for execution. If the SQL * Plus or PL/SQL link is used, someone is debugging the program. Generally, the SQL statement that is linked to the foreground application is empty.
SQL Text: A simple SQL prompt. For details, click the SQL ID.
3.2 SQL ordered by CPU Time:
The top SQL statements with the longest execution time as the sum of CPU time are recorded (note that the execution time of this SQL statement is the sum of CPU time in the monitoring range, rather than the execution time of a single SQL statement ).
3.3 SQL ordered by Gets:
The top SQL statements whose execution accounts for the total buffer gets (logical IO) are recorded (note that the execution of this SQL statement accounts for the total Gets within the monitoring scope, rather than the Gets occupied by a single SQL Execution ).
3.4 SQL ordered by Reads:
The top SQL statements that occupy the total Disk Physical reads (physical IO) are recorded (note that the execution of this SQL statement accounts for the total Disk Physical reads within the monitoring scope, instead of the Disk Physical reads occupied by a single SQL Execution ).
3.5 SQL ordered by Executions:
The top SQL statements sorted by the number of SQL executions are recorded. This sorting shows the number of SQL executions within the monitoring range.
3.6 SQL ordered by Parse cballs:
Top SQL that records the number of soft resolutions of SQL statements. When it comes to soft parsing (soft prase) and hard parsing (hard prase), it is not worth mentioning the Oracle SQL processing process.
3.7 SQL ordered by Sharable Memory:
Records top SQL statements that occupy the size of library cache. Sharable Mem (B): the size of the library cache in bytes.
3.8 SQL ordered by Version Count:
Records top SQL statements for opening sub-game targets.
3.9 SQL ordered by Cluster Wait Time:
Top SQL statements that record the cluster wait time