首页 » ORACLE » Tuning ‘latch free’ & ‘null event’ wait event in Oracle 9i

Tuning ‘latch free’ & ‘null event’ wait event in Oracle 9i

最近遇到一套9I数据库遇到了性能问题, 现象是数据库主机CPU使用率很高应用响应缓慢,Cpu Idel几乎为0, 从v$session_wait查看数据库当前的活动会话在等待’null event’和’latch free’.

anbob% uname -a
SunOS anbob 5.10 Generic_139555-08 sun4u sparc SUNW,SPARC-Enterprise

anbob% sqlplus "/ as sysdba"

SQL*Plus: Release 9.2.0.1.0 - Production on Fri Nov 11 11:35:34 2016

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.1.0 - 64bit Production
PL/SQL Release 9.2.0.1.0 - Production
CORE    9.2.0.1.0       Production
TNS for Solaris: Version 9.2.0.1.0 - Production
NLSRTL Version 9.2.0.1.0 - Production

SQL> select event,count(*) from v$session_wait group by event;

EVENT                                                              COUNT(*)
---------------------------------------------------------------- ----------
SQL*Net message from client                                              66
db file scattered read                                                    2
latch free                                                               21
null event                                                               83
pmon timer                                                                1
rdbms ipc message                                                         5
smon timer                                                                1
wakeup time manager                                                       1

Note:
对于null event通常是数据库暂时未最到等待事件的命名,原因可能是当时的CPU负载高,数据库HANG,bug, 本案例应该是因为latch导致的CPU耗尽,所以解决latch free先。
latch是一种访问共享资源时的内部内存结构,通常latch的持有非常的短暂,又因为latch spin非常的消耗CPU,sleeping阶段不会消耗CPU time但同时会显示latch free的wait event.
通常对于latch free需要确认相关的具体的latch和相关的SQL及涉及的对象。如果感兴趣推荐阅读去搜索。 对于latch free的三个参数如下:
P1 = Latch address
P2 = Latch number
P3 = Tries

SQL> select name from v$latchname where latch#=97;
NAME
----------------------------------------------------------------
cache buffers chains

SQL>  select sql_hash_value,count(*) from v$session_wait a,v$session b where a.sid=b.sid
     and event='latch free' group by sql_hash_value;

 SQL_HASH_VALUE        COUNT(*)
--------------- ---------------
...
     1993732470               1
     2081869776               1
     2349207484               2
     2380084478               2
     2695258969               1
     2907909916               1
     3165338618               1
     3442647599               1
     3655775635               1
     3756755013               3
     3790812144               1
     3936368692               1
     3980632519               2
     4005557655               1
     4161039002               1
     4249511952               1
     4274466022               2

32 rows selected.
	
SQL> select p1,count(*) from v$session_wait a  where    event='latch free' group by p1;

             P1        COUNT(*)
