首页 » ORACLE » Script: 通过AWR信息查询db 中segments 的增长信息

Script: 通过AWR信息查询db 中segments 的增长信息

awr 中有很多wr开头的表收集的数据非常有用,通过dba_hist开头的视图可以很直观的查看,也可以手动查询一些详细或定制自己所要的信息

如表的增加速度

 


col object_name for a30
col savtime for a50
col owner for a20
set linesize 150 pagesize 1000


select to_char(savtime,'yyyy-mm-dd hh24:mi:ss') stime,owner,object_name,rowcnt,blkcnt
    from sys.wri$_optstat_tab_history h,dba_objects o 
    where o.owner='ICME' and o.object_id=h.obj#
order by 2,3,1;





SELECT obj.owner,
       obj.object_name,
       TO_CHAR (sn.BEGIN_INTERVAL_TIME, 'YYYY-MM-DD hh24:mi:ss') start_day,
       h.db_block_changes_delta,
       h.space_used_total,
       h.space_used_delta
  FROM dba_hist_seg_stat h, dba_hist_snapshot sn, dba_objects obj
 WHERE     sn.snap_id = h.snap_id
       AND obj.object_id = h.obj#
       AND obj# IN (SELECT obj#
                      FROM (SELECT obj#,
                                   ROW_NUMBER ()
                                   OVER (
                                      ORDER BY db_block_changes_delta DESC)
                                      rn
                              FROM dba_hist_seg_stat)
                     WHERE rn <= 10)
order by 1,2,3
打赏

对不起,这篇文章暂时关闭评论。