[20150121]关于latch cache buffers chains事件.txt
--最近一直在看,关于latch: cache buffers chains的等待事件。
--造成这个事件的主要原因有2个：一个是热链，另外一个是热块。我喜欢通过例子来说明，实际上书上写的更加详细。
--这里主要测试热块造成的情况。
1.建立测试环境：
SCOTT@test> @ver1
PORT_STRING VERSION BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx 11.2.0.3.0 Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
SCOTT@test> create table t as select rownum id,dbms_random.string('u',100) data from dual connect by levelTable created.
SCOTT@test> @stats t
exec sys.dbms_stats.gather_table_stats ( OwnName => user,TabName => 't',Estimate_Percent => NULL,Method_Opt => 'FOR ALL COLUMNS SIZE 1 ',Cascade => True ,No_Invalidate => false)
PL/SQL procedure successfully completed.
SCOTT@test> select rowid,t.* from t where id=42;
ROWID ID DATA
------------------ ---------- ----------------------------------------------------------------------------------------------------
AABIulAAEAAAACjAAp 42 ETOMPKBSMQOOVKWYBDLQHTSQMNBEDPEXQVGMDZXTJPBSKJFIXKMOPTFQHPQSAFQDMUBZKTSKTMIRDYYKYOTTIJOYLWFEFNXVJCRU
SCOTT@test> @lookup_rowid AABIulAAEAAAACjAAp
OBJECT FILE BLOCK ROW DBA TEXT
---------- ---------- ---------- ---------- -------------------- ----------------------------------------
297893 4 163 41 4,163 alter system dump datafile 4 block 163 ;
--建立测试脚本:
$ cat f1.sql
declare
m_id number;
m_data varchar2(200);
begin
for i in 1 .. 1e9 loop
select data into m_data from t where rowid = 'AABIulAAEAAAACjAAp';
end loop;
end ;
/
$ cat f.sh
#! /bin/bash
sqlplus -s scott/btbtms @$1 &
sqlplus -s scott/btbtms @$1 &
sqlplus -s scott/btbtms @$1 &
sqlplus -s scott/btbtms @$1 &
2.测试1：
$ source f.sh f1.sql
SCOTT@test> select sid,seq#,event,state,wait_time_micro,seconds_in_wait from v$session where wait_class'Idle' order by event ;
SID SEQ# EVENT STATE WAIT_TIME_MICRO SECONDS_IN_WAIT
---------- ---------- ---------------------------------------- ------------------- --------------- ---------------
5 1927 SQL*Net message to client WAITED SHORT TIME 3 0
395 679 cursor: pin S WAITED KNOWN TIME 11206 0
14 666 cursor: pin S WAITED KNOWN TIME 10918 0
597 644 latch: cache buffers chains WAITED SHORT TIME 370 0
206 647 latch: cache buffers chains WAITED SHORT TIME 402 0
--也就是讲我们以select方式访问相同数据块，会出现latch: cache buffers chains。
SCOTT@test> select sid,seq#,event,state,wait_time_micro,seconds_in_wait,p1raw,p2raw from v$session where wait_class'Idle' and event = 'latch: cache buffers chains' order by event ;
SID SEQ# EVENT STATE WAIT_TIME_MICRO SECONDS_IN_WAIT P1RAW P2RAW
---------- ---------- ---------------------------------------- ------------------- --------------- --------------- ---------------- ----------------
14 2862 latch: cache buffers chains WAITED SHORT TIME 625 0 00000000BCA47650 000000000000009B
597 3025 latch: cache buffers chains WAITED SHORT TIME 168 0 00000000BCA47650 000000000000009B
select * from x$bh where dbarfil=4 and dbablk=163 and obj=297893;
Record View
As of: 2015/1/21 15:18:13
ADDR: 0000002A97658130
INDX: 43505
INST_ID: 1
HLADDR: 00000000BCA47650 BLSIZ: 8192
NXT_HASH: 000000008DFC8628
PRV_HASH: 00000000BCA48470
NXT_REPL: 000000009CFD1F90
PRV_REPL: 0000000097F9C040
FLAG: 0
FLAG2: 0
LOBID: 0
RFLAG: 0
SFLAG: 0
LRU_FLAG: 0
TS#: 4
FILE#: 4
DBARFIL: 4
DBABLK: 163
CLASS: 1
STATE: 1
MODE_HELD: 0
CHANGES: 1
CSTATE: 0
LE_ADDR: 00
DIRTY_QUEUE: 0
SET_DS: 00000000BCF537A0
OBJ: 297893
BA: 00000000A15B4000
CR_SCN_BAS: 0
CR_SCN_WRP: 0
CR_XID_USN: 0
CR_XID_SLT: 0
CR_XID_SQN: 0
CR_UBA_FIL: 0
CR_UBA_BLK: 0
CR_UBA_SEQ: 0
CR_UBA_REC: 0
CR_SFL: 0
CR_CLS_BAS: 0
CR_CLS_WRP: 0
LRBA_SEQ: 0
LRBA_BNO: 0
HSCN_BAS: 4294967295
HSCN_WRP: 65535
HSUB_SCN: 65535
US_NXT: 00000000A1F9E1E8
US_PRV: 00000000A1F9E1E8
WA_NXT: 00000000A1F9E1F8
WA_PRV: 00000000A1F9E1F8
OQ_NXT: 0000000093FA74E8
OQ_PRV: 00000000B4802B30
AQ_NXT: 0000000094F68398
AQ_PRV: 00000000B4802B20
OBJ_FLAG: 240
TCH: 232
TIM: 1421824595
CR_RFCNT: 2
SHR_RFCNT: 0
3.测试2设置表T1只读看看：
SCOTT@test> alter table t read only;
Table altered.
SCOTT@test> select sid,seq#,event,state,wait_time_micro,seconds_in_wait from v$session where wait_class'Idle' order by event ;
SID SEQ# EVENT STATE WAIT_TIME_MICRO SECONDS_IN_WAIT
---------- ---------- ---------------------------------------- ------------------- --------------- ---------------
5 2015 SQL*Net message to client WAITED SHORT TIME 3 0
14 3797 cursor: pin S WAITED SHORT TIME 1361 1
395 3826 latch: cache buffers chains WAITED SHORT TIME 152 0
597 3831 latch: cache buffers chains WAITED SHORT TIME 160 0
206 3764 latch: cache buffers chains WAITED SHORT TIME 189 0
--问题依旧，也就是讲即使把表T设置为只读，再访问数据块依旧需要获取latch: cache buffers chains。
4.测试3建立非唯一索引:
SCOTT@test> create index i_t_id on t(id) ;
Index created.
SCOTT@test> alter table t read write;
Table altered.
$ cat f2.sql
declare
m_id number;
m_data varchar2(200);
begin
for i in 1 .. 1e9 loop
-- select data into m_data from t where rowid = 'AABIulAAEAAAACjAAp';
select data into m_data from t where id=42 ;
end loop;
end ;
/
--停止前面启动的会话。fg 放到前台，然后ctrl+c,exit,就可以退出。批量执行f2.sql
$ source f.sh f2.sql
SCOTT@test> select sid,seq#,event,state,wait_time_micro,seconds_in_wait from v$session where wait_class'Idle' order by event ;
SID SEQ# EVENT STATE WAIT_TIME_MICRO SECONDS_IN_WAIT
---------- ---------- ---------------------------------------- ------------------- --------------- ---------------
5 2342 SQL*Net message to client WAITED SHORT TIME 3 0
398 194 cursor: pin S WAITED KNOWN TIME 11222 0
14 177 cursor: pin S WAITED KNOWN TIME 10511 0
595 185 latch: cache buffers chains WAITED SHORT TIME 229 0
206 172 latch: cache buffers chains WAITED SHORT TIME 229 0
--可以发现非唯一索引存在的情况下，依旧出现latch: cache buffers chains。在这种情况下，需要访问索引root节点，分支节点，叶
--子节点，以及相应的数据块。
--注意：如果你不能捕获latch: cache buffers chains，也许机器太快了(因为通过索引访问，减少了数据块的访问,遇到latch: cache
--buffers chains的几率下降)，你可以适当加大执行的会话的数量。
4.看看不访问数据块的情况：
--停止前面启动的会话。fg 放到前台，然后ctrl+c,exit,就可以退出。批量执行f2.sql
$ cat f3.sql
declare
m_id number;
m_data varchar2(200);
begin
for i in 1 .. 1e9 loop
-- select data into m_data from t where rowid = 'AABIulAAEAAAACjAAp';
-- select data into m_data from t where id=42 ;
select id into m_id from t where id=42 ;
end loop;
end ;
/
$ source f.sh f3.sql
--这样不访问数据块。
SCOTT@test> select sid,seq#,event,state,wait_time_micro,seconds_in_wait from v$session where wait_class'Idle' order by event ;
SID SEQ# EVENT STATE WAIT_TIME_MICRO SECONDS_IN_WAIT
---------- ---------- ---------------------------------------- ------------------- --------------- ---------------
5 2422 SQL*Net message to client WAITED SHORT TIME 4 0
399 70 cursor: pin S WAITED SHORT TIME 50 1
597 69 cursor: pin S WAITED SHORT TIME 8 0
14 63 latch: cache buffers chains WAITED SHORT TIME 256 1
206 45 latch: cache buffers chains WAITED SHORT TIME 225 1
--可以发现即使不访问数据块依旧出现latch: cache buffers chains。看看如何定位。
SCOTT@test> select sid,seq#,event,state,wait_time_micro,seconds_in_wait,p1raw,p2raw from v$session where wait_class'Idle' and event = 'latch: cache buffers chains' order by event ;
SID SEQ# EVENT STATE WAIT_TIME_MICRO SECONDS_IN_WAIT P1RAW P2RAW
---------- ---------- ---------------------------------------- ------------------- --------------- --------------- ---------------- ----------------
206 1621 latch: cache buffers chains WAITED SHORT TIME 2645 0 00000000BCAC7C58 000000000000009B
399 1828 latch: cache buffers chains WAITED SHORT TIME 25 0 00000000BCAC7C58 000000000000009B
--P1RAW=00000000BCAC7C58,与前面的不同，说明是不同的地址。
SCOTT@test> select object_id,data_object_id from dba_objects where owner=user and object_name='I_T_ID';
OBJECT_ID DATA_OBJECT_ID
---------- --------------
297894 297894
select * from x$bh where dbarfil=4 and obj=297894 and HLADDR='00000000BCAC7C58';
Record View
As of: 2015/1/21 15:58:14
ADDR: 0000002A97658130
INDX: 54119
INST_ID: 1
HLADDR: 00000000BCAC7C58
BLSIZ: 8192
NXT_HASH: 0000000090FBF678
PRV_HASH: 00000000BCAC9550
NXT_REPL: 0000000092FBBA20
PRV_REPL: 000000009AF6ACF0
FLAG: 0
FLAG2: 0
LOBID: 0
RFLAG: 0
SFLAG: 0
LRU_FLAG: 0
TS#: 4
FILE#: 4
DBARFIL: 4
DBABLK: 1804
CLASS: 1
STATE: 1
MODE_HELD: 0
CHANGES: 1
CSTATE: 0
LE_ADDR: 00
DIRTY_QUEUE: 0
SET_DS: 00000000BCF537A0
OBJ: 297894
BA: 0000000099D4C000
CR_SCN_BAS: 0
CR_SCN_WRP: 0
CR_XID_USN: 0
CR_XID_SLT: 0
CR_XID_SQN: 0
CR_UBA_FIL: 0
CR_UBA_BLK: 0
CR_UBA_SEQ: 0
CR_UBA_REC: 0
CR_SFL: 0
CR_CLS_BAS: 0
CR_CLS_WRP: 0
LRBA_SEQ: 0
LRBA_BNO: 0
HSCN_BAS: 4294967295
HSCN_WRP: 65535
HSUB_SCN: 65535
US_NXT: 0000000099FE6428
US_PRV: 0000000099FE6428
WA_NXT: 0000000099FE6438
WA_PRV: 0000000099FE6438
OQ_NXT: 000000009DF9AFC8
OQ_PRV: 00000000B4802A90
AQ_NXT: 000000009DF9AFD8
AQ_PRV: 00000000B4802A80
OBJ_FLAG: 240
TCH: 58
TIM: 1421827080
CR_RFCNT: 0
SHR_RFCNT: 0
$ cat which_obj.sql
define __FILE = &1
define __BLOCK = &2
set verify off
select * --owner,segment_name
from dba_extents
where file_id = &__FILE
and &__BLOCK between block_id and block_id + blocks - 1
-- and rownum = 1
;
--定位是什么数据块。
SCOTT@test> @which_obj 4 1804
OWNER SEGMENT_NAME PARTITION_NAME SEGMENT_TYPE TABLESPACE_NAME EXTENT_ID FILE_ID BLOCK_ID BYTES BLOCKS RELATIVE_FNO
------ ------------- --------------- ------------- -------------------------- ---------- ---------- ---------- ---------- ------------
SCOTT I_T_ID INDEX USERS 0 4 1800 65536 8 4
--转储索引tree看看:
SCOTT@test> alter session set events 'immediate trace name treedump level 297894';
Session altered.
----- begin tree dump
branch: 0x100070b 16779019 (0: nrow: 222, level: 1)
leaf: 0x100070c 16779020 (-1: nrow: 485 rrow: 485)
leaf: 0x100070d 16779021 (0: nrow: 479 rrow: 479)
leaf: 0x100070e 16779022 (1: nrow: 479 rrow: 479)
leaf: 0x100070f 16779023 (2: nrow: 479 rrow: 479)
leaf: 0x1000710 16779024 (3: nrow: 479 rrow: 479)
leaf: 0x1000711 16779025 (4: nrow: 478 rrow: 478)
leaf: 0x1000712 16779026 (5: nrow: 479 rrow: 479)
leaf: 0x1000713 16779027 (6: nrow: 479 rrow: 479)
leaf: 0x1000714 16779028 (7: nrow: 479 rrow: 479)
leaf: 0x1000715 16779029 (8: nrow: 478 rrow: 478)
leaf: 0x1000716 16779030 (9: nrow: 479 rrow: 479)
leaf: 0x1000717 16779031 (10: nrow: 479 rrow: 479)
....
--id=42应该在叶子节点的第一块，因为nrow=485.
SCOTT@test> @dfb 100070c
RFILE# BLOCK#
---------- ----------
4 1804
TEXT
----------------------------------------
alter system dump datafile 4 block 1804
--基本信息都对上了，当然具体定位不能使用这种方法，但是还是可以确定在访问叶子节点是出现latch: cache buffers chains。
--执行如下可以确定执行的sql语句。
select * from V$ACTIVE_SESSION_HISTORY where event = 'latch: cache buffers chains' and sample_time>=sysdate-25/1440
order by sample_time desc;
--因为取样的问题，访问V$ACTIVE_SESSION_HISTORY发现仅仅出现4次。但是有1点可以确定没有在索引的root节点出现latch: cache
--buffers chains.
5.测试4，因为建立的表不够大，索引的level=1.
----停止前面启动的会话。fg 放到前台，然后ctrl+c,exit,就可以退出。批量执行f3.sql
SCOTT@test> insert into t select 1e5+rownum id,dbms_random.string('u',10) data from dual connect by level900000 rows created.
SCOTT@test> commit ;
Commit complete.
SCOTT@test> alter session set events 'immediate trace name treedump level 297894';
Session altered.
*** 2015-01-21 16:23:14.980
branch: 0x100070b 16779019 (0: nrow: 4, level: 2)
branch: 0x1000f25 16781093 (-1: nrow: 671, level: 1)
leaf: 0x100070c 16779020 (-1: nrow: 485 rrow: 485)
leaf: 0x100070d 16779021 (0: nrow: 479 rrow: 479)
leaf: 0x100070e 16779022 (1: nrow: 479 rrow: 479)
... 出现了level=2.
--继续上面的测试,启动8个会话：
$ source f.sh f3.sql
$ source f.sh f3.sql
SCOTT@test> select sid,seq#,event,state,wait_time_micro,seconds_in_wait from v$session where wait_class'Idle' order by event ;
SID SEQ# EVENT STATE WAIT_TIME_MICRO SECONDS_IN_WAIT
---------- ---------- ---------------------------------------- ------------------- --------------- ---------------
5 13524 SQL*Net message to client WAITED SHORT TIME 2 0
204 148 cursor: pin S WAITED KNOWN TIME 20961 1
597 123 latch: cache buffers chains WAITED SHORT TIME 538 0
395 128 latch: cache buffers chains WAITING 5497 0
398 127 latch: cache buffers chains WAITED SHORT TIME 573 0
592 132 latch: cache buffers chains WAITING 5565 0
15 122 latch: cache buffers chains WAITED SHORT TIME 547 0
14 144 latch: cache buffers chains WAITING 5526 0
207 124 latch: cache buffers chains WAITING 5483 0
9 rows selected.
SCOTT@test> select p1,count(*) from V$ACTIVE_SESSION_HISTORY where event = 'latch: cache buffers chains' and sample_time>=sysdate-15/1440 group by p1;
P1 COUNT(*)
---------- ----------
3165420632 217
SCOTT@test> @10to16 3165420632
10 to 16 HEX REVERSE16
-------------- ------------------
00000bcac7c58 0x587cacbc
SCOTT@test> select sid,seq#,event,state,wait_time_micro,seconds_in_wait,p1raw,p2raw from v$session where wait_class'Idle' and event = 'latch: cache buffers chains' order by event ;
SID SEQ# EVENT STATE WAIT_TIME_MICRO SECONDS_IN_WAIT P1RAW P2RAW
---- ----- ---------------------------- ------------------- --------------- --------------- ---------------- ----------------
398 1677 latch: cache buffers chains WAITED SHORT TIME 505 1 00000000BCAC7C58 000000000000009B
--可以发现仅仅在访问叶子节点上是出现latch: cache buffers chains。
6.测试5，重复执行f2.sql看看。
---停止前面启动的会话。fg 放到前台，然后ctrl+c,exit,就可以退出。批量执行f2.sql.
$ cat f2.sql
declare
m_id number;
m_data varchar2(200);
begin
for i in 1 .. 1e9 loop
-- select data into m_data from t where rowid = 'AABIulAAEAAAACjAAp';
select data into m_data from t where id=42 ;
end loop;
end ;
/
$ source f.sh f2.sql
$ source f.sh f2.sql
SCOTT@test> select p1,count(*) from V$ACTIVE_SESSION_HISTORY where event = 'latch: cache buffers chains' and sample_time>=sysdate-5/1440 group by p1;
P1 COUNT(*)
---------- ----------
3164894800 21
3165420632 39
SCOTT@test> @10to16 3164894800
10 to 16 HEX REVERSE16
-------------- ------------------
00000bca47650 0x5076a4bc
--可以发现出现latch: cache buffers chains等待事件，仅仅出现数据块以及索引的叶子节点上，其它索引的根节点以及叶子节点都不
--会出现。
7.测试6，删除索引，建议唯一索引,重复执行f2.sql看看。
---停止前面启动的会话。fg 放到前台，然后ctrl+c,exit,就可以退出。批量执行f2.sql.
SCOTT@test> drop index i_t_id ;
Index dropped.
SCOTT@test> create unique index i_t_id on t(id) ;
Index created.
SCOTT@test> select object_id,data_object_id from dba_objects where owner=user and object_name='I_T_ID';
OBJECT_ID DATA_OBJECT_ID
---------- --------------
297895 297895
SCOTT@test> alter session set events 'immediate trace name treedump level 297895';
Session altered.
*** 2015-01-21 16:51:33.232
branch: 0x100070b 16779019 (0: nrow: 3, level: 2)
branch: 0x1000f72 16781170 (-1: nrow: 732, level: 1)
leaf: 0x100070c 16779020 (-1: nrow: 520 rrow: 520)
leaf: 0x100070d 16779021 (0: nrow: 513 rrow: 513)
leaf: 0x100070e 16779022 (1: nrow: 513 rrow: 513)
$ cat f2.sql
declare
m_id number;
m_data varchar2(200);
begin
for i in 1 .. 1e9 loop
-- select data into m_data from t where rowid = 'AABIulAAEAAAACjAAp';
select data into m_data from t where id=42 ;
end loop;
end ;
/
SCOTT@test> select sid,seq#,event,state,wait_time_micro,seconds_in_wait from v$session where wait_class'Idle' order by event ;
SID SEQ# EVENT STATE WAIT_TIME_MICRO SECONDS_IN_WAIT
---------- ---------- ---------------------------------------- ------------------- --------------- ---------------
5 16040 SQL*Net message to client WAITED SHORT TIME 3 0
15 39 cursor: pin S WAITED KNOWN TIME 66245 0
204 44 cursor: pin S WAITED KNOWN TIME 99815 0
597 45 cursor: pin S WAITED KNOWN TIME 28683 1
395 55 cursor: pin S WAITED KNOWN TIME 99952 0
400 46 cursor: pin S WAITED KNOWN TIME 99372 1
590 50 cursor: pin S WAITED SHORT TIME 45 0
14 43 cursor: pin S WAITED KNOWN TIME 99993 0
207 41 cursor: pin S WAITED KNOWN TIME 99461 0
9 rows selected.
--这样不再出现latch: cache buffers chains。而出现cursor: pin S等待事件。
SCOTT@test> select p1,count(*) from V$ACTIVE_SESSION_HISTORY where event = 'latch: cache buffers chains' and sample_time>=sysdate-5/1440 group by p1;
no rows selected
--现在可以得出一些总结：
--1.在唯一索引的条件下，执行的sql语句等值查询，不会出现latch: cache buffers chains。不管数据块，索引root节点，分支节点，
-- 叶子节点
--2.而通过非唯一索引访问，访问root节点，分支节点不会出现latch: cache buffers chains。其它数据块以及叶子节点会出现。
--3.只读表访问也会出现latch: cache buffers chains，通过rowid方式依旧。
