oracle里cache 20,【学习笔记】Oracle等待事件latch:cache buffers chains解决办法

最新推荐文章于 2024-07-24 04:28:59 发布

weixin_39958112

最新推荐文章于 2024-07-24 04:28:59 发布

阅读量397

点赞数

文章标签： oracle里cache 20

【学习笔记】Oracle等待事件latch:cache buffers chains解决办法

时间:2016-10-30 10:28 来源:Oracle研究中心作者:HTZ 点击:

次

天萃荷净

Oracle研究中心学习笔记：运维DBA反映Oracle数据库服务器CPU使用率一直到100%，分析Oracle等待事件由latch:cache buffers chains引起.结合MOS官方解决办法。

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

1.SQL性能不好。

2.热块

3.BUG。

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

1.环境介绍

oracleplus.net> 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

oracleplus.net> !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这个表。

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

Table created.

oracleplus.net> 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;

oracleplus.net> 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

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

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

| 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

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

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;

oracleplus.net> 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;Oracleо

14 /

Procedure created.

3.现象模拟

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

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 查询等待事件

从当前等待事件查询

oracleplus.net> @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发生的对象

oracleplus.net> @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脚本来分析

oracleplus.net> @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

oracleplus.net> @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查询看是在那个对象上面的。

oracleplus.net> @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 | 认真就输

--------------------------------------ORACLE-DBA----------------------------------------

最权威、专业的Oracle案例资源汇总之【学习笔记】Oracle等待事件latch:cache buffers chains解决办法

weixin_39958112

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
复制链接

分享到 QQ

分享到新浪微博

扫一扫