聊聊索引Index Rebuild和Rebuild Online(下)

原文链接:http://www.cnblogs.com/zoeyqq/p/6525343.html

转载地址:http://blog.itpub.net/17203031/viewspace-1473163/

3、使用10046诊断rebuild动作

 

10046诊断事件是我们经常用来使用跟踪事件,也是我们分析Oracle内部行为的最常用工具。下面笔者将用这个工具对rebuild动作进行检查。

首先获取一下数据表T和索引IDX_T_ID的分区结构。

 

 

--数据表T

SQL> select EXTENT_ID, FILE_ID, BLOCK_ID, BYTES, BLOCKS from dba_extents where owner='SYS' and segment_name='T';

 

 EXTENT_ID    FILE_ID   BLOCK_ID      BYTES     BLOCKS

---------- ---------- ---------- ---------- ----------

         0          1      31072      65536          8 –段首

         1          1      31096      65536          8

         2          1      99328      65536          8

         3          1      99360      65536          8

         4          1      99368      65536          8

         5          1      99376      65536          8

         6          1      99384      65536          8

         7          1      99392      65536          8

         8          1      99400      65536          8

         9          1      99408      65536          8

        10          1      99416      65536          8

        11          1      99424      65536          8

        12          1      99432      65536          8

        13          1      99440      65536          8

        14          1      99448      65536          8

        15          1     100736      65536          8

        16          1      99456    1048576        128

        17          1      99584    1048576        128

        18          1      99712    1048576        128

        19          1      99840    1048576        128

 

 EXTENT_ID    FILE_ID   BLOCK_ID      BYTES     BLOCKS

---------- ---------- ---------- ---------- ----------

        20          1      99968    1048576        128

        21          1     100096    1048576        128

        22          1     100224    1048576        128

        23          1     100352    1048576        128

        24          1     100480    1048576        128

 

25 rows selected

 

--分区情况

 

SQL> select EXTENT_ID, FILE_ID, BLOCK_ID, BYTES, BLOCKS from dba_extents where owner='SYS' and segment_name='IDX_T_ID';

 

 EXTENT_ID    FILE_ID   BLOCK_ID      BYTES     BLOCKS

---------- ---------- ---------- ---------- ----------

         0          1     100760      65536          8 –段首

         1          1     100768      65536          8

         2          1     100776      65536          8

         3          1     100784      65536          8

         4          1     100792      65536          8

         5          1     100800      65536          8

         6          1     100808      65536          8

         7          1     100816      65536          8

         8          1     100824      65536          8

         9          1     100832      65536          8

        10          1     100840      65536          8

        11          1     100848      65536          8

        12          1     100856      65536          8

        13          1     100608      65536          8

        14          1     100616      65536          8

        15          1     100624      65536          8

        16          1     100864    1048576        128

 

17 rows selected

 

 

了解数据表和索引段结构之后,就可以从Trace文件中分析Oracle的读取写入动作。下面执行跟踪过程。

 

 

--清理内存

SQL> alter system flush shared_pool;

System altered

 

SQL> alter system flush buffer_cache;

System altered

 

 

跟踪过程:

 

 

 

SQL> select value from v$diag_info where name='Default Trace File';

VALUE

--------------------------------------------------------------------------------

/u01/app/diag/rdbms/ora11g/ora11g/trace/ora11g_ora_1962.trc

 

 

SQL> alter system flush shared_pool;

System altered

 

SQL> alter system flush buffer_cache;

System altered

 

SQL> alter session set events '10046 trace name context forever, level 12';

会话已更改。

 

SQL> alter index idx_t_id rebuild;

索引已更改。

 

SQL> alter session set events '10046 trace name context off';

会话已更改。

 

 

从Trace文件中,我们可以发现很多的SQL语句和执行过程。一个Oracle SQL语句的执行,往往伴随着很多的recursive call调用过程。详细研究可以帮助我们理解内部运行机理。篇幅有限,本次之研究与alert index … rebuild相关语句和游标记录。

首先找到了rebuild记录游标。

 

 

=====================

