Generate awr report and awr report

Generate awr report and awr report

References:

Http://343766868.blog.163.com/blog/static/48314056201110124513396/

Overview

Oracle stores critical system statistics and load information at a certain frequency to generate snapshots. All snapshots are stored in awr, that is, automatic workload repository.

When a database performance problem is detected, you can extract the snapshot information stored in awr to generate an awr report within the specified time period (that is, the time period covered by your specified snapshot. Then, analyze the report to find out system problems.

First, let's talk about snapshot management. View the list of generated snapshots

Select * from dba_hist_snapshot;

In general, snapshot does not need to be manually managed. You can also manually generate and delete snapshots if you have special requirements.

Manually generate snapshot.

SQL> execdbms_workload_repository.create_snapshot ();

Manually delete snapshot.

SQL> exec dbms_workload_repository.dorp (low_snap_id => 1, high_snap_id => 2 );

Change the snapshot frequency and other parameters.

By default, a snapshot is generated every hour, and each snapshot is retained for 7 days.

You can use the following statement to query the configuration information generated by the current snapshot of the database.

Select * from dba_hist_wr_control;

 

You can also use

SQL> exec dbms_workload_repository.modify_snapshot_settings (interval => 60

Retention => 3*24*60

);

Where:

Retention parameter and retention time, in minutes ). If it is set to 0, the snapshot is permanently retained. If it is set to null, the original settings are retained. The range of this parameter is 1 day-100.

The Interval parameter is the snapshot generation frequency. The Unit is min (minutes ). If it is set to 0, manual and automatic snapshot generation are disabled. If it is set to null, the original settings are retained. The value range of this parameter is 10 min-1 year.

In addition, the topnsql and dbid parameters are provided. topnsql indicates the number of SQL statements that occupy resources to be collected.

Generate an awr report.

After learning about snapshots, you can use snapshot to generate awr reports.

Automatic Generation is to view awr related items in the OEM. Manual generation refers to the execution script provided by oracle.

The script is located in the $ ORACLE_HOME/rdbms/admin/directory and named awrrpt. SQL.

SQL> @? /Rdbms/admin/awrrpt. SQL

You can. The generated path is the current directory before sqlplus. Therefore, if you want to put the report in a specified directory, You can first enter the directory, such

Cd/tmp/awr/

Then, go to sqlplus and manually generate an awr report.

After the awr report is generated, the awr report is analyzed. This is a big problem and a difficult problem. Let's talk about it later.

What permissions are required to generate an oracle database awr report?

The techniques for generating an AWR report () are as follows ::
1. log on to the Oracle database ::

Sqlplus/as sysdba

2. In sqlplus, run the following () command as sys to generate the first performance data snapshot ::

Exec DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT (flush_level => 'all ');

3. Run all or many poorly performing applications or SQL statements you have found to keep the system under high pressure) status (such as CPU or I/O), and keep this status from half an hour to one hour.

4. In sqlplus, run the following () command again as sys to generate a second performance data snapshot ::

Exec DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT (flush_level => 'all ');

5. In sqlplus, run the following () command as sys to generate a performance report ::

@? /Rdbms/admin/awrrpt. SQL

As prompted, select the two () performance data snapshots generated above, and finally generate a final () AWR performance report ..

How to generate an AWR report

1. generate an AWR report for a single instance: @ $ ORACLE_HOME/rdbms/admin/awrrpt. sql2. generate an Oracle rac awr Report: @ $ ORACLE_HOME/rdbms/admin/awrgrpt. sql3. generate an AWR report for a specific database instance in the RAC environment: @ $ ORACLE_HOME/rdbms/admin/awrrpti. sql4. how to generate AWR reports for multiple database instances in the Oracle RAC environment: @ $ ORACLE_HOME/rdbms/admin/awrkgti. sql5. generate an AWR report for the SQL statement: @ $ ORACLE_HOME/rdbms/admin/awrsqrpt. sql6. generate an AWR report for an SQL statement on a specific database instance: @ $ ORACLE_HOME/rdbms/admin/awrsqrpi. SQL -- generate AWR period comparison report 7. generate the AWR period comparison report for a single instance @ $ ORACLE_HOME/rdbms/admin/awrddrpt. sql8. generate the Oracle rac awr period comparison report @ $ ORACLE_HOME/rdbms/admin/awrgdrpt. sql9. generate the AWR period comparison report for a specific database instance @ $ ORACLE_HOME/rdbms/admin/awrddrpi. sql10. generate the AWR period comparison report for specific (multiple) database instances in the Oracle RAC environment @ $ ORACLE_HOME/rdbms/admin/awrgdrpi. SQL Source: 51cto Author: Oracle