高shared pool latch及library cache latch争用问题

tianshiguodong

操作系统版本:redhat 5.3 64位       数据库版本:oracle 10.2.0.4
用户反映业务高峰时较慢，经查看问题时段AWR报告发现top5中主要为latch: library cache和latch: shared pool，附其中一份AWR
分析------->:
(1)经查看硬解析相关指标发现都很低，说明不是由于硬解析导致的latch争用，那么绑定变量导致的问题被排除；
(2)Latch Miss Sources查看主要为library cache kglLockCursor和shared pool  kghalo这2个函数，不知道这2个函数发生在sql解析那个阶段;
(3)检查发现shared pool存在抖动，说明shared pool偏小
问题------->:
(1)通过增加sga_target到6G，能否改善latch争用问题?
(2)是不是相关sql导致的latch争用，如果是，如何从AWR报告分析相关sql的问题，并找出相关sql?
请大神们给分析、分析

Stone

1) 硬解析其实是个问题，应该重点关注"SQL ordered by Gets"部分一个query的硬解析，至少在这个时间段，推测应该是这个东东坏了满锅粥。特别是"SQL Module"部分与程序"XHLisServiceA.exe"有关的部分，硬解析，I/O消耗比较大。建议考虑使用绑定变量。

[tr][/tr]

Buffer Gets	Executions	Gets per Exec	%Total	CPU Time (s)	Elapsed Time (s)	SQL Id	SQL Module	SQL Text
6,367,716	927	6,869.17	11.01	15.80	16.04	gnp1u7yqu2skn	presdisp.exe	SELECT "DRUG_PRESC_MASTER_T...
3,588,452	10	358,845.20	6.20	5.52	5.66	6y7zabg9dd4b1	XHLisServiceA.exe	SELECT pats_in_hospital.patie...
3,229,614	9	358,846.00	5.58	5.04	5.12	1761d7s6zm057	XHLisServiceA.exe	SELECT pats_in_hospital.patie...
3,229,413	9	358,823.67	5.58	5.72	6.12	fuj5yvndztbgb	XHLisServiceA.exe	SELECT pats_in_hospital.patie...
2,870,713	8	358,839.13	4.96	4.45	4.56	g5tzdvbc7cy4z	XHLisServiceA.exe	SELECT pats_in_hospital.patie...
2,511,663	7	358,809.00	4.34	3.77	3.77	gk2gr6mr59mct	XHLisServiceA.exe	SELECT pats_in_hospital.patie...
2,511,625	7	358,803.57	4.34	3.92	3.93	2rkp3b0v001wj	XHLisServiceA.exe	SELECT pats_in_hospital.patie...
2,152,838	6	358,806.33	3.72	3.37	3.39	8gu44f7gj99nz	XHLisServiceA.exe	SELECT pats_in_hospital.patie...
1,794,010	5	358,802.00	3.10	2.93	2.93	09uzsuu1ndjgu	XHLisServiceA.exe	SELECT pats_in_hospital.patie...
1,435,403	4	358,850.75	2.48	2.26	2.26	5k2x2nzfu9z4j	XHLisServiceA.exe	SELECT pats_in_hospital.patie...
1,435,366	4	358,841.50	2.48	2.19	2.21	f0rafhx0gx5mu	XHLisServiceA.exe	SELECT pats_in_hospital.patie...
1,435,358	4	358,839.50	2.48	2.28	2.29	gpjpp9dpw6fat	XHLisServiceA.exe	SELECT pats_in_hospital.patie...
1,435,337	4	358,834.25	2.48	2.20	2.20	btrua8nk7zv2c	XHLisServiceA.exe	SELECT pats_in_hospital.patie...
1,435,244	4	358,811.00	2.48	2.15	2.16	6amh56nxa6n0c	XHLisServiceA.exe	SELECT pats_in_hospital.patie...

另外这个程序"presdisp.exe"也消耗了大量的I/O，可以考虑是否有索引，是否需要索引，看看能否优化。

2）SGA增大应该有好处，但是不是解决的最主要的问题。另外操作系统只有7.8G，如果SGA增加到6G，再加上PGA的1G，估计比较危险。

3) 另外应用反映慢，可以具体再研究下，到底怎么慢。使用什么情况下慢，点那个按钮，或者使用那个程序的时候慢， 这样问的问题多了，能搞清楚了，解决问题应该也不远啦。

当然分析仅供参考，抛砖只为引玉 :)
希望有所帮助。

不了峰

Stone 发表于 2013-9-23 23:13
1) 硬解析其实是个问题，应该重点关注"SQL ordered by Gets"部分一个query的硬解析，至少在这个时间段，推 ...

同意～

我认为简单说，就是那些 XHLisServiceA.exe 程序中的不良SQL的"大量"出现 ，冲击了sga ，造成了内存的抖动。从而导致用户认为系统变慢了。

所以，首要任务是去调优那些sql。由于总内存的紧张，个人认为不应该去调整SGA的大小。

如果无法修改SQL的话，应该要手工去指定shared pool与cache size的一个最小值 ，用IO去换逻辑读，最大程度的避免内存抖动现象的发生

另外，从AAS指标，感觉系统负载并不高。
还有一个现象是user rollback每秒都会有一个产生，这个是由程序设置 auto rollback吗？还是真的有那么多的user rollback?