Oracle IO 性能数据收集

1. 操作系统的IO性能数据收集：
vmstat
procs ———–memory———- —swap– —–io—- –system– —-cpu—-
r b   swpd   free   buff cache   si   so    bi    bo   in    cs us sy id wa
0 0 195804   3812 40616 1598656    0    0     0     0 1024 66976 0 0 100 0
0 0 195804   3812 40616 1598656    0    0     0    12 1048 66961 0 0 100 0
0 0 195804   3812 40616 1598656    0    0     0    28 1054 67067 0 0 100 0
0 0 195804   3812 40616 1598656    0    0     0     0 1020 66988 0 0 100 0

PROCS
r 进程的个数，这些进程处于等待状态和基本不做任何事，但是在等待运行
b 进程的个数，这些进程在睡眠状态，并从上次更新后被中断允许。
w 进程的个数，这些进程被mm和vm子系统交换出去，还没有运行
MEMORY
swap 空闲的swap空间
PAGE
pi 交换分页进来的大小(k)
po 交换分页出去的大小(k)
DISK
Bi 每秒读的块数
Bo 每秒写的块数
CPU
Us 用于用户进程的CPU使用百分比
Sy 用于系统进程的CPU使用百分比
Id CPU空闲百分比
Wa CPU 空闲时间，在此期间系统有未完成的磁盘/NFS I/O 请求。请参阅上面的详细描述。

procs - r 过大，阀值是每个cpu四个进程
procs - 如果b的值比r大，那么可能是磁盘有问题。
page - pi/po如果持续增加，表示内存短缺
cpu - sy如果不应该超过30%，

iostat

avg-cpu:  %user   %nice    %sys %iowait   %idle
           2.99    0.00   10.67   15.27   71.06
Device:    rrqm/s wrqm/s   r/s   w/s  rsec/s  wsec/s    rkB/s    wkB/s avgrq-sz avgqu-sz   await  svctm  %util
sda         31.93   0.20 31.53  0.80 32668.27    8.03 16334.14     4.02  1010.73     1.75   53.76  25.29  81.75
sda5        32.13   0.00 31.53  0.00 32771.08    0.00 16385.54     0.00  1039.49     1.72   54.23  25.61  80.74
sdb         31.53   2.01 31.93  3.82 32302.01   44.18 16151.00    22.09   904.97     3.12   86.66  22.96  82.07
sdb1        31.53   0.00 31.93  2.81 32302.01   20.08 16151.00    10.04   930.43     2.99   85.39  23.46  81.49
sdc         31.53   3.01 33.33  9.84 32327.71   81.93 16163.86    40.96   750.70     7.83  299.70  21.97  94.84
sdc1        31.53   0.00 33.33  0.40 32327.71   12.85 16163.86     6.43   958.67     3.84  115.58  27.73  93.55
sdd         31.53   0.00 32.13  0.80 32308.43   25.70 16154.22    12.85   981.85     1.59   47.98  25.00  82.33
sdd1        31.53   0.00 32.13  0.80 32308.43   25.70 16154.22    12.85   981.85     1.59   47.98  25.00  82.33

r/s 每秒多少次读
w/s 每秒多少次写
kr/s 每秒读多少k
kw/s 每秒写多少k
avgrq-sz 平均等待服务的事务个数(队列长度)
await 等待队列平均服务时间(ms) --过高的等待时间
asvc_t 活动事务平均服务时间(ms) --超过20ms
%util 磁盘的使用率

2. Oracle统计信息

IO相应时间：

等待事件                      R/W      同步/异步       单块/多块          响应时间
------------------------------------------------------------------------------------
control file parallel write   Write    异步            多块               15 ms
control file sequential read  Read     同步            单块               20 ms
db file parallel read         Read     异步            多块               20 ms
db file scattered read        Read     异步            多块               20 ms
db file sequential read       Read     同步            单块               20 ms
direct path read              Read     异步            多块               20 ms
direct path read temp         Read     异步            多块               20 ms
direct path write             Write    异步            多块               15 ms
direct path write temp        Write    异步            多块               15 ms
log file parallel write       Write    异步            多块               15 ms

AWR report

Profile部分：

             Per Second     Per Transaction    Per Exec    Per Call
DB Time(s):        22.2                 0.2        0.01        0.01
DB CPU(s):          2.9                 0.0        0.00        0.00
Redo size:    602,081.7             6,304.8    
Logical reads:139,515.7             1,461.0    
Block changes:  2,663.3                27.9    
Physical reads:   135.6                 1.4    
Physical writes:  136.8

Top 5 events:
Event                     Waits    Time(s)      Avg wait (ms)    % DB time   Wait Class
---------------------------------------------------------------------------------------
log file sync             278,575   50,342                181        62.45   Commit
db file sequential read   460,844   18,222                 40        22.60   User I/O
DB CPU                     10,523    13.05  

