Scripts: Segment statistics, reads/writes/waits at table level

Hi,

Suppose you have been asked about how much reads/writes happening and what are the wait events that occured for a object(table) in a given period or overall. so then read on….

We have different ways to get the Table level statistics like waits/reads/logical reads/physical reads /writes etc.

1) In Oracle 8i you can use catio.sql to measure the I/O for a given period.

$ORACLE_HOME/rdbms/admin/catio.sql

SQL> exec sample_io(10,60); 

PL/SQL procedure successfully completed. 

SQL> select * from io_per_object; 

2) In Oracle 9i, we have got v$segment_statistics, the following script provides the cross tab report for the segment level statistics (run time)

SQL> select distinct statistic_name from v$segment_statistics; 

STATISTIC_NAME 
---------------------------------------- 
ITL waits 
buffer busy waits 
db block changes 
global cache cr blocks served 
global cache current blocks served 
logical reads 
physical reads 
physical reads direct 
physical writes 
physical writes direct 
row lock waits

-- Crosstab of object and statistic for an owner -- (source dbaoracle) 
col "Object" format a20 
set numwidth 12 
set lines 132 
set pages 50 
@title132 'Object Wait Statistics' 
spool rep_out&&dbobj_stat_xtab 
select * from 
( 
   select 
      DECODE 
      (GROUPING(a.object_name), 1, 'All Objects', a.object_name) 
   AS "Object", 
sum(case when 
   a.statistic_name = 'ITL waits' 
then 
   a.value else null end) "ITL Waits", 
sum(case when 
   a.statistic_name = 'buffer busy waits' 
then 
   a.value else null end) "Buffer Busy Waits", 
sum(case when 
   a.statistic_name = 'row lock waits' 
then 
   a.value else null end) "Row Lock Waits", 
sum(case when 
   a.statistic_name = 'physical reads' 
then 
   a.value else null end) "Physical Reads", 
sum(case when 
   a.statistic_name = 'logical reads' 
then 
   a.value else null end) "Logical Reads" 
from 
   v$segment_statistics a 
where 
   a.owner like upper('&owner') 
group by 
   rollup(a.object_name)) b 
where (b."ITL Waits">0 or b."Buffer Busy Waits">0) 
/ 
spool off 
clear columns

3) Further enhancement, history of these statistics in dba_hist_seg_stat views. can be viewed, the following query generates a report of physical writes/or reads for the given owner day wise.

select distinct 
to_char(begin_interval_time,'mm/dd') c1, 
sum(physical_reads_total) reads, 
sum(physical_writes_total) writes 
from 
dba_hist_seg_stat s, 
dba_hist_seg_stat_obj o 
,dba_hist_snapshot sn 
where 
o.owner = '&USERNAME 
and 
s.obj# = o.obj# 
and 
sn.snap_id = s.snap_id 
and 
object_name = '&OBJNAME 
group by to_char(begin_interval_time,'mm/dd') 
order by 1; 

For all period:-

select 
   to_char(begin_interval_time,'yy/mm/dd/hh24') c1, 
   logical_reads_total c2, 
   physical_reads_total c3 
from 
   dba_hist_seg_stat     s, 
   dba_hist_seg_stat_obj o, 
   dba_hist_snapshot     sn 
where 
   o.owner = '&schemaname' 
and 
   s.obj# = o.obj# 
and 
   sn.snap_id = s.snap_id 
and 
   object_name = '&objname' 
order by 
   begin_interval_time;

Hope this scripts will be useful to you all as well.

-Thanks

Geek DBA