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。

打赏

微信扫一扫，打赏作者吧～

历史上的今天:

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