Oracle数据库数据恢复、性能优化»论坛 › Oracle › Oracle数据库管理 › 如何用SQL语法获取oracle实例占用的服务器CPU,内存及IO ...

0 积分	0 好友	8 主题

发消息

[RAC Clusterware] 如何用SQL语法获取oracle实例占用的服务器CPU,内存及IO？

1^#

发表于 2013-3-26 16:21:24 | 查看: 7064| 回复: 6

请教，如何用SQL语法获取oracle实例占用的服务器CPU,内存及IO的情况？需要用到哪些视图呢？

分享0

收藏0 回复只看该作者道具举报

s.o.m

2^#

发表于 2013-3-26 16:40:46

select instance_number,
snap_id,
min(begin_time) as 快照开始时间,
max(end_time) as 快照停止时间,
round(sum(case metric_name
when 'Host CPU Utilization (%)' then
average
end)) as CPU使用率,
sum(case metric_name
when 'Current OS Load' then
MAXVAL
end) as 最大OS负载,
sum(case metric_name
when 'Current OS Load' then
average
end) as 平均OS负载,
round(sum(case metric_name
when 'User Transaction Per Sec' then
average
end)) as 每秒事务数,
round(sum(case metric_name
when 'Executions Per Sec' then
average
end)) as 每秒SQL执行次数,
round(sum(case metric_name
when 'Logical Reads Per Sec' then
average
end)) as 每秒逻辑读总量,
round(sum(case metric_name
when 'Physical Read Total IO Requests Per Sec' then
average
end)) as 物理读IOPS,
round(sum(case metric_name
when 'Physical Write Total IO Requests Per Sec' then
average
end)) as 物理写IOPS,
round(sum(case metric_name
when 'Redo Writes Per Sec' then
average
end)) as REDO_IOPS,
round(sum(case metric_name
when 'Physical Read Total Bytes Per Sec' then
average / 1024 / 1024
end)) as 物理读MBPS,
round(sum(case metric_name
when 'Physical Write Total Bytes Per Sec' then
average / 1024 / 1024
end)) as 物理写MBPS,
round(sum(case metric_name
when 'Redo Generated Per Sec' then
average / 1024 / 1024
end)) as REDO_MBPS,
round(sum(case metric_name
when 'Network Traffic Volume Per Sec' then
average / 1024 / 1024
end)) as 每秒网络流量_MB
from dba_hist_sysmetric_summary t
where t.instance_number in (1 /*, 2*/)
and begin_time >= trunc(sysdate)
group by instance_number, snap_id
order by snap_id desc, instance_number;

复制代码

供参考！

回复只看该作者道具举报

lxqin117

3^#

发表于 2013-3-26 16:57:11

首先先谢谢啦，再问能不能获取即时性的呢？查询当前的CPU值，内存值？

回复只看该作者道具举报

dla001

4^#

发表于 2013-3-26 17:16:04

http://www.askmaclean.com/archives/recommend-sql-dashboard-for-rac.html

oracle top

回复只看该作者道具举报

Maclean Liu(刘相兵

5^#

发表于 2013-3-26 18:48:10

看一个瞬间值有什么具体意义呢

回复只看该作者道具举报

lxqin117

6^#

发表于 2013-4-1 09:04:07

想做个性能监视器，所以先获取即时值，谢谢

回复只看该作者道具举报

tiejiang2009

7^#

发表于 2013-4-3 13:59:44

2楼的脚本厉害！

回复只看该作者道具举报

返回列表

		自动登录	找回密码
密码			注册