如何从AWR中获取历史数据信息

1. 说明

一般如果需要观察主机资源使用情况的历史,比如CPU、IO等,需要安装相关的监控工具,比如:AIX、Linux 的nmon、Oracle的OSWatch等。
但是如果未安装这些工具,但是又想大致确认一下哪个时间点是系统负载高峰,这时侯该怎么办呢,其实可以从AWR中获取相关数据。

2. 脚本

准备脚本如下:
awr_his

3. 输出样例

脚本的使用方法如下:

SQL> @?/rdbms/admin/awrrpt.sql

Current Instance
~~~~~~~~~~~~~~~~

   DB Id    DB Name      Inst Num Instance
----------- ------------ -------- ------------
 2296637408 xxxxx1              1 xxxxx11


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


Instances in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

   DB Id     Inst Num DB Name      Instance     Host
------------ -------- ------------ ------------ ------------
  2296637408        2 xxxxx1       xxxxx12      xx1xx02
* 2296637408        1 xxxxx1       xxxxx11      xx1xx01

Using 2296637408 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: 35

Listing the last 35 days of Completed Snapshots

                                                        Snap
Instance     DB Name        Snap Id    Snap Started    Level
------------ ------------ --------- ------------------ -----
xxxxx11      xxxxx1          111766 04 Jan 2017 00:00      1
                             111767 04 Jan 2017 01:00      1
                             111768 04 Jan 2017 02:00      1
                             111769 04 Jan 2017 03:00      1
......
                             112250 24 Jan 2017 04:00      1
                             112251 24 Jan 2017 05:00      1
                             112252 24 Jan 2017 06:00      1
                             112253 24 Jan 2017 07:00      1
                             112254 24 Jan 2017 08:00      1




Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap: 111766
Begin Snapshot Id specified: 111766

Enter value for end_snap: 112254
End   Snapshot Id specified: 112254



  Statistic ID Statistic  Statistic Name
-------------- ---------- ----------------------------------------------------------------
      24469293 USER       CPU used by this session
    3649082374 USER       DB time
     159846526 USER       SQL*Net roundtrips to/from client
    2420448902 USER       SQL*Net roundtrips to/from dblink
    3533966785 USER       Workload Capture: dbtime
......
    3977359732 DEBUG      transaction lock background gets
     185785666 DEBUG      transaction lock foreground requests
    4033661655 DEBUG      transaction lock foreground wait time
    3988650402 DEBUG      transaction rollbacks
    1396293204 DEBUG      transaction tables consistent read rollbacks
    1054055970 DEBUG      transaction tables consistent reads - undo records applied
     142917891 DEBUG      tune down retentions in space pressure
    1465971540 DEBUG      undo change vector size
     248401831 DEBUG      undo segment header was pinned


Specify the Statistics
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for stat_id: 24469293
Begin Statistics Id specified: 24469293


  Snap Id    DB Id     Inst Num Elapsed    Snap Started       Stat Value
--------- ------------ -------- ------- ------------------ -------------
   111766 2296637408          1    3560 04 Jan 2017 00:00              0
   111767 2296637408          1    3619 04 Jan 2017 01:00        2662602
   111768 2296637408          1    3619 04 Jan 2017 02:00        2742370
......
   112248 2296637408          2    3616 24 Jan 2017 02:00        1894188
   112249 2296637408          2    3619 24 Jan 2017 03:00        1909689
   112250 2296637408          2    3562 24 Jan 2017 04:00        2023274
   112251 2296637408          2    3616 24 Jan 2017 05:00        2187510
   112252 2296637408          2    3616 24 Jan 2017 06:00        1803255
   112253 2296637408          2    3554 24 Jan 2017 07:00        1745749
   112254 2296637408          2    3616 24 Jan 2017 08:00        1851090
SQL> exit

把最后Statistics的相关数据,可以保存成一个文本文件,如e:\output.txt。如图:
Huge-IT

4. gnuPlot画性能曲线图

最后使用gnuplot工具来画性能曲线图,gnuplot工具可以自行下载。
gnuplot打开如下:
Huge-IT
使用的画图脚本如下:

set timefmt "%d %b %Y %H:%M"
set xtic auto
set ytic auto
set xlabel "Date\nTime"
set yrange [ 0 : ]
set xdata time
set grid
set title "CPU used by this session"
set format x "%d/%m\n%H:%M"
plot 'e:/output.txt' using 5:($9/$4) title "CPUS/Sec" with lines

执行后得到CPU使用曲线如下:
Huge-IT
当前主机的CPU数是64

SQL> show parameter cpu_count

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
cpu_count                            integer     64

CPU used by this session的单位是百分之一秒,也就是64个CPU,最多可以提供6400个单位的CPU。
从以上图来看,在1月24日的某个时间CPU有比较严重的情况,这时侯就可以做相关的AWR报告进行分析了。

5. 值得关注的指标

值得关注的指标有如下:

  • CPU used by this session
  • redo size
  • db block gets
  • physical reads
  • physical writes

关于紫砂壶

感悟技术人生
此条目发表在Oracle性能问题分类目录,贴了标签。将固定链接加入收藏夹。