Oracle 表的I/O统计

apollo

Oracle 表的I/O统计

oracle可以通过查询AWR相关表来统计表的I/O。WRH$_SEG_STAT这个表就是每个小时的数据。其中包括从建库以来的所有数据，和某一个小时的数据都有。可以通过下面一个语句对这个表进行汇总。

select a.snap_id,a.ts#,d.name,a.obj#,b.object_name,b.subobject_name,to_char(c.begin_interval_time,'yyyymmdd hh24:mi:ss') snap_time,

sum(logical_reads_delta) logical_reads,sum(physical_reads_delta) physical_reads,sum(physical_writes_delta) physical_writes

from WRH$_SEG_STAT a,all_objects b,DBA_HIST_SNAPSHOT c,v$tablespace d

where  a.obj#=b.object_id

and a.snap_id=c.snap_id

and a.ts#=d.ts#

group by a.snap_id,a.ts#,d.name,a.obj#,b.object_name,b.subobject_name,to_char(c.begin_interval_time,'yyyymmdd hh24:mi:ss')

order by a.snap_id,physical_reads desc