ITPub博客

首页 > Linux操作系统 > Linux操作系统 > [20131017]索引变大的问题.txt

[20131017]索引变大的问题.txt

原创 Linux操作系统 作者:lfree 时间:2013-10-17 10:49:56 0 删除 编辑
[20131017]索引变大的问题.txt

昨天看别人的服务器,对方提示有一个表的索引很大,提议是否可以shrink减少大小,我仔细看发现这个表发现索引是一个sequence,也就
是线性增加的,但是看块里面的信息确实很空。突然想起Jonathan Lewis博客提到的情况,在并发大量插入的情况下,索引的ITL会占用
很多在分裂后依旧保持原有的数量,这样导致索引许多空间利用不上。

自己在做一些测试看看是否能模拟这个问题。

1.测试环境:
SCOTT@test> @ver
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production


SCOTT@test> create table t (id number,cr_date date,pad varchar2(20));
Table created.

SCOTT@test> create unique index i_t_id on t(id);
Index created.

create sequence scott.seq1 start with 1 increment by 1 minvalue 1 maxvalue 999999999999999999999999 cache 2000 nocycle order ;


2.建立文件ins.sql包含如下内容:
insert into t values (seq1.nextval,sysdate,'test');exec  dbms_lock.sleep(0.03);commit ;

复制1999行,在使用vim编辑如下:(^M => ctrl+v ctrl+m)
:%s/;/;^M/g
--最后加入quit。

建立一个shell脚本执行如下:
#! /bin/bash
for i in $(seq 20); do
 sqlplus scott/xxxx @ins.sql &
done


3.测试:
打开新的回话执行:
SQL> exec dbms_workload_repository.create_snapshot();
SYS@test> select * from v$sysstat where name like '%splits%';
STATISTIC# NAME                                CLASS         VALUE    STAT_ID
---------- ------------------------------ ---------- ------------- ----------
       412 leaf node splits                      128          8106 1417124053
       413 leaf node 90-10 splits                128          2627 1285138251
       414 branch node splits                    128           208  399027615
       415 root node splits                      128             8  708722985
       430 queue splits                          128             0 1168925905

--执行脚本后,等待全部结束:
$  . ins.sh

SQL> exec dbms_workload_repository.create_snapshot();

SYS@test> select * from v$sysstat where name like '%splits%';
STATISTIC# NAME                                CLASS         VALUE    STAT_ID
---------- ------------------------------ ---------- ------------- ----------
       412 leaf node splits                      128          8344 1417124053
       413 leaf node 90-10 splits                128          2766 1285138251
       414 branch node splits                    128           208  399027615
       415 root node splits                      128             8  708722985
       430 queue splits                          128             0 1168925905

--8344-8106=238
--2766-2627=139
--可以发现即使是插入线性增加的记录,索引分裂并没有90-10分裂。依旧存在一部分50-50分裂。

4.做一个转储看看:

SCOTT@test> select object_name,object_id,data_object_id from dba_objects where object_name='I_T_ID' and wner=user;
OBJECT_NAME           OBJECT_ID DATA_OBJECT_ID
-------------------- ---------- --------------
I_T_ID                   275000         275000

SYS@test> alter session set events 'immediate trace name treedump level 275000';
Session altered.

----- begin tree dump
branch: 0x1000213 16777747 (0: nrow: 194, level: 1)
   leaf: 0x1000215 16777749 (-1: nrow: 281 rrow: 281)
   leaf: 0x1000214 16777748 (0: nrow: 266 rrow: 266)
   leaf: 0x1000216 16777750 (1: nrow: 274 rrow: 274)
   leaf: 0x100022d 16777773 (2: nrow: 267 rrow: 267)
   leaf: 0x1000217 16777751 (3: nrow: 274 rrow: 274)
   leaf: 0x100022e 16777774 (4: nrow: 539 rrow: 539)
   leaf: 0x100022a 16777770 (5: nrow: 523 rrow: 523)
   leaf: 0x100022b 16777771 (6: nrow: 267 rrow: 267)
   leaf: 0x1000229 16777769 (7: nrow: 258 rrow: 258)
   leaf: 0x100022f 16777775 (8: nrow: 267 rrow: 267)
   leaf: 0x1000228 16777768 (9: nrow: 259 rrow: 259)
...
   leaf: 0x1000372 16778098 (180: nrow: 130 rrow: 130)
   leaf: 0x1000345 16778053 (181: nrow: 138 rrow: 138)
   leaf: 0x1000337 16778039 (182: nrow: 129 rrow: 129)
   leaf: 0x1000349 16778057 (183: nrow: 266 rrow: 266)
   leaf: 0x100034d 16778061 (184: nrow: 266 rrow: 266)
   leaf: 0x100033b 16778043 (185: nrow: 266 rrow: 266)
   leaf: 0x1000368 16778088 (186: nrow: 266 rrow: 266)
   leaf: 0x1000351 16778065 (187: nrow: 266 rrow: 266)
   leaf: 0x100037a 16778106 (188: nrow: 138 rrow: 138)
   leaf: 0x1000355 16778069 (189: nrow: 138 rrow: 138)
   leaf: 0x1000319 16778009 (190: nrow: 131 rrow: 131)
   leaf: 0x100033f 16778047 (191: nrow: 266 rrow: 266)
   leaf: 0x1000359 16778073 (192: nrow: 142 rrow: 142)
