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

时间：2022-12-24 07:58:48

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

Tue Sep  ::

WARNING: inbound connection timed out (ORA-)

Tue Sep  ::

WARNING: inbound connection timed out (ORA-)

Tue Sep  ::

WARNING: inbound connection timed out (ORA-)

Tue Sep  ::

WARNING: inbound connection timed out (ORA-)

数据库历史session统计如下

                                               latch: shared pool

                                                  library cache: mutex X

                                               library cache: mutex X

                   6hurnha5k9qb6               latch: shared pool

                                                  latch: shared pool

                   0rs4yunhszr7w               latch: shared pool

                   b7fy2a0snpja9               library cache: mutex X

                                                  null event

                                               latch: shared pool

                   0rs4yunhszr7w               latch: shared pool

                                               library cache: mutex X

                   6hurnha5k9qb6               latch: shared pool

                   b7fy2a0snpja9               library cache: mutex X

                                                  latch: shared pool

                                                  library cache: mutex X

                                                  null event

                                               latch: shared pool

                                                  latch: shared pool

                   0rs4yunhszr7w               latch: shared pool

                   b7fy2a0snpja9               library cache: mutex X

                   6hurnha5k9qb6               latch: shared pool

                                                  library cache: mutex X

                                               library cache: mutex X

阻塞会话明细

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

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

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

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

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

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

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

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

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

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

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

EVENT                          SQL_ID           USER_ID PROGRAM

------------------------------ ------------- ---------- ------------------------------------------------

                                                       oracle@ZJHZ-px-xxx- (MMAN)

                                                       oracle@ZJHZ-px-xxx- (MMAN)

                                                       oracle@ZJHZ-px-xxx- (MMAN)

                                                       oracle@ZJHZ-px-xxx- (MMAN)

                                                       oracle@ZJHZ-px-xxx- (MMAN)

                                                       oracle@ZJHZ-px-xxx- (MMAN)

                                                       oracle@ZJHZ-px-xxx- (MMAN)

                                                       oracle@ZJHZ-px-xxx- (MMAN)

                                                       oracle@ZJHZ-px-xxx- (MMAN)

                                                       oracle@ZJHZ-px-xxx- (MMAN)

                                                       oracle@ZJHZ-px-xxx- (MMAN)

                                                       oracle@ZJHZ-px-xxx- (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 #: nam='SGA: MMAN sleep for component shrink' ela=  component id= current size= target size= obj#=- tim=

WAIT #: nam='SGA: MMAN sleep for component shrink' ela=  component id= current size= target size= obj#=- tim=

WAIT #: nam='SGA: MMAN sleep for component shrink' ela=  component id= current size= target size= obj#=- tim=

WAIT #: nam='SGA: MMAN sleep for component shrink' ela=  component id= current size= target size= obj#=- tim=

WAIT #: nam='SGA: MMAN sleep for component shrink' ela=  component id= current size= target size= obj#=- tim=

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

SQL> select COMPONENT,

  2         STATUS,

  3         OPER_TYPE,

  4         OPER_MODE,

  5         PARAMETER,

  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_TIME

11    from V$MEMORY_RESIZE_OPS

12   where START_TIME > to_date('','yyyymmddhh24')

13   order by END_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:13

shared pool                    COMPLETE  GROW          DEFERRED  shared_pool_size                     2624       2752       2752 2017-09-26 22:23:11 2017-09-26 22:23:13

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

相关文章