本文初步陈列一些与数据库状态监控有关的SQL,有的是自己写的,有的是摘自读过的文档或书籍,… 不管怎样,能解决问题就是好的。
下面开始。

1. 数据库的用户连接与断开的历史(次数)

2. 查看某个用户的登陆与登出时间

3. 数据库的表空间大小

4. TOP10:空间占用最大的表

5. 列出每秒的逻辑读与逻辑写
代码:
with
pivoted_data as
(
select * from ( select snap_id,name,value from stats$sysstat )
pivot(
sum(value) for name in (‘physical reads’ as physical_reads,’session logical reads’ as logical_reads)
)
),
deltas as
(
select
snap_id,snap_time,
snap_time – lag(snap_time) over (partition by startup_time order by snap_id) as duration,
physical_reads – lag(physical_reads) over (partition by startup_time order by snap_id) as physical_reads,
logical_reads – lag(logical_reads) over (partition by startup_time order by snap_id) as logical_reads
from pivoted_data natural join stats$snapshot
)
select
snap_id,
to_char(snap_time,’yyyy-mon-dd hh24:mi:ss’) as snap_time,
physical_reads / duration / (24*60*60) as physical_reads_per_second,
logical_reads / duration / (24*60*60) as logical_reads_per_second
from deltas
order by snap_id
;

6. 计算CPU的利用率
代码:
with
pivoted_data as
(
select * from (select snap_id,osstat_id,value from stats$osstat) pivot(sum(value) for osstat_id in (1 as idle_time,2 as busy_time))
),
deltas as
(
select
snap_id,snap_time,
idle_time – lag(idle_time) over (partition by startup_time order by snap_id) as idle_time,
busy_time – lag(busy_time) over (partition by startup_time order by snap_id) as busy_time
from pivoted_data natural join stats$snapshot
)
select
snap_id,
to_char(snap_time,’yyyy-mon-dd hh24:mi:ss’) as snap_time,
busy_time / (idle_time+busy_time) as cpu_utilization_percent
from deltas order by snap_id
;

7. 数据库大小以及可用空间

————————————————————
Ending。

打赏

历史上的今天:

  1. 2017:  禅道:Install on Linux 64(CentOS 7.1.1503)【一键安装包】(1)
  2. 2017:  Oracle DG:一个小问题导致的DG同步失败(0)
  3. 2015:  Error:不能显示此视频模式,请将电脑显示输入设置为1440x900@60Hz(0)

说点什么

avatar

此站点使用Akismet来减少垃圾评论。了解我们如何处理您的评论数据

  Subscribe  
提醒