Oracle性能優化：latch free-SQL memory manager latch等待
我們的文章會在微信公眾號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 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
------------------作者介紹-----------------------
姓名：黃廷忠
個人博客: (http://www.htz.pw)
CSDN地址: (https://blog.csdn.net/wwwhtzpw)
博客園地址: (http://www.rzrgm.cn/www-htz-pw)
提供ORACLE技術支持(系統優化，故障處理，安裝升級，數據恢復等） TEL:18081072613，微信、QQ同手機號。
浙公網安備 33010602011771號