High ‘latch: shared pool’ and ‘library cache: mutex X’ in Oracle 19c（19.13）

好久没有处理oracle的故障了，因为我的客户在oracle 升级19c后的数据库非常稳定，前几年随着X创推进，几乎oracle都被国产化替代了，刚好最近遇到了一个问题，有家医院客户，最近每隔几天早高峰会出现较高的’latch: shared pool’ 和’library cache: mutex X’，导致数据库节点hang, 等待函数 kghfrunp ，重启解决，如果发现的早做flush sharedpool也可以缓解。数据库版本19.13.

latch: shared pool 和 library cache: mutex X 本质上是shared pool的争用。

先看awr

DB Name	DB Id	Unique Name	Role	Edition	Release	RAC	CDB
ORAxx	1978474438	oraxx	PRIMARY	EE	19.0.0.0.0	YES	NO

Instance	Inst Num	Startup Time	User Name	System Data Visible
oraxx	2	07-Jul-26 12:56	SYS	YES

Host Name	Platform	CPUs	Cores	Sockets	Memory (GB)
anbob2	AIX-Based Systems (64-bit)	160	20		998.25

	Snap Id	Snap Time	Sessions	Cursors/Session	Instances
Begin Snap:	150686	09-Jul-26 09:30:49	2507	2.1	2
End Snap:	150687	09-Jul-26 10:00:05	2658	2.2	2
Elapsed:		29.26 (mins)
DB Time:		8,964.26 (mins)

Report Summary

Load Profile

	Per Second	Per Transaction	Per Exec	Per Call
DB Time(s):	306.3	3.9	0.14	0.08
DB CPU(s):	4.3	0.1	0.00	0.00
Background CPU(s):	0.3	0.0	0.00	0.00
Redo size (bytes):	355,712.5	4,549.2
Logical read (blocks):	1,387,449.5	17,744.0
Block changes:	2,307.8	29.5
Physical read (blocks):	19,421.9	248.4
Physical write (blocks):	3,675.8	47.0
Read IO requests:	8,090.6	103.5
Write IO requests:	152.0	1.9
Read IO (MB):	151.7	1.9
Write IO (MB):	28.7	0.4
IM scan rows:	0.0	0.0
Session Logical Read IM:	0.0	0.0
Global Cache blocks received:	336.7	4.3
Global Cache blocks served:	251.9	3.2
User calls:	3,937.2	50.4
Parses (SQL):	1,304.9	16.7
Hard parses (SQL):	92.0	1.2
SQL Work Area (MB):	47.8	0.6
Logons:	9.5	0.1
User logons:	8.4	0.1
Executes (SQL):	2,198.1	28.1
Rollbacks:	2.8	0.0
Transactions:	78.2

Top 10 Foreground Events by Total Wait Time

Event	Waits	Total Wait Time (sec)	Avg Wait	% DB time	Wait Class
latch: shared pool	173,268	246.1K	1420.50ms	45.8	Concurrency
library cache: mutex X	91,005	98.3K	1080.17ms	18.3	Concurrency
DB CPU		7509.4		1.4
latch: call allocation	3,701	7454.7	2014.24ms	1.4	Other
library cache load lock	6,795	5707.7	839.98ms	1.1	Concurrency
db file sequential read	8,768,779	3400.3	387.77us	.6	User I/O
row cache lock	9,258	2104.2	227.28ms	.4	Concurrency
library cache: bucket mutex X	16,754	2048.5	122.27ms	.4	Concurrency
gc buffer busy acquire	3,043,490	1971.6	647.81us	.4	Cluster
row cache mutex	300,155	1751	5.83ms	.3	Concurrency

Host CPU

CPUs	Cores	Sockets	Load Average Begin	Load Average End	%User	%System	%WIO	%Idle
160	20		9.66	25.67	8.7	1.7	0.7	89.6

Instance CPU

%Total CPU	%Busy CPU	%DB time waiting for CPU (Resource Manager)
2.9	27.6	0.0

