Oracle：有关监控的一些SQL（一）

本文初步陈列一些与数据库状态监控有关的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。

感谢支持

赏