当前位置: 首页 > EVENT > 正文

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/2013/09/15/latch-free-sql-memory-manager-latch%e7%ad%89%e5%be%85.html | 认真就输

该日志由 huangtingzhong 于2013年09月15日发表在 EVENT 分类下, 通告目前不可用,你可以至底部留下评论。
原创文章转载请注明: latch free-SQL memory manager latch等待 | 认真就输
关键字: