今日在生产系统上业务高峰期时遇到了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博客 ” ，链接：https://blog.itpub.net/10972173/viewspace-498447/ ，如需转载，请注明出处，否则将追究法律责任。
