ITPub博客

首页 > Linux操作系统 > Linux操作系统 > latch问题诊断相关查询

latch问题诊断相关查询

原创 Linux操作系统 作者:anchen211 时间:2009-06-26 20:28:42 0 删除 编辑

 If the sessions remain connected for a long time, then the output from the query of the following would be helpful in determining the sessions that have high waits for the latch free event

 SQL> SELECT sid, substr(event,1,12) "Event",

  2  time_waited, round(average_wait,2) "Average"
  3  FROM v$session_event
  4  WHERE event ='latch free'
  5  AND time_waited != 0
  6  ORDER BY time_waited DESC;
 
Determine which latch has the greatest impact
 
SQL> SELECT latch#, substr(name,1,25) "Latch", sleeps
  2  FROM   v$latch
  3  WHERE  sleeps!=0
  4  ORDER  BY sleeps desc;
 

Collect information for the corresponding child latches.

 

SQL> SELECT addr, gets, misses, sleeps, spin_gets, sleep1,
  2        sleep2, sleep3
  3 FROM   v$latch_children
  4 WHERE  latch#=97
  5 ORDER  BY sleeps DESC;

Find out which buffer makes use of the hot latch

 

SQL> SELECT hladdr, dbarfil, dbablk, class, state, TCH
  2  FROM  x$bh
  3  WHERE hladdr IN
  4 ('8025A108','802514C4','80259CA0')
  5  ORDER BY TCH DESC;
 
Note: x$bh has a column called obj that can be mapped to the object_id  column of the dba_objects view.
When issuing the above query, consider capturing the same query into multiple tables at different time intervals and then comparing the results. This will identify the truly “hot” blocks over that time interval, and not just at a particular instant.

Identify the segments that are associated with these blocks:

 

SQL> SELECT owner, segment_name "Name", segment_type "Type"
  2  FROM   dba_extents
  3  WHERE  file_id = 2
  4  AND    block_id <= 293
  5  AND    block_id + blocks > 293;
 
总结:
 

  Monitor latches at higher levels first and focus on the latches with the biggest impact.

  If it is a solitary latch, then reduce the load on it.

  If contention is on a child latch, then:

          Identify the hot resource

          Eliminate or reduce the load on the hot resource

  Advanced Monitoring

          Tune spin count

          Tune latch wait posting

 

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

上一篇: 寻找热块
下一篇: 调优笔记一
请登录后发表评论 登录
全部评论

注册时间:2008-11-04

  • 博文量
    129
  • 访问量
    175148