Main references:
http://343766868.blog.163.com/blog/static/48314056201110124513396/
Overview
Oracle stores the system's critical statistics and load conditions at a certain frequency internally, generating snapshot (snapshots), all of which are stored in the Awr snapshot. That is automatic workload repository.
When there is a performance problem with the database being discovered. You can extract the snapshot information stored in the AWR to generate an AWR report for a specified period of time (that is, the period that is included in the snapshot that you specify). The report is then analyzed. Identify problems with the system.
First of all, snapshot management.View generated snapshot list information
Select * from Dba_hist_snapshot;
under normal circumstances. Snapshot does not need to be managed manually, and can be manually generated and deleted if there is a special need.
manually generatedSnapshot.
Sql>execdbms_workload_repository.create_snapshot ();
Manually DeleteSnapshot.
Sql>exec Dbms_workload_repository.dorp (LOW_SNAP_ID=>1,HIGH_SNAP_ID=>2);
ChangeSnapshotFrequency and other parameters.
The database defaults to one snapshot build operation per hour, with each snapshot reserved for 7 days.
The following statement enables you to query the current snapshot generation configuration information for the database.
SELECT * from Dba_hist_wr_control;
If you need to change the secondary default parameters, you can also use the
Sql>exec Dbms_workload_repository.modify_snapshot_settings (interval=>60
Retention=>3*24*60
);
Of
Retention the number of references and the retention time. The Unit is min (min).
If set to 0. The snapshot is permanently preserved.
If set to null, the original setting is preserved. The range of this parameter is 1 days---100 years.
Interval the number of snapshot frequencies to generate. The Unit is min (min). If set to 0. Disables the ability to manually and proactively generate snapshots.
If set to null, the original setting is preserved. The range for this parameter is 10min---1 years.
In addition, there are topnsql and dbid parameters. Topnsql represents the number of SQL to be taken as a resource.
Generate an AWR report.
After the snapshot is known. will be able to generate AWR reports using snapshot.
Self-generated is to use view AWR related items in the OEM. Manual build refers to running scripts provided by Oracle.
The script is located under the $oracle_home/rdbms/admin/folder with the name Awrrpt.sql.
Sql>@?/rdbms/admin/awrrpt.sql
We can. The generated path is the current folder before you enter Sqlplus.
So to put the report in the specified folder, you can go to the folder beforehand. Example
cd/tmp/awr/
Then, when entering Sqlplus, generate the AWR report manually.
Generate an AWR report. Analysis of the left AWR report, which is a big problem, it is a more difficult problem. Speak slowly later.
Copyright notice: This article blog original articles, blogs, without consent, may not be reproduced.
Generate AWR reports