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

模拟latch: cache buffers chains等待事件

昨天一客户环境CPU使用达到100%,最后定位是由于cbc等待事件导致的,因为latch要消耗CPU的。其实CBC发生的原因一般就3个:

1SQL性能不好。

2,热块

3BUG

 

下面模拟一下cbc,重点在于介绍几个脚本,用于快速定位CBC发生的对象与SQLID。不介绍怎么解决CBC,因为不同的原因解决的办法不同,如SQL性能不好,需要优化SQL,那又是另一门技术,再如热块,根据对象的不同,解决的方法也很好,如果自己不会,欢迎加入QQ群:ORACLE数据库超级群

1,环境介绍

www.htz.pw > select * from v$version;

 

BANNER

——————————————————————————–

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – 64bit Production

PL/SQL Release 11.2.0.3.0 – Production

CORE    11.2.0.3.0      Production

TNS for Linux: Version 11.2.0.3.0 – Production

NLSRTL Version 11.2.0.3.0 – Production

 

www.htz.pw > !lsb_release -a

LSB Version:    :core-3.0-amd64:core-3.0-ia32:core-3.0-noarch:graphics-3.0-amd64:graphics-3.0-ia32:graphics-3.0-noarch

Distributor ID: RedHatEnterpriseAS

Description:    Red Hat Enterprise Linux AS release 4 (Nahant Update 8)

Release:        4

Codename:       NahantUpdate8

2,脚本准备

这里主要使用NL方式,不断访问CBC2这个表。

www.htz.pw > create table htz.cbc1 as select * from dba_objects where owner=’SYS’ and rownum<101;   

                                                                                                    

Table created.                                                                                      

                                                                                                    

www.htz.pw > create table htz.cbc2 as select * from dba_objects;                                    

                                                                                                    

Table created.                                                                                      

                                                                                                    

收集统计信息                                                                                        

SELECT /*+ use_nl(a b)*/                                                                            

      COUNT (*)                                                                                     

  FROM htz.cbc1 a, htz.cbc2 b                                                                       

 WHERE a.owner = b.owner;                                                                           

www.htz.pw > SELECT /*+ use_nl(a b)*/                                                               

  2        COUNT (*)                                                                                

  3    FROM htz.cbc1 a, htz.cbc2 b                                                                  

  4   WHERE a.owner = b.owner;                                                                      

                                                                                                    

Execution Plan                                                                                      

———————————————————-                                          

Plan hash value: 4079546673                                                                         

                                                                                                    

—————————————————————————-                        

| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |                        

—————————————————————————-                        

|   0 | SELECT STATEMENT    |      |     1 |    34 | 29726   (1)| 00:05:57 |                        

|   1 |  SORT AGGREGATE     |      |     1 |    34 |            |          |                        

|   2 |   NESTED LOOPS      |      | 74911 |  2487K| 29726   (1)| 00:05:57 |                        

|   3 |    TABLE ACCESS FULL| CBC1 |   100 |  1700 |     4   (0)| 00:00:01 |                        

|*  4 |    TABLE ACCESS FULL| CBC2 |   749 | 12733 |   297   (1)| 00:00:04 |                        

—————————————————————————-                        

                                                                                                    

Predicate Information (identified by operation id):                                                 

—————————————————                                                 

                                                                                                    

   4 – filter("A"."OWNER"="B"."OWNER")                                                              

                                                                                                    

                                                                                                    

/* Formatted on 2014/9/16 14:17:55 (QP5 v5.240.12305.39446) */                                      

CREATE OR REPLACE PROCEDURE htz.cbc_test                                                            

IS                                                                                                  

   count_num   NUMBER;                                                                              

BEGIN                                                                                               

   FOR i IN 1 .. 1000                                                                               

   LOOP                                                                                             

      SELECT /*+ use_nl(a b)*/                                                                      

            COUNT (*)                                                                               

        INTO count_num                                                                              

        FROM htz.cbc1 a, htz.cbc2 b                                                                 

       WHERE a.owner = b.owner;                                                                     

   END LOOP;                                                                                        

