Extracting Oracle AWR reports

Performance testing sometimes need to analyze the execution of SQL, to identify the need to optimize the Sql,oracle database to provide a good database status and SQL performance monitoring platform, the database monitoring platform can always monitor the status of the database, but also can take the monitoring period of time to generate the AWR report, To analyze the running state of a database for a specified period of time.

The AWR report is actually the data between the two snapshots in the database, which forms a report that visually shows the state of the database running between the selected two snapshots in the form of data.

How to extract the Awr report I know of two, one is from the command line extraction, one is from the console of the Oracle database to specify two snapshots to generate (if the database opens the console, the address is generally "http://localhost:1158/em"), The following records the process of extracting the AWR report from the command line.

1. Link the database on the command line

# sqlplus /nolog -- Connect using no user

SQL>conn sysdba; -- connect to Super Admin

2, Oracle data is stored for 7 days of snapshot information by default, the default sampling interval is 1 hours (storage duration and sampling period can be set according to their own needs), this information can be obtained from the Dba_hist_wr_control view:

1Sql>Col Snap_interval forA202Sql>Col RETENTION forA203Sql>Select *  fromDba_hist_wr_control;4Snap_interval=+00000  on:xx:00.0indicates that the sampling interval is 1 hours5RETENTION=+00007 xx:xx:00.0indicates that the sampling data retention period is 7 days6 7 For more references to the Dba_hist_wr_control view see Oracle Official Documentation8http//Download.oracle.com/Docs/Cd/B19306_01/Server.102/b14237/statviews_3202.htm#refrn23462

3. Extract the AWR report between two snapshots

1Sql>@?/Rdbms/Admin/Awrrpt.sql2 Specify the report Type: "HTML"3 --Select the time to view the snapshot and not exceed the maximum retention time;4 --Enter the start and end nodes of the snapshot as follows:5Enter value forEnd_snap:12656 EndSnapshot Id specified:12657 8Define the name of the report;<Name>9 TenNote: Generate the AWR report under the current directory on the command line

Note:

Oracle does not generate the first snapshot by default, does not generate snapshot records at time intervals without the first snapshot, if the instrumentation data does not have a snapshot, receive a snapshot, and the database automatically records the snapshot based on the set period. The instructions for manually generating and deleting snapshots are as follows:

1 Create and delete baselines2 execDbms_workload_repository.create_baseline (start_snap_id=xx,end_snap_id=XX, Baseline_name= 'Base Line');3 Query Baselines4 Selectbaseline_name,start_snap_id,end_snap_id fromDba_hist_baseline; 5 Delete a baseline6 execDbms_workload_repository.drop_baseline (baseline_name= 'Base Line',Cascade =true); 7 8 Creating and deleting snapshots9 execDbms_workload_repository.create_snapshot (); Ten execDbms_workload_repository.drop_snapshot_range (low_snap_id=XX, high_snap_id=XX); XX indicates snap ID

Extracting Oracle AWR reports