我们的文章会在微信公众号IT民工的龙马人生和博客网站( www.htz.pw )同步更新 ，欢迎关注收藏，也欢迎大家转载，但是请在文章开始地方标注文章出处，谢谢！
由于博客中有大量代码，通过页面浏览效果更佳。
Oracle性能优化：latch free-SQL memory manager latch等待
环境：os :aix 5.3 db:10.2.0.5.8(rac)
1，业务运行慢，查看数据库发现其中一个节点高达700个的latch free等待
I EVENT COUNT(*)
-- ---------------------------------------- ----------1 latch free 634Backup: sbtwrite2 12db file sequential read 5enq: TX - row lock contention 5SQL*Net message to client 2gc cr request 1SQL*Net message from dblink 1PX Deq: Execute Reply 1PX Deq: Execution Msg 1Streams AQ: waiting for messages in the 1queueenq: HW - contention 1db file scattered read 1SQL*Net message from client 12 gc cr request 5enq: TX - row lock contention 4db file sequential read 3SQL*Net message from client 3SQL*Net more data from dblink 2PX Deq: reap credit 1row cache lock 1db file scattered read 1enq: HW - contention 1direct path write 1
2，查看p1raw参数
I EVENT p1text-p3text PROGRAM OSPID USERNAME STATE SQL_ID SESSION ROW#
-- ------------------------- ---------------------------------------- ----------------------------- -------------------- ----------- ---------- --------------- --------------- --------------------1 latch free address:07000000100275A0:number:00000000 JDBC Thin Client 6290:29285:4203526 CRM_APP ACTIVE 8ubjuvyg6c534:0 1.6377 39:276458:1601426:000000123:tries:00000000000000041 latch free address:07000000100275A0:number:00000000 3356:65415:1799152 INTF ACTIVE 9sc3q1pbcf7ub:0 1.1340 224:365803:745328:000000123:tries:00000000000000011 latch free address:07000000100275A0:number:00000000 3068:36861:5932380 INTF ACTIVE g5dw2wgy69pkk:1 1.2215 290:274969:1003114:000000123:tries:001 latch free address:07000000100275A0:number:00000000 3828:8055:5371066 INTF ACTIVE bp7y01nsbg2yf:0 1.3699 224:365745:371456:000000123:tries:00000000000000051 latch free address:07000000100275A0:number:00000000 JDBC Thin Client 6189:54170:374336 CRM_APP ACTIVE c7anp2y57q6sk:0 1.6145 182:280702:59905:000000123:tries:00000000000000061 latch free address:07000000100275A0:number:00000000 1995:43649:585828 INTF ACTIVE bp7y01nsbg2yf:0 . 349:365774:1470739:000000123:tries:00000000000000011 latch free address:07000000100275A0:number:00000000 6788:10174:3511824 INTF ACTIVE 7kfd65a8bszhc:0 . 169:365761:194049:000000123:tries:001 latch free address:07000000100275A0:number:00000000 JDBC Thin Client 5849:10264:1266882 CRM_APP ACTIVE 6ngvw9wu4gbs9:0 1.4200 272:280752:336829:000000123:tries:00000000000000021 latch free address:07000000100275A0:number:00000000 7233:43200:2066162 INTF ACTIVE bp7y01nsbg2yf:0 . 33:276550:1067233:000000123:tries:00000000000000011 latch free address:07000000100275A0:number:00000000 JDBC Thin Client 3763:12952:1032272 CRM_APP ACTIVE 3bnnt7f1ys1u8:0 1.6788 25:280860:472375:000000123:tries:00000000000000041 latch free address:07000000100275A0:number:00000000 1349:657:423594 INTF ACTIVE gja0421jrmvx3:0 . 81:275093:1423165:000000123:tries:0000000000000004
3，查看latch发生在什么资源上
SQL> select * from v$latch where addr='07000000100275A0';ADDR LATCH# LEVEL# NAME HASH GETS MISSES SLEEPS IMMEDIATE_GETS IMMEDIATE_MISSES WAITERS_WOKEN WAITS_HOLDING_LATCH SPIN_GETS SLEEP1 SLEEP2 SLEEP3 SLEEP4 SLEEP5 SLEEP6 SLEEP7 SLEEP8 SLEEP9 SLEEP10 SLEEP11 WAIT_TIME
---------------- ---------- ---------- -------------------------------------------------- ---------- ---------- ---------- ---------- -------------- ---------------- ------------- ------------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
07000000100275A0 291 1 SQL memory manager latch 3576695101 54948079 31722426 30087767 5156238 17318 0 0 4727852 0 0 0 0 0 0 0 0 0 0 0 8.5347E+11
4，查看pga的使用情况
SQL> select sum(PGA_USED_MEM)/1024/1024/1024,sum(PGA_ALLOC_MEM)/1024/1024/1024 from v$process;SUM(PGA_USED_MEM)/1024/1024/1024 SUM(PGA_ALLOC_MEM)/1024/1024/1024
-------------------------------- ---------------------------------12.1002033 15.1518837
SQL> select * from v$pgastat;NAME VALUE UNIT
---------------------------------------------------------------- -------------------------- ------------
aggregate PGA target parameter 12884901888 bytes
aggregate PGA auto target 805306368 bytes
global memory bound 1346560 bytes
total PGA inuse 12962652160 bytes
total PGA allocated 16171951104 bytes
maximum PGA allocated 18744916992 bytes
total freeable PGA memory 1841692672 bytes
process count 5931
max processes count 6306
PGA memory freed back to OS 254549278851072 bytes
total PGA used for auto workareas 203935744 bytes
maximum PGA used for auto workareas 6624104448 bytes
total PGA used for manual workareas 0 bytes
maximum PGA used for manual workareas 8601600 bytes
over allocation count 1002554
bytes processed 688458378573824 bytes
extra bytes read/written 11514653887488 bytes
cache hit percentage 98 percent
recompute count (total) 28378262SQL> select * from v$pgastat;NAME VALUE UNIT
---------------------------------------------------------------- ---------------- ------------
aggregate PGA target parameter 12884901888 bytes
aggregate PGA auto target 805306368 bytes
global memory bound 1186816 bytes
total PGA inuse 12891575296 bytes
total PGA allocated 16107764736 bytes
maximum PGA allocated 18744916992 bytes
total freeable PGA memory 1835728896 bytes
process count 6020
max processes count 6306
PGA memory freed back to OS 254549830467584 bytes
total PGA used for auto workareas 202895360 bytesNAME VALUE UNIT
---------------------------------------------------------------- ---------------- ------------
maximum PGA used for auto workareas 6624104448 bytes
total PGA used for manual workareas 0 bytes
maximum PGA used for manual workareas 8601600 bytes
over allocation count 1002554
bytes processed 688464160836608 bytes
extra bytes read/written 11515083701248 bytes
cache hit percentage 98 percent
recompute count (total) 28380736通过mos查询，发现是BUG导致的Bug 9732503 latch free waits for SQL Memory Manager latch / extra child cursors This note gives a brief overview of bug 9732503. The content was last updated on: 28-JUN-2013 Click here for details of each of the sections below.
Affects:Product (Component)
Oracle Server (Rdbms)
Range of versions believed to be affected
Versions BELOW 12.1
Versions confirmed as being affected• 11.2.0.1• 11.1.0.7• 10.2.0.5• 10.2.0.4
Platforms affected
Generic (all / most platforms affected)
Fixed:This issue is fixed in• 12.1.0.1 (Base Release)• 11.2.0.2 (Server Patch Set)• 11.1.0.7 Patch 36 on Windows Platforms
Symptoms:
Related To:• Latch Contention• Shared Pool Affected• Cursor not shared due to OPTIMIZER_MISMATCH• V$SQL• V$SQL_SHARED_CURSOR• PGA_AGGREGATE_TARGET• _smm_px_max_size
DescriptionWhen the parameter PGA_AGGREGATE_TARGET is set too low, Oracle might
experience a significant level of contention on the SQL Memory Manager latch.Rediscovery Notes:The statistic "recompute count (queries)" in v$pgastat will be high (non zero)and should increase when observing the contention.The latch shows waits for location "qesmmIQueryRefreshBound"This issue can also cause excess shared pool usage in the formof multiple child cursors created for SQL statements due to OPTIMIZER_MISMATCH in V$SQL_SHARED_CURSOR as the internal resizingcan cause some parameters that affect the optimizer environmentto change, notably _smm_px_max_sizeWorkaroundThis issue is often caused when the value of pga_aggregate_target is set toolow, ie. when the statistic "over allocation count" is non zero inv$pgastat. When this is the case, increase pga_aggregate_target such that itis set to at least "maximum PGA allocated" in v$pgastat. You can find moreinformation on tuning pga_aggregate_target in the Oracle tuning guide.Please note: The above is a summary description only. Actual symptoms can vary. Matching to any symptoms here does not confirm that you are encountering this problem. For questions about this bug please consult Oracle Support.
ReferencesBug:9732503 (This link will only work for PUBLISHED bugs)Note:245840.1 Information on the sections in this article
5，手动增加pga大小
SQL> alter system set pga_aggregate_target=20G ;System altered.SQL> show parameter sga;NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
lock_sga boolean FALSE
pre_page_sga boolean FALSE
sga_max_size big integer 45G
sga_target big integer 0
SQL> show parameter pgaNAME TYPE VALUE
------------------------------------ ----------- ------------------------------
pga_aggregate_target big integer 20G
6，再次查询等待事件，一切正常
I EVENT COUNT(*)
-- ---------------------------------------- ----------1 Backup: sbtwrite2 12db file sequential read 11gc cr request 7gc buffer busy 3SQL*Net message from client 3db file scattered read 2gc current request 2SQL*Net more data from client 1buffer busy waits 1SQL*Net more data from dblink 1SQL*Net message from dblink 1PX Deq: Execute Reply 1PX Deq: Execution Msg 1Streams AQ: waiting for messages in the 1queue2 db file sequential read 7gc cr request 3SQL*Net message from dblink 3gc current request 2SQL*Net message from client 2SQL*Net message to client 2latch: shared pool 1latch: cache buffers chains 1log file sync 1enq: TX - contention 1db file parallel read 1PX Deq: reap credit 1
------------------作者介绍-----------------------
姓名：黄廷忠
个人博客: (http://www.htz.pw)
CSDN地址: (https://blog.csdn.net/wwwhtzpw)
博客园地址: (https://www.cnblogs.com/www-htz-pw)