通常DBA在管理数据库的时候,可以通过分析AWR(Automatic Workload Repository)报告来诊断数据库的性能问题,熟练掌握AWR报告,是DBA运维的必备技能之一。AWR报告的原理是基于Oracle数据库的定时 镜像功能。默认情况下,Oracle数据库后台进程会以一定间隔(一小时)收集系统当前状态镜像,并且保存在数据库中。生成AWR报告时,只需要指定进行 分析的时间段(开始镜像编号和结束镜像编号),就可以生成该时间段的性能分析情况。AWR镜像保存在数据库中的时间为一个月左右。本篇中采用的是手工脚本 方式生成AWR的方法。
1、创建生成的AWR报告存放目录
[root@localhost ~]# mkdir -p /awr
[root@localhost ~]#chown oracle:oinstall /awr
2、启用数据库常用工具sqlplus,调用生成AWR的脚本。此脚本保存在$ORACLE_HOME下的rdbms/admin中,名称为awrrpt.sql。
[root@localhost ~]# su - oracle
[oracle@localhost ~]$ cd /awr/
[oracle@localhost awr]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Sun Jun 28 03:35:35 2015
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> @?/rdbms/admin/awrrpt.sql
Current Instance
~~~~~~~~~~~~~~~~
DB Id DB Name Inst Num Instance
----------- ------------ -------- ------------
2181523472 TEST 1 test
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: html
Type Specified: html
EXT
-----
.html
Instances in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
DB Id Inst Num DB Name Instance Host
------------ -------- ------------ ------------ ------------
* 2181523472 1 TEST test localhost.lo
caldomain
Using 2181523472 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 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
------------ ------------ --------- ------------------ -----
test TEST 2 28 Jun 2015 03:42 1
3 28 Jun 2015 05:00 1
4 28 Jun 2015 06:00 1
Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap: 3
Begin Snapshot Id specified: 3
Enter value for end_snap: 4
End Snapshot Id specified: 4
Specify the Report Name
~~~~~~~~~~~~~~~~~~~~~~~
The default report file name is awrrpt_1_3_4.html. To use this name,
press to continue, otherwise enter an alternative.
Enter value for report_name:
Using the report name awrrpt_1_3_4.html
.
.
Report written to awrrpt_1_3_4.html
SQL>
[oracle@localhost awr]$ pwd
/awr
[oracle@localhost awr]$ ls
awrrpt_1_3_4.html
[oracle@localhost awr]$
以上就是一份oracle数据库AWR报告的生成过程
1、创建生成的AWR报告存放目录
[root@localhost ~]# mkdir -p /awr
[root@localhost ~]#chown oracle:oinstall /awr
2、启用数据库常用工具sqlplus,调用生成AWR的脚本。此脚本保存在$ORACLE_HOME下的rdbms/admin中,名称为awrrpt.sql。
[root@localhost ~]# su - oracle
[oracle@localhost ~]$ cd /awr/
[oracle@localhost awr]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Sun Jun 28 03:35:35 2015
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> @?/rdbms/admin/awrrpt.sql
Current Instance
~~~~~~~~~~~~~~~~
DB Id DB Name Inst Num Instance
----------- ------------ -------- ------------
2181523472 TEST 1 test
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: html
Type Specified: html
EXT
-----
.html
Instances in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
DB Id Inst Num DB Name Instance Host
------------ -------- ------------ ------------ ------------
* 2181523472 1 TEST test localhost.lo
caldomain
Using 2181523472 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 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
------------ ------------ --------- ------------------ -----
test TEST 2 28 Jun 2015 03:42 1
3 28 Jun 2015 05:00 1
4 28 Jun 2015 06:00 1
Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap: 3
Begin Snapshot Id specified: 3
Enter value for end_snap: 4
End Snapshot Id specified: 4
Specify the Report Name
~~~~~~~~~~~~~~~~~~~~~~~
The default report file name is awrrpt_1_3_4.html. To use this name,
press to continue, otherwise enter an alternative.
Enter value for report_name:
Using the report name awrrpt_1_3_4.html
.
.
Report written to awrrpt_1_3_4.html
SQL>
[oracle@localhost awr]$ pwd
/awr
[oracle@localhost awr]$ ls
awrrpt_1_3_4.html
[oracle@localhost awr]$
以上就是一份oracle数据库AWR报告的生成过程
![](http://blog.itpub.net/kindeditor/plugins/emoticons/images/13.gif)
![](http://blog.itpub.net/kindeditor/plugins/emoticons/images/13.gif)
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/30310891/viewspace-1715103/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/30310891/viewspace-1715103/