PARSING IN CURSOR #3075237312 len=28 dep=0 uid=0 oct=9 lid=0 tim=1427116687152197 hv=411325523 ad='35dc51e8' sqlid='1w5dx14c88p2m'

alter index idx_t_id rebuild

END OF STMT

PARSE #3075237312:c=147978,e=169400,p=26,cr=312,cu=0,mis=1,r=0,dep=0,og=1,plh=1483129259,tim=1427116687152192

 

 

Alter index …. Rebuild语句被解析parse为编号:3075237312。之后Trace文件中包括与这个编号有关的记录如下:

首先记录的是从对象中大量读取数据的过程:

 

 

WAIT #3075237312: nam='db file sequential read' ela= 148 file#=1 block#=100760 blocks=1 obj#=87690 tim=1427116687641730

WAIT #3075237312: nam='db file scattered read' ela= 155 file#=1 block#=100761 blocks=7 obj#=87690 tim=1427116687642009

WAIT #3075237312: nam='db file scattered read' ela= 339 file#=1 block#=100768 blocks=8 obj#=87690 tim=1427116687646571

WAIT #3075237312: nam='db file scattered read' ela= 176 file#=1 block#=100776 blocks=8 obj#=87690 tim=1427116687650926

WAIT #3075237312: nam='db file scattered read' ela= 204 file#=1 block#=100784 blocks=8 obj#=87690 tim=1427116687655912

WAIT #3075237312: nam='db file scattered read' ela= 197 file#=1 block#=100792 blocks=8 obj#=87690 tim=1427116687660075

WAIT #3075237312: nam='db file scattered read' ela= 207 file#=1 block#=100800 blocks=8 obj#=87690 tim=1427116687664669

WAIT #3075237312: nam='db file scattered read' ela= 203 file#=1 block#=100808 blocks=8 obj#=87690 tim=1427116687669300

WAIT #3075237312: nam='db file scattered read' ela= 220 file#=1 block#=100816 blocks=8 obj#=87690 tim=1427116687674227

WAIT #3075237312: nam='db file scattered read' ela= 162 file#=1 block#=100824 blocks=8 obj#=87690 tim=1427116687679009

WAIT #3075237312: nam='db file scattered read' ela= 210 file#=1 block#=100832 blocks=8 obj#=87690 tim=1427116687683670

 

*** 2015-03-23 21:18:07.688

WAIT #3075237312: nam='db file scattered read' ela= 196 file#=1 block#=100840 blocks=8 obj#=87690 tim=1427116687688942

WAIT #3075237312: nam='db file scattered read' ela= 456 file#=1 block#=100848 blocks=8 obj#=87690 tim=1427116687694629

WAIT #3075237312: nam='db file scattered read' ela= 248 file#=1 block#=100856 blocks=8 obj#=87690 tim=1427116687699340

WAIT #3075237312: nam='db file scattered read' ela= 318 file#=1 block#=100608 blocks=8 obj#=87690 tim=1427116687704678

WAIT #3075237312: nam='db file scattered read' ela= 336 file#=1 block#=100616 blocks=8 obj#=87690 tim=1427116687709104

WAIT #3075237312: nam='db file scattered read' ela= 216 file#=1 block#=100624 blocks=8 obj#=87690 tim=1427116687713798

WAIT #3075237312: nam='db file scattered read' ela= 3976 file#=1 block#=100864 blocks=32 obj#=87690 tim=1427116687724032

WAIT #3075237312: nam='db file scattered read' ela= 769 file#=1 block#=100896 blocks=32 obj#=87690 tim=1427116687749159

WAIT #3075237312: nam='db file sequential read' ela= 165 file#=1 block#=100928 blocks=1 obj#=87690 tim=1427116687771987

WAIT #3075237312: nam='Disk file operations I/O' ela= 41 FileOperation=2 fileno=3 filetype=2 obj#=0 tim=1427116687775771

WAIT #3075237312: nam='db file sequential read' ela= 170 file#=3 block#=3044 blocks=1 obj#=0 tim=1427116687776293

WAIT #3075237312: nam='db file sequential read' ela= 178 file#=1 block#=2 blocks=1 obj#=1 tim=1427116687778458

