mysql10649_Oracle数据库大量library cache: mutex X及latch: shared pool问题排查一例

业务系统数据库夯住，数据库内大量的library cache: mutex X及latch: shared pool等待，alert日志信息如下

Tue Sep 26 22:10:04 2017WARNING: inbound connection timed out (ORA-3136)

Tue Sep26 22:10:04 2017WARNING: inbound connection timed out (ORA-3136)

Tue Sep26 22:10:04 2017WARNING: inbound connection timed out (ORA-3136)

Tue Sep26 22:10:04 2017WARNING: inbound connection timed out (ORA-3136)

数据库历史session统计如下

201709262207 352 latch: shared pool 180

201709262207 library cache: mutex X 60

201709262207 476 library cache: mutex X 60

201709262207 6hurnha5k9qb6 352 latch: shared pool 60

201709262207 latch: shared pool 60

201709262207 0rs4yunhszr7w 352 latch: shared pool 60

201709262207 b7fy2a0snpja9 476 library cache: mutex X 60

201709262208 null event 300

201709262208 352 latch: shared pool 180

201709262208 0rs4yunhszr7w 352 latch: shared pool 60

201709262208 476 library cache: mutex X 60

201709262208 6hurnha5k9qb6 352 latch: shared pool 60

201709262208 b7fy2a0snpja9 476 library cache: mutex X 60

201709262208 latch: shared pool 60

201709262208 library cache: mutex X 60

201709262209 null event 326

201709262209 352 latch: shared pool 180

201709262209 latch: shared pool 60

201709262209 0rs4yunhszr7w 352 latch: shared pool 60

201709262209 b7fy2a0snpja9 476 library cache: mutex X 60

201709262209 6hurnha5k9qb6 352 latch: shared pool 60

201709262209 library cache: mutex X 60

201709262209 476 library cache: mutex X 60

阻塞会话明细

SQL> select event,sql_id,USER_ID,program from gV$active_session_history a where INST_ID=1 and SESSION_ID=476 and to_char(a.sample_time, 'yyyymmddHH24mi')=201709262203;

latch: shared pool 6hurnha5k9qb60 oracle@ZJHZ-px-xxx-39(J002)

latch: shared pool 6hurnha5k9qb60 oracle@ZJHZ-px-xxx-39(J002)

latch: shared pool 6hurnha5k9qb60 oracle@ZJHZ-px-xxx-39(J002)

latch: shared pool 6hurnha5k9qb60 oracle@ZJHZ-px-xxx-39(J002)

latch: shared pool 6hurnha5k9qb60 oracle@ZJHZ-px-xxx-39(J002)

latch: shared pool 6hurnha5k9qb60 oracle@ZJHZ-px-xxx-39(J002)

latch: shared pool 6hurnha5k9qb60 oracle@ZJHZ-px-xxx-39(J002)

latch: shared pool 6hurnha5k9qb60 oracle@ZJHZ-px-xxx-39(J002)

latch: shared pool 6hurnha5k9qb60 oracle@ZJHZ-px-xxx-39(J002)

SQL> select event,sql_id,USER_ID,program from gV$active_session_history a where INST_ID=1 and SESSION_ID=352and to_char(a.sample_time, 'yyyymmddHH24mi')=201709262203;

EVENT SQL_ID USER_ID PROGRAM------------------------------ ------------- ---------- ------------------------------------------------

0 oracle@ZJHZ-px-xxx-39(MMAN)0 oracle@ZJHZ-px-xxx-39(MMAN)0 oracle@ZJHZ-px-xxx-39(MMAN)0 oracle@ZJHZ-px-xxx-39(MMAN)0 oracle@ZJHZ-px-xxx-39(MMAN)0 oracle@ZJHZ-px-xxx-39(MMAN)0 oracle@ZJHZ-px-xxx-39(MMAN)0 oracle@ZJHZ-px-xxx-39(MMAN)0 oracle@ZJHZ-px-xxx-39(MMAN)0 oracle@ZJHZ-px-xxx-39(MMAN)0 oracle@ZJHZ-px-xxx-39(MMAN)0 oracle@ZJHZ-px-xxx-39 (MMAN)

MMAN进程是Oracle 10g引入用于进行内存管理的进程，在进行动态内存调整时，这个进程要发挥其作用,这个进程的作用是内部数据库任务的执行者：MMAN is used for internal database tasks.

MMAN to wait and post itself for satisfying an auto-tuned memory request while trying to fully free a component's quiesced granules. In Release 10.1, the name of this event was 'wait for SGA component shrink'.

在后台转储跟踪，可以看到MMAN进程的等待：

WAIT #0: nam='SGA: MMAN sleep for component shrink' ela= 10045 component id=6 current size=8 target size=7 obj#=-1 tim=9815936073WAIT #0: nam='SGA: MMAN sleep for component shrink' ela= 10581 component id=6 current size=8 target size=7 obj#=-1 tim=9815946809WAIT #0: nam='SGA: MMAN sleep for component shrink' ela= 10541 component id=6 current size=8 target size=7 obj#=-1 tim=9815957544WAIT #0: nam='SGA: MMAN sleep for component shrink' ela= 10655 component id=6 current size=8 target size=7 obj#=-1 tim=9815968290WAIT #0: nam='SGA: MMAN sleep for component shrink' ela= 10649 component id=6 current size=8 target size=7 obj#=-1 tim=9815979031

查询数据库最近内存调整记录

SQL> selectCOMPONENT,2STATUS,3OPER_TYPE,4OPER_MODE,5PARAMETER,6 round(INITIAL_SIZE/1024/1024,2) INITIAL_mb ,7 round(TARGET_SIZE/1024/1024,2) TARGET_MB,8 round(FINAL_SIZE/1024/1024,2) FINAL_mb,9 to_char(START_TIME, 'yyyy-mm-dd hh24:mi:ss') START_TIME,10 to_char(END_TIME, 'yyyy-mm-dd hh24:mi:ss') END_TIME11 fromV$MEMORY_RESIZE_OPS12 where START_TIME > to_date('2017092621','yyyymmddhh24')13 order byEND_TIME;

COMPONENT STATUS OPER_TYPE OPER_MODE PARAMETER INITIAL_MB TARGET_MB FINAL_MB START_TIME END_TIME------------------------------ --------- ------------- --------- ------------------------------ ---------- ---------- ---------- ------------------- -------------------

shared pool COMPLETE SHRINK DEFERRED shared_pool_size 2752 2624 2624 2017-09-26 22:01:21 2017-09-26 22:10:07

DEFAULT buffer cache COMPLETE GROW DEFERRED db_cache_size 1536 1664 1664 2017-09-26 22:01:21 2017-09-26 22:10:07

DEFAULT buffer cache COMPLETE SHRINK DEFERRED db_cache_size 1664 1536 1536 2017-09-26 22:23:11 2017-09-26 22:23:13shared pool COMPLETE GROW DEFERRED shared_pool_size2624 2752 2752 2017-09-26 22:23:11 2017-09-26 22:23:13

至此问题定位，是由于SGA内存自动调整导致数据库异常