--------------- ---------------
    16162378384               1
    16162683952               9
    16162801168              13
    16162947472              23
    
 select to_char(16162947472,'xxxxxxxxxxxxxxx') from dual;
 TO_CHAR(16162947
----------------
       3c3630190


SQL> select * from v$latch_children where addr=upper(lpad('3c3630190',16,0));

ADDR               LATCH#  CHILD#     LEVEL# NAME                        GETS    MISSES   SLEEPS  IMMEDIATE_GETS IMMEDIATE_MISSES 
---------------- -------- ------- ---------- ---------------------- --------- --------- -------- --------------- ---------------- 
00000003C3630190       97    2949          1 cache buffers chains      294507       499      373            8804                3 
-- wait 1s
SQL> /

ADDR               LATCH#  CHILD#     LEVEL# NAME                        GETS    MISSES   SLEEPS  IMMEDIATE_GETS IMMEDIATE_MISSES 
---------------- -------- ------- ---------- ---------------------- --------- --------- -------- --------------- ---------------- 
00000003C3630190       97    2949          1 cache buffers chains      300406       503      373            8925                3 

SQL> /

ADDR               LATCH#  CHILD#     LEVEL# NAME                        GETS    MISSES   SLEEPS  IMMEDIATE_GETS IMMEDIATE_MISSES 
---------------- -------- ------- ---------- ---------------------- --------- --------- -------- --------------- ---------------- 
00000003C3630190       97    2949          1 cache buffers chains      300966       504      373            8939                3 

SQL> /

ADDR               LATCH#  CHILD#     LEVEL# NAME                        GETS    MISSES   SLEEPS  IMMEDIATE_GETS IMMEDIATE_MISSES 
---------------- -------- ------- ---------- ---------------------- --------- --------- -------- --------------- ---------------- 
00000003C3630190       97    2949          1 cache buffers chains      301324       504      373            8948                3 

SQL> 

select hladdr,  file#, dbablk, decode(state,1,'cur ',3,'CR',state) ST, tch
 from x$bh where hladdr in
  (select addr from  (select addr from v$latch_children  where addr=upper(lpad('3c3630190',16,0))
 order by sleeps, misses,immediate_misses desc )where rownum <2) order by tch;
 
 HLADDR                FILE#     DBABLK ST                                              TCH
---------------- ---------- ---------- ---------------------------------------- ----------
...
...
00000003C3630190         16     436368 cur                                             156
00000003C3630190         17     491740 cur                                             157
00000003C3630190         11     257281 cur                                             160
00000003C3630190         16     706704 cur                                             161
00000003C3630190         12     468301 cur                                             162
00000003C3630190         12     759117 cur                                             162
00000003C3630190         12    1017696 cur                                             169
00000003C3630190         11     204033 cur                                             170
00000003C3630190         17     475356 cur                                             170
00000003C3630190         12     226637 cur                                             174
00000003C3630190         18     248104 cur                                             178
00000003C3630190         12     128333 cur                                             179
00000003C3630190         16    1169552 cur                                             185
00000003C3630190         16     727184 cur                                             190
00000003C3630190         12     173389 cur                                             194
00000003C3630190         12     857952 cur                                             197
00000003C3630190         12     578893 cur                                             214
00000003C3630190         12    1029984 cur                                             226
00000003C3630190         18       5909 cur                                             228
00000003C3630190         16    1255568 cur                                             410
00000003C3630190         18     157992 cur                                             422
00000003C3630190         19     119156 cur                                             468
00000003C3630190         17     241884 cur                                             490
00000003C3630190         12    1156960 cur                                             508
00000003C3630190         16     907408 cur                                             600
00000003C3630190         12    1009504 cur                                            1051   <<< 
125 rows selected. 

SQL> select  owner, segment_name,partition_name, segment_type, file_id,block_id from dba_extents
where file_id = 12 and   1009504 between    block_id  and block_id+blocks-1;

OWNER         SEGMENT_NAME               PARTITION_NAME   SEGMENT_TYPE          FILE_ID   BLOCK_ID
------------- -------------------------- ---------------- ------------------ ---------- ----------
LTEOMS        IDX_WFWI_STATE                              INDEX                      12    1008521


 
SQL> select /*+ RULE */ substr(a.event,1,25) ,substr(b.program,1,39) program,  
 b.username,substr(b.osuser||'@'||b.machine||'@'||b.process||'@'||c.spid,1,31) client,
 to_char(b.logon_time,'mm-dd hh24:mi') logon_time
 from v$session_wait a,v$session b, v$process c
 where a.sid=b.sid and b.paddr=c.addr
 and a.event ='latch free';
 
 PROGRAM                     USERNAME        CLIENT                      LOGON_TIME
--------------------------- --------------- --------------------------- -----------
JDBC Thin Client            LTEOMS          root@hneoms@@2172           11-11 10:16
JDBC Thin Client            LTEOMS          root@hneoms@@2394           11-11 10:27
JDBC Thin Client            LTEOMS          root@hneoms@@2222           11-11 10:18
JDBC Thin Client            LTEOMS          root@hneoms@@1998           11-11 10:09
JDBC Thin Client            LTEOMS          root@hneoms@@1974           11-11 10:07
JDBC Thin Client            LTEOMS          root@hneoms@@1915           11-11 10:05

6 rows selected.


select hash_value,sql_text from v$sql where hash_value in(
1045805320,
1081719454,
1542141426,
1663684681,
1759938066,
1826030977,
1906825844,
1993732470,
2081869776,
2349207484,
2380084478,
2695258969,
2907909916,
3165338618,
3442647599,
3655775635,
3756755013,
3790812144,
3936368692,
3980632519,
4005557655,
4161039002,
4249511952,
4274466022
);


SELECT COUNT(*) AMOUNT FROM (SELECT  DISTINCT ADVISE_NUM adviseNum,REPLY_NUM replyNum,HASTEN_NUM hastenNum,MAIN_ID mainId,TITLE title,SHEET_ID sheetId,STATUS status,SEND_TIME sendTime,SEND_DEPT_ID sendDeptId,SEND_USER_ID sendUserId,LINK_ID linkId,TASK_ACCEPT_LIMIT taskAcceptLimit,TASK_COMPLETE_LIMIT
 taskCompleteLimit,TASK_TYPE taskType,START_TIME startTime,END_TIME endTime,ACTIVITY_DEF_ID activityDefId,ACTIVITY_INST_NAME activityInstName,ACTIVITY_INST_ID activityInstId,PROCESS_INST_ID processInstId,PROCESS_INST_NAME processInstName,PROCESS_DEF_ID processDefId,PROCESS_DEF_NAME processDefName,WO
...
...
24 rows selected.

Note:
根据p2的值确认了是cache buffers chains latch,通常出现在hot block的SQL中,并且只有gets有明显的增长,通常从x$bh的tch列可以确认对象为一索引对象,并且有确认了相的SQL基本相同只是字面量值不同,这个sql较长这里之截取了其中一部分,因为v$sql在9i中不存在sqlfull_text, 可以写PLSQL 去v$sqltext中拼全SQL语句, 继续可以从SQL的执行计划与hot block的对象交集进行判断优化。因为9i中没有dbms_xplan package查看执行计划,这里我调用了TanelPoder的脚本。因为该SQL的执行计划有约1000行,我们可以先看与hot block 索引相关的执行计划。

SQL> @xm 3756755013 0

Ch Pr   Op                                                         Objcect                        Optimizer Optim rows Optim bytes Optimizer
ld ed   ID Operation                                               Name                                Cost  from step   from step Mode
-- -- ---- ------------------------------------------------------- ------------------------------ --------- ---------- ----------- ----------
 0       0 SELECT STATEMENT                                                                           13222                        CHOOSE
         1  SORT AGGREGATE                                                                                           1
         2   VIEW                                                                                     13222         64
         3    SORT UNIQUE                                                                             13222         64       44736
         4     VIEW                                                                                   13268         64       44736
         5      SORT UNIQUE                                                                           13268         64       46142
         6       UNION-ALL
         7        CONCATENATION
         8         NESTED LOOPS                                                                          44          1         723
   AF    9          HASH JOIN                                                                            43          1         697
        10           NESTED LOOPS                                                                        40          1         321
        11            INLIST ITERATOR
    F   12             TABLE ACCESS BY INDEX ROWID                 WFWIPARTICIPANT                        6        314       26376
   A    13              INDEX RANGE SCAN                           WF_IDX_PART_TYPE                       2      12546
    F   14            TABLE ACCESS BY INDEX ROWID                  WFWORKITEM                            40       1799      426363
   A    15             INDEX UNIQUE SCAN                           PK_WFWORKITEM                        815          1
        16           TABLE ACCESS FULL                             WS_CIRCUITSHEET_TASK                   2        409      153784
    F   17          TABLE ACCESS BY INDEX ROWID                    WS_CIRCUITSHEET_MAIN                   1          1          26
   A    18           INDEX UNIQUE SCAN                             WS_PK_CIRCUITSHEET_MAIN                         102
        19         NESTED LOOPS                                                                          44          1         723
   AF   20          HASH JOIN                                                                            43          1         697
        21           TABLE ACCESS BY INDEX ROWID                   WFWORKITEM                            40       1799      426363
        22            NESTED LOOPS                                                                       40          1         321
        23             INLIST ITERATOR
    F   24              TABLE ACCESS BY INDEX ROWID                WFWIPARTICIPANT                        6        314       26376
   A    25               INDEX RANGE SCAN                          WF_IDX_PART_TYPE                       2      12546
        26             BITMAP CONVERSION TO ROWIDS
        27              BITMAP AND
        28               BITMAP CONVERSION FROM ROWIDS
   A    29                INDEX RANGE SCAN                         PK_WFWORKITEM                                     1
        30               BITMAP CONVERSION FROM ROWIDS
   A    31                INDEX RANGE SCAN                         IDX_WFWI_STATE                         1          1                <<<<<<<<<
        32           TABLE ACCESS FULL                             WS_CIRCUITSHEET_TASK                   2        409      153784
    F   33          TABLE ACCESS BY INDEX ROWID                    WS_CIRCUITSHEET_MAIN                   1          1          26
   A    34           INDEX UNIQUE SCAN                             WS_PK_CIRCUITSHEET_MAIN                         102
        35        CONCATENATION
        36         NESTED LOOPS                                                                         141          1         786
   AF   37          HASH JOIN                                                                           140          1         760
        38           NESTED LOOPS                                                                        40          1         321
        39            INLIST ITERATOR
    F   40             TABLE ACCESS BY INDEX ROWID                 WFWIPARTICIPANT                        6        314       26376
   A    41              INDEX RANGE SCAN                           WF_IDX_PART_TYPE                       2      12546
    F   42            TABLE ACCESS BY INDEX ROWID                  WFWORKITEM                            40         10        2370
   A    43             INDEX UNIQUE SCAN                           PK_WFWORKITEM                        815          1
        44           TABLE ACCESS FULL                             WS_CUTMANAGESHEET_TASK                86      71389    31339771
    F   45          TABLE ACCESS BY INDEX ROWID                    WS_CUTMANAGESHEET_MAIN                 1          1          26
   A    46           INDEX UNIQUE SCAN                             WS_PK_CUTMANAGESHEET_MAIN                       100
        47         NESTED LOOPS                                                                         141          1         786
   AF   48          HASH JOIN                                                                           140          1         760
        49           TABLE ACCESS BY INDEX ROWID                   WFWORKITEM                            40         10        2370
        50            NESTED LOOPS                                                                       40          1         321
        51             INLIST ITERATOR
    F   52              TABLE ACCESS BY INDEX ROWID                WFWIPARTICIPANT                        6        314       26376
   A    53               INDEX RANGE SCAN                          WF_IDX_PART_TYPE                       2      12546
        54             BITMAP CONVERSION TO ROWIDS
        55              BITMAP AND
        56               BITMAP CONVERSION FROM ROWIDS
   A    57                INDEX RANGE SCAN                         PK_WFWORKITEM                                     1
        58               BITMAP CONVERSION FROM ROWIDS
    A   59                INDEX RANGE SCAN                         IDX_WFWI_STATE                         1          1               
        60           TABLE ACCESS FULL                             WS_CUTMANAGESHEET_TASK                86      71389    31339771
    F   61          TABLE ACCESS BY INDEX ROWID                    WS_CUTMANAGESHEET_MAIN                 1          1          26
   A    62           INDEX UNIQUE SCAN                             WS_PK_CUTMANAGESHEET_MAIN                       100
...
...
   A   837            INDEX RANGE SCAN                             IX_HNRESONE_T_WORKID                   1          1
    F  838          TABLE ACCESS BY INDEX ROWID                    WS_HNRESONE_MAIN                       1         53        1378
   A   839           INDEX UNIQUE SCAN                             WS_PK_HNRESONE_MAIN                             100



Ch Op
ld ID     Predicate Information (identified by operation id):
-- ------ ----------------------------------------------------------------------------------------------------
 0     9  - access("WORKITEM_ID"="WORKITEMID")
       9  - filter("CURRENTSTATE"=4 OR "CURRENTSTATE"=10 OR "PROLONG_READ_FLAG">0 OR "PHASE_BACK_READ_FLAG">0)
      12  - filter("PARTIINTYPE"='EXE' OR "PARTIINTYPE"='GET')
      13  - access("PARTICIPANTTYPE"='role' AND ("PARTICIPANT"='8a9580bc22fe4d430123037a68e208fd' OR
          "PARTICIPANT"='8a9580bc29781f21012981a824492b13'))

      14  - filter("CURRENTSTATE"="WORKITEMSTATE")
      15  - access("WORKITEMID"="WORKITEMID")
      17  - filter("STATUS"=2)
      18  - access("MAIN_ID"="MAIN_ID")
      20  - access("WORKITEM_ID"="WORKITEMID")
      20  - filter("CURRENTSTATE"=4 OR "CURRENTSTATE"=10 OR "PROLONG_READ_FLAG">0 OR "PHASE_BACK_READ_FLAG">0)
      24  - filter(("PARTIINTYPE"='EXE' OR "PARTIINTYPE"='GET') AND (LNNVL("PARTICIPANTTYPE"='role') OR
          LNNVL("PARTICIPANT"='8a9580bc22fe4d430123037a68e208fd') AND
          LNNVL("PARTICIPANT"='8a9580bc29781f21012981a824492b13')))

      25  - access(("PARTICIPANTTYPE"='person' AND "PARTICIPANT"='wangqm' OR "PARTICIPANTTYPE"='organization'
          AND "PARTICIPANT"='1238'))

      29  - access("WORKITEMID"="WORKITEMID")
      31  - access("CURRENTSTATE"="WORKITEMSTATE")
      33  - filter("STATUS"=2)
      34  - access("MAIN_ID"="MAIN_ID")
      37  - access("WORKITEM_ID"="WORKITEMID")
      37  - filter("CURRENTSTATE"=4 OR "CURRENTSTATE"=10 OR "PROLONG_READ_FLAG">0 OR "PHASE_BACK_READ_FLAG">0)
      40  - filter("PARTIINTYPE"='EXE' OR "PARTIINTYPE"='GET')
      41  - access("PARTICIPANTTYPE"='role' AND ("PARTICIPANT"='8a9580bc22fe4d430123037a68e208fd' OR
          "PARTICIPANT"='8a9580bc29781f21012981a824492b13'))

      42  - filter("CURRENTSTATE"="WORKITEMSTATE")
      43  - access("WORKITEMID"="WORKITEMID")
      45  - filter("STATUS"=2)
      46  - access("MAIN_ID"="MAIN_ID")
      48  - access("WORKITEM_ID"="WORKITEMID")
      48  - filter("CURRENTSTATE"=4 OR "CURRENTSTATE"=10 OR "PROLONG_READ_FLAG">0 OR "PHASE_BACK_READ_FLAG">0)
      52  - filter(("PARTIINTYPE"='EXE' OR "PARTIINTYPE"='GET') AND (LNNVL("PARTICIPANTTYPE"='role') OR
          LNNVL("PARTICIPANT"='8a9580bc22fe4d430123037a68e208fd') AND
          LNNVL("PARTICIPANT"='8a9580bc29781f21012981a824492b13')))

      53  - access(("PARTICIPANTTYPE"='person' AND "PARTICIPANT"='wangqm' OR "PARTICIPANTTYPE"='organization'
          AND "PARTICIPANT"='1238'))

      57  - access("WORKITEMID"="WORKITEMID")
      59  - access("CURRENTSTATE"="WORKITEMSTATE")
      61  - filter("STATUS"=2)
      62  - access("MAIN_ID"="MAIN_ID")
...


Note:
在使用到索引的位置使用的是nested-loop join, 而且多处出现,这样该语句自身就增加了buffer在访问该索引时,结果就是导致更高的latch CBC竞争,另外该索引是在BITMAP CONVERSION中使用,通常是要么是创建了不科学的索引,如在selectivity 较低的列创建了索引,导致使用了错误的执行计划,如改变两索引为组合索引解决,或通过_b_tree_bitmap_plans参数考虑在SQL,SESSION,SYSTEM level禁用普通tree索引转换bitmap的特性。

打赏

对不起,这篇文章暂时关闭评论。