END;                                                                                                

/                                                                                                   

                                                                                                    

CREATE OR REPLACE PROCEDURE htz.cbc_test                                                            

IS                                                                                                  

   count_num   NUMBER;                                                                              

BEGIN                                                                                               

   FOR i IN 1 .. 1000                                                                               

   LOOP                                                                                             

      SELECT /*+ use_nl(a b)*/                                                                      

            COUNT (*)                                                                               

        INTO count_num                                                                              

        FROM htz.cbc1 b, htz.cbc2 a                                                                 

       WHERE a.owner = b.owner;                                                                     

   END LOOP;                                                                                        

END;                                                                                                

/                                                                                                   

www.htz.pw > CREATE OR REPLACE PROCEDURE htz.cbc_test                                               

  2  IS                                                                                             

  3     count_num   NUMBER;                                                                         

  4  BEGIN                                                                                          

  5     FOR i IN 1 .. 1000                                                                          

  6     LOOP                                                                                        

  7        SELECT /*+ use_nl(a b)*/                                                                 

  8              COUNT (*)                                                                          

  9          INTO count_num                                                                         

 10          FROM htz.cbc1 a, htz.cbc2 b                                                            

 11         WHERE a.owner = b.owner;                                                                

 12     END LOOP;                                                                                   

 13  END;                                                                                           

 14  /                                                                                              

                                                                                                    

Procedure created.                                                                                                    

3,现象模拟

这里模拟6个并发,2SQL语句

VAR job_no NUMBER;                                                   

                                                                     

BEGIN                                                                

   FOR idx IN 1 .. 3                                                 

   LOOP                                                              

      DBMS_JOB.submit (:job_no, ‘htz.cbc_test;’);                    

      DBMS_JOB.submit (:job_no, ‘htz.cbc_test1;’);                   

      COMMIT;                                                        

   END LOOP;                                                         

END;                                                                 

/                                                                    

4,现象分析

4.1 查询等待事件

从当前等待事件查询

www.htz.pw > @event_latch_cache_buffer_chain.sql                                                             

                                                                                                             

SESS_SERIAL                                                             COUNT            COUNT RAW_SQL   COUNT

OS_PROCESS      SQL_ID          P1TEXT            P1 P1RAW                RAW RAW_PCT  RAW_SQL PCT      SQL_ID

————— ————— ——- ———— —————– —— ——- ——– ——- ——-

79:767:22018    5trhkkmhmr6gn:1 address   3619727480 00000000D7C0A878       3 50%            2 33.33%        3

74:53:22022     5trhkkmhmr6gn:1 address   3619727480 00000000D7C0A878       3 50%            2 33.33%        3

59:21:22024     c2bh3va425z8z:1 address   3619727480 00000000D7C0A878       3 50%            1 16.67%        3

54:15:22026     5trhkkmhmr6gn:1 address   3620683008 00000000D7CF3D00       3 50%            1 16.67%        3

26:101:22028    c2bh3va425z8z:1 address   3620683008 00000000D7CF3D00       3 50%            2 33.33%        3

56:299:22020    c2bh3va425z8z:1 address   3620683008 00000000D7CF3D00       3 50%            2 33.33%        3

通过PAW_PCT,可以发现那一个P1RAW上出现最多的cbc等待,通过RAW_SQL_PCT可以知道在那一个P1RAW上,那条SQL占用最多的cbc等待,可以快速定位到出问题的sql

4.2 查询cbc发生的对象

www.htz.pw > @event_latch_cache_buffer_cache_object_by_addr.sql  

it will run long                                                 

Enter value for addr_list: ‘00000000D7C0A878′,’00000000D7CF3D00’ 

                                             OWNER                                                                

                                             OBJECT_NAME                                                          

 FILE_ID RFILE_ID       BLOCK DATA_OBJECT_ID SUB_OBJECT   OBJECT_TYPE          ADDR                   TCH    CHILD#

