分享程序员开发的那些事...
更新时间：2022-04-14 07:13:47
[20140509]关于索引问题.txt
--今天检查生产系统,发现一个奇怪的现象,对应的表和索引基本一样大,自己做一些检查:
> @ver
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
> column PCT_INCREASE noprint
> column FREELISTS noprint
> column FREELIST_GROUPS noprint
> column PARTITION_NAME noprint
> select * from dba_segments where (segment_name='PK_MS_YJ02' or segment_name='MS_YJ02') and owner='XXXXXX_HIS';
OWNER SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME HEADER_FILE HEADER_BLOCK BYTES BLOCKS EXTENTS INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS RELATIVE_FNO BUFFER_
---------- ------------- ------------- ---------------- ----------- ------------ ---------- ---------- ---------- -------------- ----------- ----------- ----------- ------------ -------
XXXXXX_HIS MS_YJ02 TABLE XXXXXX_HIS 7 16083 464519168 56704 129 196608 1 2147483645 7 DEFAULT
XXXXXX_HIS PK_MS_YJ02 INDEX XXXXXX_HIS 7 16107 461373440 56320 126 131072 1 2147483645 7 DEFAULT
--可以发现占用的块大小56704,56320,两者基本一样,why?
> select * from dba_objects where object_name in('PK_MS_YJ02' ,'MS_YJ02') and owner='XXXXXX_HIS';
OWNER OBJECT_NAME SUBOBJECT_ OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE STATUS T G S
---------- -------------------- ---------- ---------- -------------- ------------------- ------- - - -
XXXXXX_HIS MS_YJ02 57479 74728 TABLE VALID N N N
XXXXXX_HIS PK_MS_YJ02 57480 74727 INDEX VALID N N N
> @desc XXXXXX_HIS.MS_YJ02
Name Null? Type
----------------------- -------- ----------------
SBXH NOT NULL NUMBER(18)
YJXH NOT NULL NUMBER(18)
YLXH NOT NULL NUMBER(18)
XMLX NOT NULL NUMBER(2)
YJZX NOT NULL NUMBER(1)
YLDJ NOT NULL NUMBER(10,2)
YLSL NOT NULL NUMBER(8,2)
HJJE NOT NULL NUMBER(12,2)
FYGB NOT NULL NUMBER(18)
ZFBL NOT NULL NUMBER(5,3)
BZXX VARCHAR2(1500)
DZBL NOT NULL NUMBER(6,3)
--主键是SBXH,才一个字段,是顺序增加的.这样按照道理索引许多情况下是leaf node 90-10 splits.实际上这个分裂100%对1条记录分裂.
--转储索引结构:(注意使用object_id)
> alter session set events 'immediate trace name treedump level 57480';
--使用好大,转储很慢,取出其中一截看看:
branch: 0x1c03eec 29376236 (0: nrow: 155, level: 2)
branch: 0x1eaf36d 32174957 (-1: nrow: 360, level: 1)
leaf: 0x1c03ef0 29376240 (-1: nrow: 394 rrow: 394)
leaf: 0x1c03eef 29376239 (0: nrow: 396 rrow: 396)
leaf: 0x1c03eed 29376237 (1: nrow: 468 rrow: 468)
leaf: 0x1c03ef4 29376244 (2: nrow: 375 rrow: 375)
leaf: 0x1c03eee 29376238 (3: nrow: 315 rrow: 315)
leaf: 0x1c03ef5 29376245 (4: nrow: 352 rrow: 352)
leaf: 0x1c03ef6 29376246 (5: nrow: 291 rrow: 291)
leaf: 0x1c03ef1 29376241 (6: nrow: 309 rrow: 309)
leaf: 0x1c03ef7 29376247 (7: nrow: 282 rrow: 282)
leaf: 0x1c03ef8 29376248 (8: nrow: 285 rrow: 285)
leaf: 0x1c03ef3 29376243 (9: nrow: 445 rrow: 445)
...
branch: 0x43f97fc 71276540 (153: nrow: 384, level: 1)
leaf: 0x43f97ec 71276524 (-1: nrow: 129 rrow: 129)
leaf: 0x43f97a8 71276456 (0: nrow: 129 rrow: 128)
leaf: 0x43f9934 71276852 (1: nrow: 129 rrow: 129)
leaf: 0x43f97b8 71276472 (2: nrow: 143 rrow: 143)
leaf: 0x43f9944 71276868 (3: nrow: 129 rrow: 129)
leaf: 0x43f99bd 71276989 (4: nrow: 144 rrow: 136)
...
leaf: 0x43f9703 71276291 (370: nrow: 130 rrow: 130)
leaf: 0x43f99f7 71277047 (371: nrow: 130 rrow: 130)
leaf: 0x43f9a1d 71277085 (372: nrow: 129 rrow: 129)
leaf: 0x43f9a2d 71277101 (373: nrow: 131 rrow: 131)
leaf: 0x43f9810 71276560 (374: nrow: 129 rrow: 129)
leaf: 0x43f98b2 71276722 (375: nrow: 125 rrow: 125)
leaf: 0x43f9965 71276901 (376: nrow: 129 rrow: 129)
leaf: 0x43f9a4b 71277131 (377: nrow: 132 rrow: 132)
leaf: 0x43f9736 71276342 (378: nrow: 129 rrow: 129)
leaf: 0x43f9a4d 71277133 (379: nrow: 130 rrow: 130)
leaf: 0x43f97d9 71276505 (380: nrow: 129 rrow: 129)
leaf: 0x43f99a0 71276960 (381: nrow: 125 rrow: 125)
leaf: 0x43f977a 71276410 (382: nrow: 24 rrow: 24)
----- end tree dump
--可以发现一个特点越往后面,一块保存的记录越少.前面可以达到3XX,而后面基本维持在13X上下.
--转储dba= 0x43f99a0 71276960看看.
> @dfb 43f99a0
RFILE# BLOCK#
---------- ----------
16 4168096
TEXT
----------------------------------------------
alter system dump datafile 16 block 4168096 ;
> alter system dump datafile 16 block 4168096 ;
System altered.
Block header dump: 0x043f99a0
Object id on Block? Y
seg/obj: 0x123e7 csc: 0x02.94569f8d itc: 169 flg: E typ: 2 - INDEX
brn: 0 bdba: 0x43f968c ver: 0x01 opc: 0
inc: 0 exflg: 0
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x000e.014.00045c5d 0x00809e90.5081.01 CB-- 0 scn 0x0002.94576d86
0x02 0x0032.01a.00022ec7 0x0080a5ca.283f.0b --U- 2 fsc 0x0000.94576d87
0x03 0x001e.022.00048b55 0x00809054.4da5.39 --U- 3 fsc 0x0000.94577542
0x04 0x003c.02c.0001476b 0x0080b0b3.177a.2d --U- 1 fsc 0x0000.94579789
0x05 0x004e.002.0000870e 0x0080a4eb.0be0.4a --U- 2 fsc 0x0000.9457c44a
0x06 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
0x07 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
0x08 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
0x09 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
0x0a 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
0x0b 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
...
0xa3 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
0xa4 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
0xa5 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
0xa6 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
0xa7 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
0xa8 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
0xa9 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
--昏,ITL槽占用了0xa9=169个.
--每个占用24字节. 24*169=4056,这样一半的空间用来保存ITL.
--这样导致磁盘空间的大量浪费,为什么会出现这种情况呢?而且一旦出现这种情况,分裂的索引块会继承这个特性,保留这么多ITL槽,导致
--磁盘空间的大量浪费.
> SELECT max(sbxh) FROM XXXXXX_HIS.ms_yj02;
MAX(SBXH)
----------
9272551
> SELECT * FROM XXXXXX_HIS.gy_identity_ms WHERE bmc = 'MS_YJ02';
BMC DQZ CSZ DZZ
-------- ---------- ---------- ----------
MS_YJ02 9272551 1 1
--看看这大家也许能猜开发并没有使用sequence号,而是通过一个表不断取值来更新ms_yj02的顺序号,理论讲最多出现阻塞的情况.
--难道存在大量的事务作用在一个块里面,或者是阻塞而导致这种情况.但是我看最近的几个块ITL槽使用的仅仅5个.像上面的块--
--仅仅使用了5个.估计可能某个时候系统运行缓慢,导致大量的dml事务出现,我跟踪了一下插入的过程,发现当时插入过程执行的sql
--语句当时没有很好的优化,插入过程并不是很快,加上有大量DML操作,而且使用的顺序号,导致占用大量的ITL槽位.
--先不管这些,把索引重建看看把.
> ALTER INDEX XXXXXX_HIS.PK_MS_YJ02 REBUILD NOCOMPRESS online NOPARALLEL TABLESPACE XXXXXX_HIS pctfree 0 ;
--留待观察把!
> select * from dba_segments where (segment_name='PK_MS_YJ02' or segment_name='MS_YJ02' ) and owner='XXXXXX_HIS';
OWNER SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME HEADER_FILE HEADER_BLOCK BYTES BLOCKS EXTENTS INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS RELATIVE_FNO BUFFER_
---------- ----------------- ------------- ---------------- ----------- ------------ ---------- ---------- ---------- -------------- ----------- ----------- ----------- ------------ -------
XXXXXX_HIS PK_MS_YJ02 INDEX XXXXXX_HIS 18 476867 134217728 16384 87 131072 1 2147483645 18 DEFAULT
XXXXXX_HIS MS_YJ02 TABLE XXXXXX_HIS 7 16083 464519168 56704 129 196608 1 2147483645 7 DEFAULT
--对比可以发现索引现在占用16384块,减少了许多.剩下的问题还有待观察,如果依旧存在问题,难道要使用翻转索引,视乎使用它不是很好.......
--关于这些问题,可以参考一下链接:
https://jonathanlewis.wordpress.com/2009/07/23/index-quiz-2/
https://jonathanlewis.wordpress.com/2009/07/24/iq2-answers/