ITPub博客

首页 > 数据库 > 数据库开发技术 > Script: resource_waits.sql

Script: resource_waits.sql

原创 数据库开发技术 作者:OmarChina 时间:2007-10-22 23:38:05 0 删除 编辑
-- Script: resource_waits.sql
-- Purpose: to show the total waiting time for resource types
--
-- Copyright: (c) 1998 Ixora Pty Ltd
-- Author: Steve Adams[@more@]

-------------------------------------------------------------------------------
--
-- Script: resource_waits.sql
-- Purpose: to show the total waiting time for resource types
--
-- Copyright: (c) 1998 Ixora Pty Ltd
-- Author: Steve Adams
--
-------------------------------------------------------------------------------
@reset_sqlplus

column average_wait format 9999990.00

select
substr(e.event, 1, 40) event,
e.time_waited,
e.time_waited / (
e.total_waits - decode(e.event, 'latch free', 0, e.total_timeouts)
) average_wait
from
sys.v_$system_event e,
sys.v_$instance i
where
e.event = 'buffer busy waits' or
e.event = 'enqueue' or
e.event = 'free buffer waits' or
e.event = 'global cache freelist wait' or
e.event = 'latch free' or
e.event = 'log buffer space' or
e.event = 'parallel query qref latch' or
e.event = 'pipe put' or
e.event = 'write complete waits' or
e.event like 'library cache%' or
e.event like 'log file switch%' or
( e.event = 'row cache lock' and
i.parallel = 'NO'
)
union all
select
'non-routine log file syncs',
round(e.average_wait * greatest(e.total_waits - s.value, 0)),
e.average_wait
from
sys.v_$system_event e,
sys.v_$sysstat s
where
e.event = 'log file sync' and
s.name = 'user commits'
order by
2 desc
/

@reset_sqlplus

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/9925929/viewspace-978076/,如需转载,请注明出处,否则将追究法律责任。

下一篇: american slang(5)
请登录后发表评论 登录
全部评论
  • 博文量
    68
  • 访问量
    997213