——– ——– ———– ————– ———— ——————– —————- ——— ———

       4        4       32310          81858 HTZ.CBC2.    TABLE                00000000D7CF3D00       219      3669

       4        4       32190          81858 HTZ.CBC2.    TABLE                00000000D7C0A878       219      2332

       1        1       22205              2 SYS.IND$.    TABLE                00000000D7C0A878         6      2332

       1        1       61600              2 SYS.COL$.    TABLE                00000000D7CF3D00         6      3669

       1        1       22205              2 SYS.COL$.    TABLE                00000000D7C0A878         6      2332

       1        1       61600              2 SYS.CLU$.    TABLE                00000000D7CF3D00         6      3669

       1        1       22205              2 SYS.CLU$.    TABLE                00000000D7C0A878         6      2332

       1        1       61600              2 SYS.C_OBJ#.  CLUSTER              00000000D7CF3D00         6      3669

       1        1       22205              2 SYS.C_OBJ#.  CLUSTER              00000000D7C0A878         6      2332

       1        1       61600              2 SYS.TAB$.    TABLE                00000000D7CF3D00         6      3669

       1        1       22205              2 SYS.TAB$.    TABLE                00000000D7C0A878         6      2332

       1        1       61600              2 SYS.LOB$.    TABLE                00000000D7CF3D00         6      3669

       1        1       22205              2 SYS.LOB$.    TABLE                00000000D7C0A878         6      2332

这里可以查询到对象的信息,可以看到是发生在表上面。一般热点块,多数出现在索引。并且block号会是相同的。

下面的工作就是分析SQL语句这些了。

4.3 通过国外大牛的latchprof脚本来分析

www.htz.pw > @latchprof.sql sid,name,laddr % cache 10000                                                             

                                                                                                                     

