Thursday, September 6, 2007

How to determine tables with highest data change rate at Oracle 10G

DBA_HIST_SEG_STAT system view can be used to see physical read-write statistics on segments. Statistics are taken by regular snaps. Following sql script returns tables with higher block change rate, bigger then 20000.

select SNAP_ID,OWNER ,OBJECT_NAME, OBJECT_TYPE, DB_BLOCK_CHANGES_DELTA
from dba_objects a,
(select SNAP_ID,obj#,DB_BLOCK_CHANGES_DELTA from DBA_HIST_SEG_STAT where DB_BLOCK_CHANGES_DELTA > 20000 order by snap_id desc, DB_BLOCK_CHANGES_DELTA desc) b
where a.object_id=b.obj# and object_type='TABLE'
order by snap_id desc, DB_BLOCK_CHANGES_DELTA desc

select c.SNAP_ID,c.END_INTERVAL_TIME SNAP_TIME,a.OWNER ,a.OBJECT_NAME, a.OBJECT_TYPE, b.DB_BLOCK_CHANGES_DELTA
from dba_objects a,
(select SNAP_ID,obj#,DB_BLOCK_CHANGES_DELTA from DBA_HIST_SEG_STAT where DB_BLOCK_CHANGES_DELTA > 20000 order by snap_id desc, DB_BLOCK_CHANGES_DELTA desc) b,
DBA_HIST_SNAPSHOT c
where a.object_id=b.obj# and object_type='TABLE' and b.SNAP_ID=c.SNAP_ID
order by snap_id desc, DB_BLOCK_CHANGES_DELTA desc

2 comments:

Anonymous said...

Itwon't help, in this we have to input object name.
Requirement is we can able to see list of tables with top most growth in last couple of days

Anonymous said...



Its giving block changes & physical writes includes insert/update/delete, we need actual growth due to inserts . Unable to derive the same ..