环境：os :aix 5.3 db:10.2.0.5.8(rac)
1，业务运行慢，查看数据库发现其中一个节点高达700个的latch free等待
I EVENT COUNT(*) -- ---------------------------------------- ---------- 1 latch free 634 Backup: sbtwrite2 12 db file sequential read 5 enq: TX - row lock contention 5 SQL*Net message to client 2 gc cr request 1 SQL*Net message from dblink 1 PX Deq: Execute Reply 1 PX Deq: Execution Msg 1 Streams AQ: waiting for messages in the 1 queue enq: HW - contention 1 db file scattered read 1 SQL*Net message from client 1 2 gc cr request 5 enq: TX - row lock contention 4 db file sequential read 3 SQL*Net message from client 3 SQL*Net more data from dblink 2 PX Deq: reap credit 1 row cache lock 1 db file scattered read 1 enq: HW - contention 1 direct 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:0
00000123:tries:0000000000000004
1 latch free address:07000000100275A0:number:00000000 3356:65415:1799152 INTF ACTIVE 9sc3q1pbcf7ub:0 1.1340 224:365803:745328:0
00000123:tries:0000000000000001
1 latch free address:07000000100275A0:number:00000000 3068:36861:5932380 INTF ACTIVE g5dw2wgy69pkk:1 1.2215 290:274969:1003114:0
00000123:tries:00
1 latch free address:07000000100275A0:number:00000000 3828:8055:5371066 INTF ACTIVE bp7y01nsbg2yf:0 1.3699 224:365745:371456:0
00000123:tries:0000000000000005
1 latch free address:07000000100275A0:number:00000000 JDBC Thin Client 6189:54170:374336 CRM_APP ACTIVE c7anp2y57q6sk:0 1.6145 182:280702:59905:0
00000123:tries:0000000000000006
1 latch free address:07000000100275A0:number:00000000 1995:43649:585828 INTF ACTIVE bp7y01nsbg2yf:0 . 349:365774:1470739:0
00000123:tries:0000000000000001
1 latch free address:07000000100275A0:number:00000000 6788:10174:3511824 INTF ACTIVE 7kfd65a8bszhc:0 . 169:365761:194049:0
00000123:tries:00
1 latch free address:07000000100275A0:number:00000000 JDBC Thin Client 5849:10264:1266882 CRM_APP ACTIVE 6ngvw9wu4gbs9:0 1.4200 272:280752:336829:0
00000123:tries:0000000000000002
1 latch free address:07000000100275A0:number:00000000 7233:43200:2066162 INTF ACTIVE bp7y01nsbg2yf:0 . 33:276550:1067233:0
00000123:tries:0000000000000001
1 latch free address:07000000100275A0:number:00000000 JDBC Thin Client 3763:12952:1032272 CRM_APP ACTIVE 3bnnt7f1ys1u8:0 1.6788 25:280860:472375:0
00000123:tries:0000000000000004
1 latch free address:07000000100275A0:number:00000000 1349:657:423594 INTF ACTIVE gja0421jrmvx3:0 . 81:275093:1423165:0
00000123: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) 28378262 SQL> 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 bytes NAME 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 Description When 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 form of multiple child cursors created for SQL statements due to OPTIMIZER_MISMATCH in V$SQL_SHARED_CURSOR as the internal resizing can cause some parameters that affect the optimizer environment to change, notably _smm_px_max_size Workaround This issue is often caused when the value of pga_aggregate_target is set too low, ie. when the statistic "over allocation count" is non zero in v$pgastat. When this is the case, increase pga_aggregate_target such that it is set to at least "maximum PGA allocated" in v$pgastat. You can find more information 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. References Bug: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 pga NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ pga_aggregate_target big integer 20G
6，再次查询等待事件，一切正常
I EVENT COUNT(*) -- ---------------------------------------- ---------- 1 Backup: sbtwrite2 12 db file sequential read 11 gc cr request 7 gc buffer busy 3 SQL*Net message from client 3 db file scattered read 2 gc current request 2 SQL*Net more data from client 1 buffer busy waits 1 SQL*Net more data from dblink 1 SQL*Net message from dblink 1 PX Deq: Execute Reply 1 PX Deq: Execution Msg 1 Streams AQ: waiting for messages in the 1 queue 2 db file sequential read 7 gc cr request 3 SQL*Net message from dblink 3 gc current request 2 SQL*Net message from client 2 SQL*Net message to client 2 latch: shared pool 1 latch: cache buffers chains 1 log file sync 1 enq: TX - contention 1 db file parallel read 1 PX Deq: reap credit 1
latch free-SQL memory manager latch等待：等您坐沙发呢！