分享程序员开发的那些事...
更新时间：2022-09-13 10:41:18
[20150205]关于位图索引6.txt
--许多人知道在oltp系统不适合使用位图索引.它的索引的记录结构如下是:
字段0:键值
字段1:开始rowid
字段2:结束rowid
字段3:位图信息,指示那行记录,位图1=>表示存在.位图0=>表示不存在.
--昨天主要学习了解了字段3的相关信息,昨晚想起以前itpub的讨论,讲位图索引很容易出现阻塞,主要是如果事务发生在同一个行片,
--如果不在同一个行片,就不会出现阻塞,还是通过例子来说明:
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
--drop table t purge;
create table t(id number , name varchar2(10), status varchar2(1));
insert into t select rownum-1 id,dbms_random.string('X',10) c20,decode(mod((rownum-1),8),0,'Y','N') c1 from dual connect by levelcommit ;
SCOTT@test> select * from t where status='Y' ;
ID NAME S
---------- -------------------- -
0 NC2C2RJYZJ Y
8 9A01DWFONE Y
16 KAGEJYP0P9 Y
24 1625QJBKKM Y
32 M4YFHZNSYM Y
40 XGM1VKPXLY Y
48 U6UIUBLYZL Y
56 HH75RCZP2V Y
64 M2VZO0E3EJ Y
update t set status='N' where id=0;
commit ;
create bitmap index ib_t_status on t(status);
alter system checkpoint ;
alter system dump datafile 4 block 531 ;
row#0[8003] flag: ------, lock: 0, len=29
col 0; len 1; (1): 4e
col 1; len 6; (6): 01 00 00 a7 00 00
col 2; len 6; (6): 01 00 00 a7 00 47
col 3; len 10; (10): cf ff fe fe fe fe fe fe fe 01
row#1[7975] flag: ------, lock: 0, len=28
col 0; len 1; (1): 59
col 1; len 6; (6): 01 00 00 a7 00 08
col 2; len 6; (6): 01 00 00 a7 00 47
col 3; len 9; (9): cf 01 01 01 01 01 01 01 01
update t set status='Y' where id=0;
commit ;
SCOTT@test> select rowid,t.* from t where status='Y';
ROWID ID NAME S
------------------ ---------- -------------------- -
AABJAiAAEAAAACnAAA 0 NC2C2RJYZJ Y
AABJAiAAEAAAACnAAI 8 9A01DWFONE Y
AABJAiAAEAAAACnAAQ 16 KAGEJYP0P9 Y
AABJAiAAEAAAACnAAY 24 1625QJBKKM Y
AABJAiAAEAAAACnAAg 32 M4YFHZNSYM Y
AABJAiAAEAAAACnAAo 40 XGM1VKPXLY Y
AABJAiAAEAAAACnAAw 48 U6UIUBLYZL Y
AABJAiAAEAAAACnAA4 56 HH75RCZP2V Y
AABJAiAAEAAAACnABA 64 M2VZO0E3EJ Y
9 rows selected.
SCOTT@test> @lookup_rowid AABJAiAAEAAAACnAAA
OBJECT FILE BLOCK ROW DBA TEXT
---------- ---------- ---------- ---------- -------------------- ----------------------------------------
299042 4 167 0 4,167 alter system dump datafile 4 block 167 ;
alter system checkpoint ;
alter system dump datafile 4 block 531 ;
row#0[7946] flag: ------, lock: 2, len=29
col 0; len 1; (1): 4e
col 1; len 6; (6): 01 00 00 a7 00 00
col 2; len 6; (6): 01 00 00 a7 00 47
col 3; len 10; (10): cf fe fe fe fe fe fe fe fe 01
row#1[7902] flag: ------, lock: 2, len=25
col 0; len 1; (1): 59
col 1; len 6; (6): 00 00 00 00 00 00
col 2; len 6; (6): 01 00 00 a7 00 07
col 3; len 6; (6): c0 93 f9 80 e8 05
row#2[7975] flag: ------, lock: 0, len=28
col 0; len 1; (1): 59
col 1; len 6; (6): 01 00 00 a7 00 08
col 2; len 6; (6): 01 00 00 a7 00 47
col 3; len 9; (9): cf 01 01 01 01 01 01 01 01
----- end of leaf block dump -----
--可以发现经过这样操作后,status='Y' ,被分成了两片,而且很奇怪的是开始rowid是00 00 00 00 00 00,结束01 00 00 a7 00 07.
2.先看看col 3; len 6; (6): c0 93 f9 80 e8 05的情况:
-- c0 拆开
--11 000 000
-- 11 大于192.表示Multi-Byte Groups
-- 000 表示 ???????
-- 000 表示1个字节长度.
--不懂先放一下.
-- f9 80 e8
-- 11 111 001
-- 11 大于192.表示Multi-Byte Groups
-- 111,必须考虑下面的扩展 0x80(128),注意这里高位是1.第3个字节是e8.
-- 001 表示2个字节长度. 不对,后面仅剩下1个字节.
--分析不出来,那位知道,先放一放. 开始rowid=00 00 00 00 00 00,如何确定范围呢?先存疑.
3.可以发现存在2个行片:(注意不要提交)
--会话1:
update t set status='N' where id=0;
--会话2:
update t set status='N' where id=64;
--挂起!
--这样一定会存在阻塞.虽然id=0,64的status='Y'不在同一个行片,但是这些记录在status='N'是在一个行片.
$ cat viewlock.sql
column sid format 99999
column username format a10
column osuser format a10
column machine format a10
column lock_type format a12
column mode_held format a10
column mode_requested format a10
column lock_id1 format a10
column lock_id2 format a10
column owner format a6
column object_type format a10
column object_name format a20
column block format a5
column lockwait format a20
SELECT lk.SID, se.username, se.osuser, se.machine,
DECODE (lk.TYPE, 'TX', 'Transaction', 'TM', 'DML', 'UL', 'PL/SQL User Lock', lk.TYPE) lock_type,
DECODE (lk.lmode,
0, 'None',
1, 'Null',
2, 'Row-S (SS)',
3, 'Row-X (SX)',
4, 'Share',
5, 'S/Row-X (SSX)',
6, 'Exclusive',
TO_CHAR (lk.lmode)
) mode_held,
DECODE (lk.request,
0, 'None',
1, 'Null',
2, 'Row-S (SS)',
3, 'Row-X (SX)',
4, 'Share',
5, 'S/Row-X (SSX)',
6, 'Exclusive',
TO_CHAR (lk.request)
) mode_requested,
TO_CHAR (lk.id1) lock_id1, TO_CHAR (lk.id2) lock_id2, ob.owner, ob.object_type, ob.object_name,
DECODE (lk.BLOCK, 0, 'No', 1, 'Yes', 2, 'Global') BLOCK, se.lockwait
FROM v$lock lk, dba_objects ob, v$session se
WHERE lk.TYPE IN ('TX','TM', 'UL') AND lk.SID = se.SID AND lk.id1 = ob.object_id(+);
SCOTT@test> @viewlock
SID USERNAME OSUSER MACHINE LOCK_TYPE MODE_HELD MODE_REQUE LOCK_ID1 LOCK_ID2 OWNER OBJECT_TYP OBJECT_NAME BLOCK LOCKWAIT
------ ---------- ---------- ---------- ------------ ---------- ---------- ---------- ---------- ------ ---------- -------------------- ----- --------------------
396 SCOTT oracle11g hisdg DML Row-X (SX) None 299042 0 SCOTT TABLE T No
595 SCOTT oracle11g hisdg DML Row-X (SX) None 299042 0 SCOTT TABLE T No 00000000BDC94740
595 SCOTT oracle11g hisdg Transaction Exclusive None 589825 30343 No 00000000BDC94740
396 SCOTT oracle11g hisdg Transaction Exclusive None 327711 46386 Yes
595 SCOTT oracle11g hisdg Transaction None Share 327711 46386 No 00000000BDC94740
4.如果两个修改成不同值,挂起就不会出现.
--会话1:
rollback;
update t set status='N' where id=0;
--会话2:
rollback;
update t set status='A' where id=64;
--没有阻塞.
--id=0 ,id=64,开始status='Y',分别属于不同的行片.但是修改为不同的值,一个修改为'N',另外一个修改为'A',这样可以分别操作不同
--的行片,就不会出现阻塞.
总结:
1.oltp系统使用位图索引要注意.
2.自己以为搞懂了col3的含义,还是有不懂的.
row#1[7902] flag: ------, lock: 2, len=25
col 0; len 1; (1): 59
col 1; len 6; (6): 00 00 00 00 00 00
col 2; len 6; (6): 01 00 00 a7 00 07
col 3; len 6; (6): c0 93 f9 80 e8 05
--不知道那位知道.