Note: 从上面可以发现数据库出现了非常高的性能负载问题，但是看sql parse和logon 并不高或导致这么严重问题的地步。并且主机的CPU使用率也并不高。

Latch Sleep Breakdown

• ordered by misses desc

Latch Name	Get Requests	Misses	Sleeps	Spin Gets
cache buffers chains	4,840,975,073	11,165,535	755,557	10,476,003
shared pool	52,028,140	492,124	4,714,323	273,603
ges resource hash list	124,844,804	373,696	6,911	366,865

Latch Miss Sources

• only latches with sleeps are shown
• ordered by name, sleeps desc

Latch Name	Where	NoWait Misses	Sleeps	Waiter Sleeps
unknown latch	kghfrunp: alloc: wait	0	4,526,661	526,884
unknown latch	kghalo	0	116,910	3,048,314
unknown latch	kghfre	0	25,182	642,385
unknown latch	kghalp	0	16,261	285,663
unknown latch	kghfrunp: no latch	0	15,818	0

kghfrunp==> kernel generic heap manager free unused space in a heap free unpinned space

kghalo==>kernel generic heap manager main allocation entry point

Mutex Sleep Summary

• ordered by number of sleeps desc

Mutex Type	Location	Sleeps	Wait Time (ms)
Library Cache	kglhdgn2 106	7,593,655	72,536,578
Cursor Pin	kkslce [KKSCHLPIN2]	1,555,951	1,598,425
Library Cache	kgllkdl1 85	1,441,434	13,616,083
Library Cache	kgllkal1 80	567,219	5,332,435
Row Cache	[19] kqrpre	207,889	1,175,250

ASH

Top Event P1/P2/P3 Values

• Top Events by DB Time and the top P1/P2/P3 values for those events.
• % Event is the percentage of DB Time due to the event
• % Activity is the percentage of DB Time due to the event with the given P1,P2,P3 Values.

Event	% Event	P1, P2, P3 Values	% Activity	Parameter 1	Parameter 2	Parameter 3
latch: shared pool	52.20	“504403158276130312”,”619″,”0″	6.45	address	number	why
latch: shared pool		“504403158276130152”,”619″,”0″	6.33	address	number	why
latch: shared pool		“504403158276129992”,”619″,”0″	5.77	address	number	why
library cache: mutex X	19.51	“991821498”,”51586852192256″,”18446744069419106410″	1.71	idn	value	where
library cache: mutex X		“1696937788”,”30769145708544″,”7288292302806581354″	1.31	idn	value	where
library cache: mutex X		“991821498”,”6287832121344″,”18446744069419106410″	1.07	idn	value	where
latch: call allocation	2.72	“504403158265523224”,”24″,”0″	0.95	address	number	why
row cache mutex	1.84	“10”,”19″,”0″	0.84	cache id	where requested	NOT DEFINED
gc buffer busy acquire	1.13	“188”,”1561563″,”1″	0.03	file#	block#	class#

一个堵塞者可能会导致2种等待事件latch: shared pool 和library cache: mutex X, 看https://ksun-oracle.blogspot.com/2021/05/oracle-19c-new-shared-pool-so-private.html

latch: shared pool

查询v$session 可以blocking_session， 或查询V$LATCHHOLDER

library cache: mutex X的p1和p2值 找 library object和堵塞者。 如p1 p2 p3 “991821498”,”51586852192256″,”18446744069419106410″

select mutex_identifier, sleep_timestamp, mutex_type, gets, sleeps
                ,requesting_session, blocking_session, location
   from v$mutex_sleep_history
  where mutex_identifier in (991821498) and sleep_timestamp > sysdate-1/1440 and sleeps > 3
  order by sleep_timestamp desc, location;

-- waiters
 select chain_signature, sid, p1 
   from v$wait_chains where p1 in (991821498) or chain_signature = 'library cache: mutex X';