Avg wait (ms) - 平均每次等待时间(ms)

Tablespace IO

Tablespace Reads     Av Reads/s    Av Rd(ms)    Av Blks/Rd    Writes   Av Writes/s   Buffer Waits   Av Buf Wt(ms)
-------------------------------------------------------------------------------------------------------------------
HSL2       245,257           68        32.76          1.01    23,848             7          2,418            1.77
HSLIDX2    102,935           28        51.41          1.00    68,825            19            192           60.36

Avg Rd(ms) - 平均每次读等待时间(ms)

等待时间直方图

Event                      Total Waits   <1ms   <2ms   <4ms   <8ms   <16ms   <32ms   <=1s   >1s
-----------------------------------------------------------------------------------------------
db file scattered read            1172    5.8    6.6   11.5   29.4    36.9     8.3    1.4    .1
db file sequential read         463.5K    4.6    5.0    8.7   33.5    40.0     4.6    2.8    .8
direct path read                    32   34.4    9.4   25.0   18.8     9.4     3.1  

常用的脚本：
select bt.tsname,  bt.filename,
1024*1024*1024*(et.readtim - bt.readtim) / df.bytes rd_tm_per_GB,
et.readtim - bt.readtim read_time,
et.writetim - bt.writetim write_time,
et.phyrds - bt.phyrds phys_reads,
et.phywrts - bt.phywrts phys_writes,
et.phyblkrd - bt.phyblkrd blocks_read,
et.phyblkwrt - bt.phyblkwrt blocks_wrt,
et.singleblkrds - bt.singleblkrds single_reads,
et.singleblkrdtim - bt.singleblkrdtim single_read_time,
df.bytes/1024/1024   M_Bytes
from sys.dba_hist_filestatxs bt,   sys.dba_hist_filestatxs et,  v$datafile df
where bt.snap_id = :bid and
tid id d et.snap_id = :eid and
bt.dbid = :dbid and bt.dbid = et.bdid and
bt.instance_number = :inst_num and bt.instance_number = et.instance_number and
bt.file# = et.file# and bt.file# = df.file#
;

select tbsp "Tablespace"
     , ios "I/O Activity"
From (
select e.tsname tbsp
     , sum (e.phyrds  - nvl(b.phyrds,0))  +
       sum (e.phywrts - nvl(b.phywrts,0)) ios
  from dba_hist_filestatxs  e
     , dba_hist_filestatxs  b
 where b.snap_id(+)         = &pBgnSnap
   and e.snap_id            = &pEndSnap
   and b.dbid(+)            = &pDbId
   and e.dbid               = &pDbId
   and b.dbid(+)            = e.dbid
   and b.instance_number(+) = &pInstNum
   and e.instance_number    = &pInstNum
   and b.instance_number(+) = e.instance_number
   and b.file#              = e.file#
   and ( (e.phyrds  - nvl(b.phyrds,0) ) +
         (e.phywrts - nvl(b.phywrts,0)) ) > 0
 group by e.tsname
union
select e.tsname tbsp
     , sum (e.phyrds  - nvl(b.phyrds,0))  +
       sum (e.phywrts - nvl(b.phywrts,0)) ios
  from dba_hist_tempstatxs  e
     , dba_hist_tempstatxs  b
 where b.snap_id(+)         = &pBgnSnap
   and e.snap_id            = &pEndSnap
   and b.dbid(+)            = &pDbId
   and e.dbid               = &pDbId
   and b.dbid(+)            = e.dbid
   and b.instance_number(+) = &pInstNum
   and e.instance_number    = &pInstNum
   and b.instance_number(+) = e.instance_number
   and b.file#              = e.file#
   and ( (e.phyrds  - nvl(b.phyrds,0) ) +
         (e.phywrts - nvl(b.phywrts,0) ) ) > 0
 group by e.tsname
)
;

select
   to_char(begin_interval_time,'yyyy-mm-dd hh24:mi') snap_time,
--   file_name,
   object_type,
   object_name,
   wait_count,
   time
from
   dba_hist_waitstat            wait,
   dba_hist_snapshot            snap,
   dba_hist_active_sess_history ash,
   dba_data_files               df,
   dba_objects                  obj
where
   wait.snap_id = snap.snap_id
and
   wait.snap_id = ash.snap_id
and
   df.file_id = ash.current_file#
and
   obj.object_id = ash.current_obj#
and
   wait_count > 50
order by
   to_char(begin_interval_time,'yyyy-mm-dd hh24:mi'),
   file_name
;

NOTE:
Hardware configuration
 Evaluate
  Data Layout: RAID 1+0/RAID 5/ASM
  Buffer Pool Allocation
  Data compression
  SQL Statements

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/25105315/viewspace-712667/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/25105315/viewspace-712667/