ITPub博客

首页 > 数字化转型 > ERP > The Research of Latch(three)

The Research of Latch(three)

原创 ERP 作者:shiyihai 时间:2007-10-16 11:01:57 0 删除 编辑

三、查询热点块和热点对象

statspack报告中latch的竞争比较突出,见如下:
Latch Name Requests Misses Sleeps Sleeps 1->4
-------------------------- -------------- ----------- ----------- ------------
cache buffers chains 105,398,981 27,571 361 27232/317/22
NoWait Waiter
Latch Name Where Misses Sleeps Sleeps
------------------------ -------------------------- ------- ---------- --------
cache buffers chains kcbrls: kslbegin 0 290 7
cache buffers chains kcbgtcr: fast path 0 57 43
cache buffers chains kcbgtcr: kslbegin excl 0 13 308
cache buffers chains kcbzgb: scan from tail. no 0 1 0
通过查询v$session_event可以发现latch free事件

[@more@]

SQL> select event,count(*) from v$session_event where event like '%latch%' group by event having count(*)>10;
EVENT COUNT(*)
---------------------------------------------------------------- ----------
latch free 43

1 rows selected.
SQL> select latch#,name,gets,misses,sleeps from v$latch where name like 'cache buffer%';

LATCH# NAME GETS MISSES SLEEPS
---------- ---------------------------------------------------------------- ---------- ---------- ----------
93 cache buffers lru chain 31237794 20489 273
98 cache buffers chains 1.6175E+11 65038428 505897
99 cache buffer handles 3979454 6 0

SQL> select addr,name,misses from v$latch_children where misses>500000 and name='cache buffers chains' order by misses desc;

ADDR NAME MISSES
---------------- ---------------------------------------------------------------- ----------
C0000000996CCAE8 cache buffers chains 52579647
C000000099492038 cache buffers chains 2768666
C0000000996A68A0 cache buffers chains 2538949
C00000009948C290 cache buffers chains 539535

SQL> select bh.addr,obj.name obj_name,bh.tch touch from x$bh bh,sys.file$ f,v$datafile fl,sys.obj$ obj,sys.ts$ ts
where fl.file#=f.file# and bh.file#=fl.file# and obj.dataobj#=bh.obj and bh.ts#=ts.ts#
and bh.HLADDR='C0000000996CCAE8' and bh.tch>0 order by bh.tch desc; 2 3

ADDR OBJ_NAME TOUCH
---------------- ------------------------------ ----------
800003FB80075598 INDX_T_R_REF_GOODSID 18268
800003FB80075598 SYS_C003928 4667
800003FB80075598 T_PPS_PRESENT_LOGIN_ID 12
800003FB80075598 INDEX_PPS_AWARD_MSISDN 10
800003FB80075598 T_ACTIONLOG 5
800003FB80075598 T_R_GCONTENT 5
800003FB80075598 IDX_MOFEATURESTR_OPERTYPE 3
800003FB80075598 USER_INFO 2
800003FB80075598 T_FILE_CONTENT_BACKUP 1
800003FB80075598 T_ACTIONLOG 1
800003FB80075598 T_ACTIONLOG 1

ADDR OBJ_NAME TOUCH
---------------- ------------------------------ ----------
800003FB80075598 WAP_SERVICE 1
800003FB80075598 MOFEATURESTR 1

13 rows selected.
发觉索引INDX_T_R_REF_GOODSID和SYS_C003928形成热点对象。可以考虑keep到cache中来解决IO的问题。
alter index momax.INDX_T_R_REF_GOODSID storage(buffer_pool keep);
alter index momax.SYS_C003928 storage(buffer_pool keep);

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

请登录后发表评论 登录
全部评论
  • 博文量
    235
  • 访问量
    1667636