ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 一例cache buffers chain等待诊断思路

一例cache buffers chain等待诊断思路

原创 Linux操作系统 作者:lsq_008 时间:2008-11-21 21:50:27 0 删除 编辑

    今日在生产系统上业务高峰期时遇到了latch free等待,top 5等待如下:

Top 5 Timed Events
~~~~~~~~~~~~~~~~~~                                                     % Total
Event                                               Waits    Time (s) Ela Time
-------------------------------------------- ------------ ----------- --------
latch free                                        897,198      76,481    65.08
CPU time                                                       24,725    21.04
db file sequential read                         3,016,619       7,965     6.78
log file sync                                   2,874,499       5,159     4.39
log file parallel write                         4,281,451       1,755     1.49

进一步查看具体的latch等待情况,发现cache buffers chains的latch等待严重:

cache buffers chains        931,260,976    1.3    0.0  80306   20,183,335    0.2

经过到pub上查出一堆解决方法,最后步骤如下:

(1)查找misses较高的latch地址:
SQL>  select * from (select addr,child#,name,sleeps,misses,wait_time from  v$latch_children order by 6 desc ) where rownum<10;

ADDR                 CHILD# NAME                                                                 SLEEPS     MISSES  WAIT_TIME
---------------- ---------- ---------------------------------------------------------------- ---------- ---------- ----------
C00000037014E570      93624 cache buffers chains                                                5246160  109284261 2.7435E+11
C0000004415B3840          4 shared pool                                                         1404244   28474410  493984918
C0000003719F0458         67 library cache                                                       1081725   46060551  415043701
C00000036EB87278      66927 cache buffers chains                                                1145494   57692776  366726011
C0000004415B3610          2 shared pool                                                          927865   14205641  350796759
C0000004415B3728          3 shared pool                                                          660872    8912208  284980457
C0000003719F2528         37 library cache                                                        147978    4773082  214295538
C0000004415B3958          5 shared pool                                                          415133    5984237  207155525
C0000004415B3A70          6 shared pool                                                          443142    5731340  201941102

9 rows selected.

(2)可以看出,地址为C00000037014E570的latch的misses很高,从x$bh中查找该latch保护的block地址

SQL> select addr,ts#,file#,dbarfil,dbablk,tch,hladdr from x$bh where hladdr='C00000037014E570';

ADDR                    TS#      FILE#    DBARFIL     DBABLK        TCH HLADDR
---------------- ---------- ---------- ---------- ---------- ---------- ----------------
800003FB80070330         25        124        124     331005          1 C00000037014E570
800003FB800701F8         25        124        124     331005         50 C00000037014E570
800003FA00140270         25        124        124     331005          1 C00000037014E570
800003FA00140138         25        124        124     331005          1 C00000037014E570
800003FA00130270         25        124        124     331005          1 C00000037014E570
800003FA00130138         25        124        124     331005          1 C00000037014E570
800003FA00120270         25        124        124     331005          1 C00000037014E570
800003FA00120138         25        124        124     331005          1 C00000037014E570
800003FA00110270         25        124        124     331005          1 C00000037014E570
800003FA00110138         25        124        124     331005          1 C00000037014E570
800003FA00100270         25        124        124     331005          1 C00000037014E570
800003FA00100138         25        124        124     331005          1 C00000037014E570
800003FA000F0270         25        124        124     331005          1 C00000037014E570
800003FA000F0138         25        124        124     331005          1 C00000037014E570
800003FA000E0270         25        124        124     331005          1 C00000037014E570
800003FA000E0138         25        124        124     331005          1 C00000037014E570
800003FA000D0270         25        124        124     331005          1 C00000037014E570
800003FA000D0138         25        124        124     331005          1 C00000037014E570
800003FA000C0270         25        124        124     331005          1 C00000037014E570
800003FA000C0138         25        124        124     331005          1 C00000037014E570
800003FA000B0270         25        124        124     331005          1 C00000037014E570
800003FA000B0138         25        124        124     331005          1 C00000037014E570
800003FA000A0270         25        124        124     331005          1 C00000037014E570
800003FA000A0138         25        124        124     331005          1 C00000037014E570
800003FA00090270         25        124        124     331005          1 C00000037014E570
800003FA00090138         25        124        124     331005          1 C00000037014E570
800003FA00080270         25        124        124     331005          1 C00000037014E570
800003FA00080138         25        124        124     331005          1 C00000037014E570
800003FA00070270         25        124        124     331005          1 C00000037014E570
800003FA00070138         25        124        124     331005          1 C00000037014E570
800003FA00060270         25        124        124     331005          1 C00000037014E570
800003FA00060138         25        124        124     331005          1 C00000037014E570
800003FA00050270         25        124        124     331005          1 C00000037014E570
800003FA00050138         25        124        124     331005          1 C00000037014E570
800003FA00040270         25        124        124     331005          1 C00000037014E570
800003FA00040138         25        124        124     331005          1 C00000037014E570
800003FA00030270         25        124        124     331005          1 C00000037014E570
800003FA00030138         25        124        124     331005          1 C00000037014E570
800003FA00020270         25        124        124     331005          1 C00000037014E570
800003FA00020138         25        124        124     331005          1 C00000037014E570
800003FA00010270         25        124        124     331005          1 C00000037014E570
800003FA00010138         25        124        124     331005          1 C00000037014E570
800003FA00000270         25        124        124     331005          1 C00000037014E570
800003FA00000138         25        124        124     331005          1 C00000037014E570
800003FA401F0270         25        124        124     331005          1 C00000037014E570
800003FA401F0138         25        124        124     331005          1 C00000037014E570
800003FA401E0270         25        124        124     331005          1 C00000037014E570
800003FA401E0138         25        124        124     331005          1 C00000037014E570
800003FA401D0270         25        124        124     331005          1 C00000037014E570
800003FA401D0138         25        124        124     331005          1 C00000037014E570
800003FA401C0270         25        124        124     331005          1 C00000037014E570
800003FA401C0138         25        124        124     331005          1 C00000037014E570
800003FA401B0270         25        124        124     331005          1 C00000037014E570
800003FA401B0138         25        124        124     331005          1 C00000037014E570
800003FA401A0270         25        124        124     331005          1 C00000037014E570
800003FA401A0138         25        124        124     331005          1 C00000037014E570
800003FA40190270         25        124        124     331005          1 C00000037014E570
800003FA40190138         25        124        124     331005          1 C00000037014E570
800003FA40180270         25        124        124     331005          1 C00000037014E570
800003FA40180138         25        124        124     331005          1 C00000037014E570
800003FA40170270         25        124        124     331005          1 C00000037014E570
800003FA40170138         25        124        124     331005          1 C00000037014E570
800003FA40160270         25        124        124     331005          1 C00000037014E570
800003FA40160138         25        124        124     331005          1 C00000037014E570
800003FA40150270         25        124        124     331005          1 C00000037014E570
800003FA40150138         25        124        124     331005          1 C00000037014E570
800003FA40140270         25        124        124     331005          1 C00000037014E570
800003FA40140138         25        124        124     331005          1 C00000037014E570
800003FA40130270         25        124        124     331005          1 C00000037014E570
800003FA40130138         25        124        124     331005          1 C00000037014E570
800003FA40120270         25        124        124     331005          1 C00000037014E570
800003FA40120138         25        124        124     331005          1 C00000037014E570
800003FA40110270         25        124        124     331005          1 C00000037014E570
800003FA40110138         25        124        124     331005          1 C00000037014E570
800003FA40100270         25        124        124     331005          1 C00000037014E570
800003FA40100138         25        124        124     331005          1 C00000037014E570
800003FA400F0270         25        124        124     331005          1 C00000037014E570
800003FA400F0138         25        124        124     331005          1 C00000037014E570
800003FA400E0270         25        124        124     331005          1 C00000037014E570
800003FA400E0138         25        124        124     331005          1 C00000037014E570
800003FA400D0270         25        124        124     331005          1 C00000037014E570
800003FA400D0138         25        124        124     331005          1 C00000037014E570
800003FA400C0270         25        124        124     331005          1 C00000037014E570
800003FA400C0138         25        124        124     331005          1 C00000037014E570
800003FA400B0270         25        124        124     331005          1 C00000037014E570
800003FA400B0138         25        124        124     331005          1 C00000037014E570
800003FB80070330         25         52         52     471579          6 C00000037014E570
800003FB80070330         15         33         33      72605          5 C00000037014E570
800003FB800701F8         15         29         29      36724          2 C00000037014E570
800003FA00140270         31         76         76     162577          0 C00000037014E570
800003FB80070330         15        178        178      96176          0 C00000037014E570
800003FB80070330         25        145        145     421913          1 C00000037014E570
800003FB80070330         25        124        124      26286         27 C00000037014E570

93 rows selected.

(3)可见,有相当数量的文件号为124,block号为331005的block。继续查找文件号124,block号331005的块位于哪个segment上:

SQL> select owner,segment_name,segment_type from dba_extents
    where relative_fno=124
    and &id between block_id and block_id+blocks;
Enter value for id: 331005
old   3: and &id between block_id and block_id+blocks
new   3: and 331005 between block_id and block_id+blocks

OWNER                          SEGMENT_NAME                                                                      SEGMENT_TYPE
------------------------------ --------------------------------------------------------------------------------- ------------------
SCOTT                          SYS_C0021700 2                                                                     INDEX

SQL> select owner,table_name,index_name from dba_indexes where index_name='SYS_C0021700';

OWNER                          TABLE_NAME                     INDEX_NAME
------------------------------ ------------------------------ ------------------------------
SCOTT                          TTTT                              SYS_C00217002

SQL> select name,blocks,lf_rows,lf_blks,br_rows,br_blks,pct_used,used_space from index_stats;

NAME                               BLOCKS    LF_ROWS    LF_BLKS    BR_ROWS    BR_BLKS   PCT_USED USED_SPACE
------------------------------ ---------- ---------- ---------- ---------- ---------- ---------- ----------
SYS_C00217002                           24        908          3          2          1         57      18190

至此,发生cache buffer chains等待热点块已经确认,在结合查看statsapck报告上的top sql,发现一条与该热点块相关的sql:

     11,201,598    2,298,222            4.9    2.5   528.45  76831.45 2390675032
Module: JDBC Thin Client

select FMT from TTTT  where col1 = RPAD(:1,6) and col2
= :
2

   显然,此sql执行次数相当高,切elapsd time远远超过了cpu time,估计大量的时间被耗费在latch 的等待上了。

  最后,经过与开发沟通,在应用级对该sql做了优化,以后业务恢复正常。

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

请登录后发表评论 登录
全部评论
十余年大型金融及电信系统数据库管理经验,曾服务于中国建设银行、中国移动。对oracle,mysql数据库有深入了解。 擅长python开发,独立开发了开源数据库自动化监控运维平台Power Monitor。

注册时间:2008-02-29

  • 博文量
    325
  • 访问量
    1241921