Subscribe to Posts by Email

Subscriber Count

    696

Disclaimer

All information is offered in good faith and in the hope that it may be of use for educational purpose and for Database community purpose, but is not guaranteed to be correct, up to date or suitable for any particular purpose. db.geeksinsight.com accepts no liability in respect of this information or its use. This site is independent of and does not represent Oracle Corporation in any way. Oracle does not officially sponsor, approve, or endorse this site or its content and if notify any such I am happy to remove. Product and company names mentioned in this website may be the trademarks of their respective owners and published here for informational purpose only. This is my personal blog. The views expressed on these pages are mine and learnt from other blogs and bloggers and to enhance and support the DBA community and this web blog does not represent the thoughts, intentions, plans or strategies of my current employer nor the Oracle and its affiliates or any other companies. And this website does not offer or take profit for providing these content and this is purely non-profit and for educational purpose only. If you see any issues with Content and copy write issues, I am happy to remove if you notify me. Contact Geek DBA Team, via geeksinsights@gmail.com

Pages

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

Comments are closed.