[20140509]关于索引问题.txt

[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/

上一篇:10 个*的 CSS3 + HTML5 的菜单实现


下一篇:[流媒体]实例解析MMS流媒体协议,下载LiveMediaVideo[4]