Oracle - 博客园

2009年3月12日

摘要： Most blocking lock detection scripts fail to consider that processes waiting for, but not yet holding, a lock can block other processes that need a conflicting lock on the same resource. To resolve su... 阅读全文

posted @ 2009-03-12 09:14 Oracle 阅读(303) 评论(0) 推荐(0)

2009年3月11日

latch_sleeps.sql

摘要： If there are latch free waits, then this script can be used to further diagnose the problem. For each latch type, it reports the estimated impact of sleeps, and the number of sleeps per get. It also r... 阅读全文

posted @ 2009-03-11 18:06 Oracle 阅读(226) 评论(0) 推荐(0)

latch_spin.sql

摘要： This script compares spin gets to sleep gets as an indicator of the effectiveness of spinning, for each latch type. column name format a39 heading "LATCH TYPE" column spin_gets ... 阅读全文

posted @ 2009-03-11 17:39 Oracle 阅读(216) 评论(0) 推荐(0)

latch_get.sql

摘要： This script reports the breakdown of willing-to-wait gets for each latch type, into simple gets, spin gets and sleep gets. Spin gets and sleep gets are latch gets that require spinning or sleeping res... 阅读全文

posted @ 2009-03-11 17:28 Oracle 阅读(233) 评论(0) 推荐(0)

latch_types.sql

摘要： This scripts prints a list of the latch types, and reports the number of child latches for each type. If a latch type has multiple child latches, V$LATCH_CHILDREN can be used to determine whether ac... 阅读全文

posted @ 2009-03-11 16:44 Oracle 阅读(238) 评论(0) 推荐(0)

trace_waits.sql

摘要： This script is one of our favorites. It finds the top N sessions that have been affected by a particular type of resource wait, and enables event 10046, level 8 in those sessions for the specified per... 阅读全文

posted @ 2009-03-11 16:25 Oracle 阅读(223) 评论(0) 推荐(0)

session_waits.sql

摘要： select e.event, e.time_waited from sys.v_$session_event e where e.sid = &Sid union all select n.name, s.value from sys.v_$statname n, sys.v_$sesstat s where s.sid = &Sid and n.s... 阅读全文

posted @ 2009-03-11 16:21 Oracle 阅读(185) 评论(0) 推荐(0)

resource_waits.sql

摘要： reselect substr(e.event, 1, 40) event, e.time_waited, e.time_waited / decode( e.event, 'latch free', e.total_waits, decode( e.total_waits - e.total_timeouts, 0, 1, ... 阅读全文

posted @ 2009-03-11 16:18 Oracle 阅读(195) 评论(0) 推荐(0)

routine_waits.sql

摘要： select substr(e.event, 1, 40) event, e.average_wait from sys.v_$system_event e, sys.v_$instance i where e.event = 'DFS lock handle' or e.event = 'rdbms ipc reply' or e.event like 'SQL... 阅读全文

posted @ 2009-03-11 16:17 Oracle 阅读(212) 评论(0) 推荐(0)

all_parameters.sql

摘要： select x.ksppinm name, y.kspftctxvl value, y.kspftctxdf isdefault, decode(bitand(y.kspftctxvf,7),1,'MODIFIED',4,'SYSTEM_MOD','FALSE') ismod, decode(bitand(y.kspftctxvf,2),2,'TRUE','FALSE... 阅读全文

posted @ 2009-03-11 16:02 Oracle 阅读(150) 评论(0) 推荐(0)