----- end tree dump

可以发现后面一些块保存的键值很少。


SYS@test> @dfb 1000372
old   2: dbms_utility.data_block_address_file(to_number('&1','xxxxxxxxxxxxxxxx')) rfile#,
new   2: dbms_utility.data_block_address_file(to_number('1000372','xxxxxxxxxxxxxxxx')) rfile#,
old   3: dbms_utility.data_block_address_block(to_number('&&1','xxxxxxxxxxxxxxxx')) block#
new   3: dbms_utility.data_block_address_block(to_number('1000372','xxxxxxxxxxxxxxxx')) block#
    RFILE#     BLOCK#
---------- ----------
         4        882

old   1: select 'alter system dump datafile '||dbms_utility.data_block_address_file(to_number('&1','xxxxxxxxxxxxxxxx'))||' block '||
new   1: select 'alter system dump datafile '||dbms_utility.data_block_address_file(to_number('1000372','xxxxxxxxxxxxxxxx'))||' block '||
old   2: dbms_utility.data_block_address_block(to_number('&&1','xxxxxxxxxxxxxxxx')) ||' ;' text
new   2: dbms_utility.data_block_address_block(to_number('1000372','xxxxxxxxxxxxxxxx')) ||' ;' text
TEXT
--------------------------------------------------------------------------------------------------------------------
alter system dump datafile 4 block 882 ;

SQL> alter system dump datafile 4 block 882 ;

Block header dump:  0x01000372
 Object id on Block? Y
 seg/obj: 0x43238  csc: 0x00.c16bf3b9  itc: 169  flg: E  typ: 2 - INDEX
     brn: 0  bdba: 0x1000301 ver: 0x01 opc: 0
     inc: 0  exflg: 0

 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0x0014.002.000001f9  0x00c03989.0179.01  CB--    0  scn 0x0000.c16bf3b7
0x02   0x0003.019.0000337f  0x00c0315f.16c9.38  --U-    1  fsc 0x0000.c16bf3c9
0x03   0x0002.003.000032b5  0x00c03789.17af.23  --U-    1  fsc 0x0000.c16bf3ce
0x04   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
0x05   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
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
0x0c   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
0x0d   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
0x0e   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
0x0f   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
0x10   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
0x11   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
0x12   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
0x13   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
0x14   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
0x15   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
0x16   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
0x17   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
0x18   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
0x19   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
0x1a   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
0x1b   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
0x1c   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
0x1d   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
0x1e   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
0x1f   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
....
0x96   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
0x97   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
0x98   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
0x99   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
0x9a   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
0x9b   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
0x9c   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
0x9d   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
0x9e   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
0x9f   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
0xa0   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
0xa1   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
0xa2   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

SYS@test> @16to10 a9
16 to 10 DEC
------------
         169


--通过bbed观察也能说明问题:
BBED> set dba 4,882
        DBA             0x01000372 (16778098 4,882)

BBED> map /v
 File: /u01/app/oracle11g/oradata/test/users01.dbf (4)
 Block: 882                                   Dba:0x01000372
------------------------------------------------------------
 KTB Data Block (Index Leaf)

 struct kcbh, 20 bytes                      @0
    ub1 type_kcbh                           @0
    ub1 frmt_kcbh                           @1
    ub1 spare1_kcbh                         @2
    ub1 spare2_kcbh                         @3
    ub4 rdba_kcbh                           @4
    ub4 bas_kcbh                            @8
    ub2 wrp_kcbh                            @12
    ub1 seq_kcbh                            @14
    ub1 flg_kcbh                            @15
    ub2 chkval_kcbh                         @16
    ub2 spare3_kcbh                         @18

 struct ktbbh, 4080 bytes                   @20
    ub1 ktbbhtyp                            @20
    union ktbbhsid, 4 bytes                 @24
    struct ktbbhcsc, 8 bytes                @28
    sb2 ktbbhict                            @36
    ub1 ktbbhflg                            @38
    ub1 ktbbhfsl                            @39
    ub4 ktbbhfnx                            @40
    struct ktbbhitl[169], 4056 bytes        @44

 struct kdxle, 32 bytes                     @4108
    struct kdxlexco, 16 bytes               @4108
    sb2 kdxlespl                            @4124
    sb2 kdxlende                            @4126
    ub4 kdxlenxt                            @4128
    ub4 kdxleprv                            @4132
    ub1 kdxledsz                            @4136
    ub1 kdxleflg                            @4137

 sb2 kd_off[130]                            @4140

 ub1 freespace[2039]                        @4400

 ub1 rowdata[1689]                          @6439

 ub4 tailchk                                @8188

--  struct ktbbhitl[169], 4056 bytes        @44
--  说明索引itl槽非常多。每个占用24字节 24*169=4056,占一半空间。




来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/267265/viewspace-774519/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
熟悉oracle相关技术,擅长sql优化,rman备份与恢复,熟悉linux shell编程。

注册时间:2008-01-03

  • 博文量
    2455
  • 访问量
    6258499