select 'BLOCKING_SESSION'   sess, program, event, mod(s.p1, power(2, 17)) "buckt muext(child_latch)", s.p1, s.p2, s.p3, s.sql_id, q.sql_text, m.*, s.*, q.*
from v$session s, v$mutex_sleep_history m, v$sqlarea q
where s.sid = m.blocking_session and s.sql_id = q.sql_id and m.sleep_timestamp > sysdate-5/1440 and m.sleeps > 3
union all
select 'REQUESTING_SESSION' sess, program, event, mod(s.p1, power(2, 17)) "buckt muext(child_latch)", s.p1, s.p2, s.p3, s.sql_id, q.sql_text, m.*, s.*, q.*
from v$session s, v$mutex_sleep_history m, v$sqlarea q
where s.sid = m.requesting_session and s.sql_id = q.sql_id and m.sleep_timestamp > sysdate-5/1440 and m.sleeps > 3;

-- find obj,  ADDR is its object_handle
 select hash_value, addr, owner, name, namespace, type from v$db_object_cache where hash_value in (991821498)

HASH_VALUE ADDR             OWNER                          NAME                                               NAMESPACE                     TYPE
---------- ---------------- ------------------------------ -------------------------------------------------- ----------------------------- ----------
 991821498 00000000FA5A4168                                RECO.ORACLE.COM                                    DBLINK                        CURSOR

-- find blocker
SQL> @dec 51586852192256

                                DEC                  HEX
----------------------------------- --------------------
              51586852192256.000000         2EEB00000000

SQL> @hex 2EEB

                                DEC                  HEX
----------------------------------- --------------------
                       12011.000000                 2EEB

LMHB trace 文件

Dumping process info for latch holder 34735110
(latch info) hold_bits=0x40 ud_influx=0x7e
  Waiting For:
    0x700000000a244c8 Child 'shared pool' (level=10, SGA latch, child#=2)
    Holder Location: kgh.h LINE:9698 ID:kghfrunp: alloc: wait: 
    Holder Context: 0
    Waiter Location: kgh.h LINE:9637 ID:kghfre: Chunk Header
...

Short stack dump: 
ksedsts()+512<-ksdxfstk()+68<-ksdxcb()+892<-sspuser()+240<-__sighandler()<-thread_wait()+556<-sskgpwwait()+56<-skgpwwait()+268<-IPRA.$kslges()+1480<-IPRA.$kslgetl_int()+2880<-kslgetl()+60<-ksfglt()+188<-IPRA.$kghfrunp()+3328<-IPRA.$kghfnd()+1476<-IPRA.$kghprmalo()+1404<-kghalo()+3892<-IPRA.$dbktbPoolBufAlloc()+256<-IPRA.$dbktbPoolBucketAddBuf()+60<-dbktbDefaultBucketAlloc()+224<-ksucrp()+1512<-opiino()+1428<-opiodr()+1256<-opidrv()+1080<-sou2o()+208<-opimai_real()+680<-ssthrdmain()+460<-main()+216<-__start()+112
Kernel stack:
...

Note: 在ash中也没看到特别突出的SQL

原因

这个库确实有较大的内存分配， shared pool几乎200GB（SGA 500G） 对于一家医院来说确实是不小的配置， 而且也查询了sharedpool只的内存区，基本上就是SQLA 与handle占用的，并不存在其他异常增加或内存溢出。

MOS有一个bug较为匹配

Bug 33225584 – Instance Slow or Hung With High Shared Pool Latch Contention With Waits/Sleeps in kghfrunp() and Possible ‘ORA-32701: Possible hangs up to hang ID= detected’

影响版本：

• 19.14.0
• 19.13.0
• 19.12.0
• 19.11.0
• 19.10.0
• 19.8.0
• 19.5.0
• 19.1.0
• 12.2.0.1 (Base Release)

Note that this fix has been superseded by the fix in Bug:34220943

Symptoms Related To:

• Hang (Involving Shared Resource)
• Latch Contention
• Shared Pool Affected
• ORA-32701
• Stack is likely to include kghfrunp

解决方法

安装对应的补丁 34220943 

— or

定期检查shared pool free, 定期触发flush sharedpool ，临时解决