WAIT #3075237312: nam='db file sequential read' ela= 134 file#=1 block#=3 blocks=1 obj#=1 tim=1427116687778761

 

 

第一句的sequential单块读动作,读取的是file#=1 block#=100760 blocks=1 obj#=87690。参考之前分区结构,恰好是索引IDX_T_ID段的段头块结构。

 

 

SQL> select EXTENT_ID, FILE_ID, BLOCK_ID, BYTES, BLOCKS from dba_extents where owner='SYS' and segment_name='IDX_T_ID';

 

 EXTENT_ID    FILE_ID   BLOCK_ID      BYTES     BLOCKS

---------- ---------- ---------- ---------- ----------

         0          1     100760      65536          8

……

 

 

之后一系列的“db file scattered read”多块读动作,持续的“吞”掉索引的结构块。这些特征完全符合index fast full scan工作特点。

之后,就将处理数据写入结构:

 

 

WAIT #3075237312: nam='direct path write' ela= 17189 file number=1 first dba=100636 block cnt=4 obj#=0 tim=1427116687900949

WAIT #3075237312: nam='direct path write' ela= 3769 file number=1 first dba=100640 block cnt=4 obj#=0 tim=1427116687907864

WAIT #3075237312: nam='direct path write' ela= 26675 file number=1 first dba=100644 block cnt=4 obj#=0 tim=1427116687939138

WAIT #3075237312: nam='direct path write' ela= 2119 file number=1 first dba=100648 block cnt=4 obj#=0 tim=1427116687944101

WAIT #3075237312: nam='direct path write' ela= 5425 file number=1 first dba=100652 block cnt=4 obj#=0 tim=1427116687953153

(篇幅原因,有省略……)

WAIT #3075237312: nam='direct path write' ela= 6109 file number=1 first dba=100692 block cnt=4 obj#=0 tim=1427116688030198

 

 

这部分动作是rebuild的后续动作,写入数据内容就是新索引IDX_T_ID段结构。这点从执行后新段结构信息可以证明。

 

 

SQL> select EXTENT_ID, FILE_ID, BLOCK_ID, BYTES, BLOCKS from dba_extents where owner='SYS' and segment_name='IDX_T_ID';

 

 EXTENT_ID    FILE_ID   BLOCK_ID      BYTES     BLOCKS

---------- ---------- ---------- ---------- ----------

         0          1     100632      65536          8

         1          1     100640      65536          8

         2          1     100648      65536          8

         3          1     100656      65536          8

         4          1     100664      65536          8

         5          1     100672      65536          8

         6          1     100680      65536          8

         7          1     100688      65536          8

         8          1     100696      65536          8

         9          1     100704      65536          8

        10          1     100712      65536          8

        11          1     100720      65536          8

        12          1     100728      65536          8

        13          1     100992      65536          8

        14          1     101000      65536          8

        15          1     101008      65536          8

        16          1     101120    1048576        128

 

17 rows selected

 

 

这系列也就证明了rebuild操作是基于原有索引结构数据,重新构建出索引段结构。

 

4、使用10046诊断rebuild online动作

 

下面测试一下rebuild online动作。为了保证实验质量,清理一下内存缓存。

 

 

SQL> alter system flush shared_pool;

System altered

 

SQL> alter system flush buffer_cache;

System altered

 

 

开启跟踪事件。

 

 

SQL> select value from v$diag_info where name='Default Trace File';

VALUE

-------------------------------------------------------------------------

/u01/app/diag/rdbms/ora11g/ora11g/trace/ora11g_ora_4149.trc

 

SQL> alter session set events '10046 trace name context forever, level 12';

会话已更改。

 

SQL> alter index idx_t_id rebuild online;

索引已更改。

 

SQL> alter session set events '10046 trace name context off';

会话已更改。

 

 

在跟踪文件中,找到对应的rebuild online游标信息。

 

 

=====================

PARSING IN CURSOR #3075319188 len=35 dep=0 uid=0 oct=9 lid=0 tim=1427116924266395 hv=572453287 ad='35d443ac' sqlid='6bvhyk0j1xwd7'

alter index idx_t_id rebuild online

END OF STMT

