AWR全称Automatic Workload Repository,自动负载信息库,是Oracle 10g版本后推出的一种性能收集和分析工具,提供了一个时间段内整个系统的报表数据。通过AWR报告,可以分析指定的时间段内数据库系统的性能。
1. sqlplus登录Oracle
[root@ufdb165 ~]# su - oracle
Last login: Thu Aug 11 13:30:16 CST 2022 on pts/0
[oracle@ufdb165 ~]$ sqlplus /nolog
SQL*Plus: Release 11.2.0.4.0 Production on Sun Aug 14 13:59:26 2022
Copyright (c) 1982, 2013, Oracle. All rights reserved.
SQL> conn /as sysdba
Connected.
SQL>
2. 创建快照
执行语句创建快照:exec dbms_workload_repository.create_snapshot;
2.1 压测前创建快照
SQL> exec dbms_workload_repository.create_snapshot;
PL/SQL procedure successfully completed.
2.2 压测结束创建快照
SQL> exec dbms_workload_repository.create_snapshot;
PL/SQL procedure successfully completed.
3. 生成awr报告
3.1 执行命令: @?/rdbms/admin/awrrpt
3.2 选择awr报告类型,可选择html或text,默认为html(直接回车即可)。建议选择html
3.3 选择要生成的报告的日期是在多少天以前记录,输入1,则表示要生成今天0点开始到现在之内的某个时间段的报告;输入2,则表示要生成昨天0点开始到现在之内的某个时间段的报告;以此类推。默认情况下,AWR会将镜像信息保留一个月,缺省记录最近7天。
3.4 输入天数后,界面会输出一个时间段的表格,每个时间点都对应一个snap Id,间隔时间默认是1个小时。接下来,输入要生成报告的时间开始点对应的snap Id, 然后再输入结束点对应的snap Id
3.5 接着要求输入生成报告的名字,例如:awr.html。系统会自动生成一个报告名字并会在提示信息中显示出来,如果使用默认名称,则不用输入任何内容,直接回车即可。
SQL> @?/rdbms/admin/awrrpt
Current Instance
~~~~~~~~~~~~~~~~
DB Id DB Name Inst Num Instance
----------- ------------ -------- ------------
2154841071 UFGOVDB1 1 ufgovdb1
Specify the Report Type
~~~~~~~~~~~~~~~~~~~~~~~
Would you like an HTML report, or a plain text report?
Enter 'html' for an HTML report, or 'text' for plain text
Defaults to 'html'
Enter value for report_type:
Type Specified: html
Instances in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
DB Id Inst Num DB Name Instance Host
------------ -------- ------------ ------------ ------------
* 2154841071 1 UFGOVDB1 ufgovdb1 ufdb165
Using 2154841071 for database Id
Using 1 for instance number
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> without
specifying a number lists all completed snapshots.
Enter value for num_days: 1
Listing the last day's Completed Snapshots
Snap
Instance DB Name Snap Id Snap Started Level
------------ ------------ --------- ------------------ -----
ufgovdb1 UFGOVDB1 659 15 Aug 2022 00:00 1
660 15 Aug 2022 00:36 1
661 15 Aug 2022 00:36 1
Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap: 660
Begin Snapshot Id specified: 660
Enter value for end_snap: 661
End Snapshot Id specified: 661
Specify the Report Name
~~~~~~~~~~~~~~~~~~~~~~~
The default report file name is awrrpt_1_660_661.html. To use this name,
press <return> to continue, otherwise enter an alternative.
Enter value for report_name: awr.html
Using the report name awr.html
4. awr报告存放位置
awr报告默认存放位置为:/home/oracle/
[oracle@ufdb165 ~]$ cd /home/oracle/
[oracle@ufdb165 ~]$ ls
20220811-10-12.lst 20220811-1.lst 20220811awr.lst awr.html etc