早上群裡喊資料庫挂了，開始階段服務登入不上，等登入系統後發現系統負載很高。
運作的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的總的時間