— LatchProf 2.00 by Tanel Poder ( http://www.tanelpoder.com )                                                       

                                                                                                                     

       SID NAME                                LADDR                  Held       Gets  Held %     Held ms Avg hold ms

———- ———————————– —————- ———- ———- ——- ———– ———–

        26 cache buffers chains                00000000D7A8A218         73          1     .73      73.073      73.073

        54 cache buffers chains                00000000D7E40BB8         71          1     .71      71.071      71.071

        79 cache buffers chains                00000000D7C9A468         70          1     .70      70.070      70.070

        54 cache buffers chains                00000000D7C36C90         70          1     .70      70.070      70.070

        54 cache buffers chains                00000000D7E62CA8         69          1     .69      69.069      69.069

        26 cache buffers chains                00000000D7A79330         68          1     .68      68.068      68.068

                                                                                                                     

6 rows selected.                                                                                                     

                                                                                                                     

                                                                                                                     

                                                                                                                                                                                                                                 

www.htz.pw > @latchprofx.sql laddr % cache 100000                                                                    

                                                                                                                     

— LatchProfX 2.00 by Tanel Poder ( http://www.tanelpoder.com )                                                      

                                                                                                                     

LADDR                  Held       Gets  Held %     Held ms Avg hold ms                                               

—————- ———- ———- ——- ———– ———–                                               

00000000D7E1A6E0       1753          2    1.75    1404.153     702.077                                               

00000000D7C67EB8        935          1     .94     748.935     748.935                                               

00000000D7AD8720        930          1     .93     744.930     744.930                                               

00000000D7FBAFB8        921          1     .92     737.721     737.721                                               

00000000D7F40C10        920          1     .92     736.920     736.920                                               

00000000D7C31070        919          1     .92     736.119     736.119                                               

00000000D7B95DD0        898          1     .90     719.298     719.298                                               

00000000D7D1A048        865          1     .87     692.865     692.865                                               

4.4 通过ASH数据查询

下面是通过ASH查询看是在那个对象上面的。

www.htz.pw > @event_latch_cache_buffer_chain_ash.sql    

Enter value for begin_date: 2014-09-16 20:30:37         

Enter value for interval_hours: 2                       

                                                        

                                          PCT           

TIME                 LADDR                TIME_ADDR ADDR_

——————– ——————– ——— —–

20140916 20 30-40    00000000D7D79CD0     18.18%    .65%

20140916 20 30-40    00000000D7F4BC80     36.36%    1.31%

20140916 20 30-40    00000000D7B62C68     45.45%    1.63%

20140916 20 40-50    00000000D7BA5480     17.65%    .98%

20140916 20 40-50    00000000D7C04F78     17.65%    .98%

20140916 20 40-50    00000000D7D51A48     17.65%    .98%

20140916 20 40-50    00000000D7F5D720     17.65%    .98%

20140916 20 40-50    00000000D7BA5610     23.53%    1.31%

20140916 20 40-50    00000000D7E4C0D8     5.88%     .33%

20140916 20 50-60    00000000D7FCC350     10.53%    1.31%

20140916 20 50-60    00000000D7AFF300     13.16%    1.63%

20140916 20 50-60    00000000D7BC2120     13.16%    2.61%

20140916 20 50-60    00000000D7BE2848     13.16%    1.63%

20140916 20 50-60    00000000D7D25BA8     18.42%    2.29%

20140916 20 50-60    00000000D7A79330     2.63%     .65%

20140916 20 50-60    00000000D7BF90F8     2.63%     .33%

20140916 20 50-60    00000000D7DF9400     2.63%     .33%

20140916 20 50-60    00000000D7AF3AC0     5.26%     .65%

20140916 20 50-60    00000000D7B102B0     5.26%     .65%

20140916 20 50-60    00000000D7D574D8     5.26%     .65%

20140916 20 50-60    00000000D7D57730     7.89%     .98%

20140916 21 0-10     00000000D7A79330     .8%       .65%

20140916 21 0-10     00000000D7A96098     .8%       .33%

20140916 21 0-10     00000000D7AD8B08     .8%       .33%

20140916 21 0-10     00000000D7B47D78     .8%       .33%

20140916 21 0-10     00000000D7EA0200     .8%       .33%

20140916 21 0-10     00000000D7F6E6D0     .8%       .33%

20140916 21 0-10     00000000D7B4D4E8     1.6%      .65%

20140916 21 0-10     00000000D7B73A88     1.6%      .65%

20140916 21 0-10     00000000D7DF3D58     1.6%      .65%

20140916 21 0-10     00000000D7EA5C90     1.6%      .65%

20140916 21 0-10     00000000D7EBAF60     1.6%      .65%

20140916 21 0-10     00000000D7F8F118     1.6%      .65%

20140916 21 0-10     00000000D7FD7A00     1.6%      .65%

20140916 21 0-10     00000000D7BC2120     2.4%      2.61%

20140916 21 0-10     00000000D7C95B08     2.4%      .98%

20140916 21 0-10     00000000D7DA01A8     2.4%      .98%

20140916 21 0-10     00000000D7E6E1C8     2.4%      .98%

20140916 21 0-10     00000000D7F14988     2.4%      .98%

20140916 21 0-10     00000000D7F5D270     2.4%      .98%

20140916 21 0-10     00000000D7FE89B0     2.4%      .98%

20140916 21 0-10     00000000D7C10308     3.2%      1.31%

20140916 21 0-10     00000000D7C85198     3.2%      1.31%

20140916 21 0-10     00000000D7CBC558     3.2%      1.31%

20140916 21 0-10     00000000D7CF9790     3.2%      1.31%

20140916 21 0-10     00000000D7D7F2B0     3.2%      1.31%

20140916 21 0-10     00000000D7DE2DA8     3.2%      1.31%

20140916 21 0-10     00000000D7E03980     3.2%      1.31%

20140916 21 0-10     00000000D7F0ED68     3.2%      1.31%

20140916 21 0-10     00000000D7FA00C8     3.2%      1.31%

20140916 21 0-10     00000000D7FDD490     3.2%      1.31%

20140916 21 0-10     00000000D7B9A280     4%        1.63%

20140916 21 0-10     00000000D7DB0F00     4%        1.63%

20140916 21 0-10     00000000D7E5D3A8     4%        1.63%

20140916 21 0-10     00000000D7EE2E00     4%        1.63%

20140916 21 0-10     00000000D7F36D98     4%        1.63%

20140916 21 0-10     00000000D7F73B20     4%        1.63%

20140916 21 0-10     00000000D7EF3E78     4.8%      1.96%

20140916 21 0-10     00000000D7C5D168     6.4%      2.61%

20140916 21 10-20    00000000D7A95D78     .87%      .33%

20140916 21 10-20    00000000D7AF93C0     .87%      .33%

20140916 21 10-20    00000000D7C31070     .87%      .33%

20140916 21 10-20    00000000D7C90398     .87%      .33%

20140916 21 10-20    00000000D7CBC620     .87%      .33%

20140916 21 10-20    00000000D7DB12E8     .87%      .33%

20140916 21 10-20    00000000D7F571A0     .87%      .33%

20140916 21 10-20    00000000D7F79678     .87%      .33%

20140916 21 10-20    00000000D7F94A18     .87%      .33%

20140916 21 10-20    00000000D7B1FD48     1.74%     .65%

20140916 21 10-20    00000000D7C146F0     1.74%     .65%

20140916 21 10-20    00000000D7C256A0     1.74%     .65%

20140916 21 10-20    00000000D7C47C40     1.74%     .65%

20140916 21 10-20    00000000D7D310C8     1.74%     .65%

20140916 21 10-20    00000000D7F68AB0     1.74%     .65%

20140916 21 10-20    00000000D7AB6568     2.61%     .98%

20140916 21 10-20    00000000D7CCBFF0     2.61%     .98%

20140916 21 10-20    00000000D7CF36C0     2.61%     .98%

20140916 21 10-20    00000000D7D1FF88     2.61%     .98%

20140916 21 10-20    00000000D7E312B0     2.61%     .98%

20140916 21 10-20    00000000D7CA5B18     3.48%     1.31%

20140916 21 10-20    00000000D7D68870     3.48%     1.31%

20140916 21 10-20    00000000D7D8EFA0     3.48%     1.31%

20140916 21 10-20    00000000D7E20300     3.48%     1.31%

20140916 21 10-20    00000000D7E25430     3.48%     1.31%

20140916 21 10-20    00000000D7E83948     3.48%     1.31%

20140916 21 10-20    00000000D7EAB590     3.48%     1.31%

20140916 21 10-20    00000000D7FCC0F8     3.48%     1.31%

20140916 21 10-20    00000000D7FE8B40     3.48%     1.31%

20140916 21 10-20    00000000D7BB1170     4.35%     1.63%

20140916 21 10-20    00000000D7EB1020     5.22%     1.96%

20140916 21 10-20    00000000D7FE28E0     5.22%     1.96%

20140916 21 10-20    00000000D7BDCDB8     6.96%     2.61%

20140916 21 10-20    00000000D7F14B18     6.96%     2.61%

20140916 21 10-20    00000000D7C047A8     8.7%      3.27%

本文固定链接: http://www.htz.pw/2014/09/17/%e6%a8%a1%e6%8b%9flatch-cache-buffers-chains%e7%ad%89%e5%be%85%e4%ba%8b%e4%bb%b6.html | 认真就输

该日志由 huangtingzhong 于2014年09月17日发表在 EVENT 分类下, 你可以发表评论,并在保留原文地址及作者的情况下引用到你的网站或博客。
原创文章转载请注明: 模拟latch: cache buffers chains等待事件 | 认真就输
关键字: , , ,