ORACLE常用监控语句(未完待续)

最新推荐文章于 2024-08-08 17:19:34 发布

原创最新推荐文章于 2024-08-08 17:19:34 发布 · 988 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#常用 #监控语句

ORACLE 专栏收录该内容

42 篇文章

订阅专栏

本文介绍如何使用Oracle数据库的v$log_history视图来查询重做日志的切换频率，并展示了一个SQL查询示例。此外，还提供了一个用于找出经常进行DML操作的表的方法，通过dba_objects和dba_hist_seg_stat视图的联合查询实现。

--查询日志的切换频率
select t1.RECID as srecid
       ,t2.RECID as erecid
       ,t1.FIRST_TIME as stime
       ,t2.FIRST_TIME as etime
       ,round((t2.FIRST_TIME-t1.FIRST_TIME)*1440,2) as minutes
from v$log_history t1,v$log_history t2
where t1.RECID+1=t2.RECID and /*选择时间段*/t1.FIRST_TIME between trunc(sysdate)/*起始*/ and sysdate/*终止*/
order by t1.RECID desc

--查看经常发生dml操作的表
select t1.OWNER,t1.OBJECT_NAME,t1.OBJECT_TYPE,t2.db_block_changes_total
from dba_objects t1
join (
      select obj#,sum(db_block_changes_total) db_block_changes_total
      from dba_hist_seg_stat
      group by obj#
) t2
on t1.OBJECT_ID=t2.obj#
where t1.OBJECT_TYPE='TABLE'
order by db_block_changes_total desc