PARSE #3075319188:c=92986,e=109127,p=30,cr=236,cu=0,mis=1,r=0,dep=0,og=1,plh=1193657316,tim=1427116924266390

 

 

对rebuild online语句,游标编号:#3075319188。在文件中找到对应游标的记录。

 

 

WAIT #3075319188: nam='db file sequential read' ela= 199 file#=3 block#=192 blocks=1 obj#=0 tim=1427116926331398

WAIT #3075319188: nam='db file sequential read' ela= 146 file#=3 block#=6548 blocks=1 obj#=0 tim=1427116926331706

WAIT #3075319188: nam='db file sequential read' ela= 147 file#=1 block#=31072 blocks=1 obj#=87689 tim=1427116926332503

WAIT #3075319188: nam='db file sequential read' ela= 166 file#=1 block#=31072 blocks=1 obj#=87689 tim=1427116926332741

WAIT #3075319188: nam='db file scattered read' ela= 315 file#=1 block#=31073 blocks=7 obj#=87689 tim=1427116926333268

(篇幅原因,有省略……)

WAIT #3075319188: nam='db file scattered read' ela= 961 file#=1 block#=100511 blocks=32 obj#=87689 tim=1427116926441946

WAIT #3075319188: nam='db file scattered read' ela= 392 file#=1 block#=100543 blocks=15 obj#=87689 tim=1427116926444785

 

 

首先我们看一下比较奇怪的对file#=3的读动作,单块读动作可以找到对应段的情况。

 

 

SQL> select * from dba_extents where FILE_ID=3 and block_id<=192 and block_id+blocks-1>=192;

 

SEGMENT_NAME             SEGMENT_TYPE       TABLESPACE_NAME                 EXTENT_ID    FILE_ID   BLOCK_ID      BYTES     BLOCKS RELATIVE_FNO

-------------------------------------------------------------------------------- ------------------------------ ------------------ -------------

_SYSSMU5_3994777876$     TYPE2 UNDO         UNDOTBS1                                0          3        192      65536          8            3

(结果有删减)

 

SQL> select * from dba_extents where FILE_ID=3 and block_id<=6548 and block_id+blocks-1>=6548;

 

SEGMENT_NAME            SEGMENT_TYPE       TABLESPACE_NAME                 EXTENT_ID    FILE_ID   BLOCK_ID      BYTES     BLOCKS RELATIVE_FNO

------------------------ ------------------ ------------------------------ ---------- ---------- ---------- ---------- ---------- ------------

_SYSSMU5_3994777876$    TYPE2 UNDO         UNDOTBS1                               10          3       6528    1048576        128            3

 

 

这两个部分内容是Undo段中数据。说明在读过程,有访问Undo前镜像的情景。其他的数据内容为数据表段读取:

 

 

SQL> select EXTENT_ID, FILE_ID, BLOCK_ID, BYTES, BLOCKS from dba_extents where owner='SYS' and segment_name='T';

 

 EXTENT_ID    FILE_ID   BLOCK_ID      BYTES     BLOCKS

---------- ---------- ---------- ---------- ----------

         0          1      31072      65536          8

         1          1      31096      65536          8

         2          1      99328      65536          8

 

 

之后是写入动作:

 

 

WAIT #3075319188: nam='direct path write' ela= 104461 file number=1 first dba=100618 block cnt=2 obj#=87689 tim=1427116926569710

WAIT #3075319188: nam='direct path write' ela= 13504 file number=1 first dba=100620 block cnt=4 obj#=87689 tim=1427116926604072

(篇幅原因,有省略……)

WAIT #3075319188: nam='direct path write' ela= 2319 file number=1 first dba=100928 block cnt=1 obj#=87689 tim=1427116928413458

 

 

从Trace文件结果看,rebuild online过程是直接对数据表的访问,将数据读取后进行索引化过程。

 

5、结论

 

索引rebuild是一个我们经常遇到的操作过程,详细理解rebuild和rebuild online可以帮助在实际工作中强化分析能力,更好解决问题。

转载于:https://www.cnblogs.com/zoeyqq/p/6525343.html

上一篇:聊聊索引Index Rebuild和Rebuild Online(上)


下一篇:Unity笔记--Canvas-网格重建