昨天一客户环境CPU使用达到100%，最后定位是由于cbc等待事件导致的,因为latch要消耗CPU的。其实CBC发生的原因一般就3个：
|
1，SQL性能不好。 2，热块 3，BUG。 |
下面模拟一下cbc，重点在于介绍几个脚本，用于快速定位CBC发生的对象与SQLID。不介绍怎么解决CBC，因为不同的原因解决的办法不同，如SQL性能不好，需要优化SQL，那又是另一门技术，再如热块，根据对象的不同，解决的方法也很好，如果自己不会，欢迎加入QQ群：
。
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个并发，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 查询等待事件
从当前等待事件查询
|
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% |
模拟latch: cache buffers chains等待事件：等您坐沙发呢！