早上群里喊数据库挂了，开始阶段服务登录不上，等登录系统后发现系统负载很高。
运行的oracle服务，今天就用oracle的awr作了一把分析，步骤如下：
一、登录数据库
[root@iZ233j4mpnbZ ~]# su - oracle
[oracle@iZ233j4mpnbZ ~]$ sqlplus sys as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Tue Jun 21 14:36:31 2016
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Enter password:
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL>
二、数据异常时间段的参数
输入完后，将输出在当前文件夹下。
＃执行对应的awrrpt.sql脚本文件
SQL> @?/rdbms/admin/awrrpt.sql
Current Instance
~~~~~~~~~~~~~~~~
DB Id DB Name Inst Num Instance
----------- ------------ -------- ------------
745948352 XFIREORC1 xfireorc
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'
＃输入文件类型，默认为html
Enter value for report_type: html
Type Specified: html
Instances in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
DB Id Inst Num DB Name InstanceHost
------------ -------- ------------ ------------ ------------
* 745948352 1 XFIREORC xfireorciZ233j4mpnbZ
Using 745948352 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
------------ ------------ --------- ------------------ -----
xfireorc XFIREORC 9501 21 Jun 2016 00:00 1
9502 21 Jun 2016 01:00 1
9503 21 Jun 2016 02:00 1
9504 21 Jun 2016 03:00 1
9505 21 Jun 2016 04:01 1
9506 21 Jun 2016 05:00 1
9507 21 Jun 2016 06:00 1
9508 21 Jun 2016 07:00 1
9509 21 Jun 2016 08:00 1
9510 21 Jun 2016 09:00 1
9511 21 Jun 2016 10:00 1
9512 21 Jun 2016 11:00 1
9513 21 Jun 2016 12:00 1
9514 21 Jun 2016 13:00 1
9515 21 Jun 2016 14:00 1
＃对应的输入编号，指定分析一个具体时间段内的快照。
Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap: 9511
Begin Snapshot Id specified: 9511
Enter value for end_snap: 9512
End Snapshot Id specified: 9512
Specify the Report Name
~~~~~~~~~~~~~~~~~~~~~~~
The default report file name is awrrpt_1_9511_9512.html. To use this name,
press <return> to continue, otherwise enter an alternative.
＃输入文件名
Enter value for report_name: report10-11
Using the report name report10-11
三、分析
将输出的报告，拷贝到本地进行分析。里边的内容有很多，但是真的很强大。也很易懂。
1、分析单条语句造成的Physical Reads（物理读）次数
2、分析语句占用cpu的总的时间
相关资源：ORACLE 健康检查与性能分析报告
