[20120801]11g下Oracle Index rebuild online之3.txt

[20120801]11g下Oracle Index rebuild online之3.txt

接着昨天的测试继续:

昨天在索引rebuild online时,按ctrl+c中断时,要等很长时间,不想一些dml语句如果挂起,按ctrl+c很快退出.
今天继续测试看看..

SQL> select * from v$version ;

BANNER

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

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

PL/SQL Release 11.2.0.1.0 - Production

CORE    11.2.0.1.0      Production

TNS for Linux: Version 11.2.0.1.0 - Production

NLSRTL Version 11.2.0.1.0 - Production

create table t (id number,name varchar2(10));

create index i_t_id on t(id);

insert into t  select rownum id ,'test' name from dual connect by level 

commit ;

1.测试,插入记录后在rebuild online：
会话1插入1行：

SQL1> select * from v$mystat where rownum

       SID STATISTIC#      VALUE

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

       133          0          0

insert into t values (101,'a');

会话2插入1行：

SQL2> select * from v$mystat where rownum

       SID STATISTIC#      VALUE

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

       192          0          0

SQL2> set timing on

SQL2> alter index i_t_id rebuild online ;

--挂起!

回到回话1执行:

SQL1> exec scott.print_table('select * from v$session_wait where sid=192');

SID                           : 192

SEQ#                          : 33

EVENT                         : enq: TX - row lock contention

P1TEXT                        : name|mode

P1                            : 1415053316

P1RAW                         : 0000000054580004

P2TEXT                        : usn

P2                            : 589838

P2RAW                         : 000000000009000E

P3TEXT                        : sequence

P3                            : 7179

P3RAW                         : 0000000000001C0B

WAIT_CLASS_ID                 : 4217450380

WAIT_CLASS#                   : 1

WAIT_CLASS                    : Application

WAIT_TIME                     : 0

SECONDS_IN_WAIT               : 3

STATE                         : WAITING

WAIT_TIME_MICRO               : 2684547

TIME_REMAINING_MICRO          : -1

TIME_SINCE_LAST_WAIT_MICRO    : 0

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

PL/SQL procedure successfully completed.

--可以发现出现的等待事件是enq: TX - row lock contention.

2.回到回话2,按ctrl+c中断rebuild online.回到回话1执行:

SQL1> exec scott.print_table('select * from v$session_wait where sid=192');

SID                           : 192

SEQ#                          : 67

EVENT                         : index (re)build online cleanup

P1TEXT                        : object

P1                            : 101584

P1RAW                         : 0000000000018CD0

P2TEXT                        : mode

P2                            : 4

P2RAW                         : 0000000000000004

P3TEXT                        : wait

P3                            : 0

P3RAW                         : 00

WAIT_CLASS_ID                 : 4166625743

WAIT_CLASS#                   : 3

WAIT_CLASS                    : Administrative

WAIT_TIME                     : 0

SECONDS_IN_WAIT               : 0

STATE                         : WAITING

WAIT_TIME_MICRO               : 7931

TIME_REMAINING_MICRO          : 92069

TIME_SINCE_LAST_WAIT_MICRO    : 0

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

PL/SQL procedure successfully completed.

--可以发现等待事件是 index (re)build online cleanup.测试大约3:27.08出现:
--也就是大约3*60+27=207秒后退出.

SQL2> alter index i_t_id rebuild online ;

alter index i_t_id rebuild online

*

ERROR at line 1:

ORA-01013: user requested cancel of current operation

Elapsed: 00:03:27.08

SQL1> exec scott.print_table('select * from v$session_wait where sid=192');

SID                           : 192

SEQ#                          : 2103

EVENT                         : SQL*Net message from client

P1TEXT                        : driver id

P1                            : 1650815232

P1RAW                         : 0000000062657100

P2TEXT                        : #bytes

P2                            : 1

P2RAW                         : 0000000000000001

P3TEXT                        :

P3                            : 0

P3RAW                         : 00

WAIT_CLASS_ID                 : 2723168908

WAIT_CLASS#                   : 6

WAIT_CLASS                    : Idle

WAIT_TIME                     : 0

SECONDS_IN_WAIT               : 65

STATE                         : WAITING

WAIT_TIME_MICRO               : 64925947

TIME_REMAINING_MICRO          : -1

TIME_SINCE_LAST_WAIT_MICRO    : 0

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

PL/SQL procedure successfully completed.

查看一下v$system_event视图:

SQL> exec scott.print_table('select * from v$system_event where event =''index (re)build online cleanup''');

EVENT                         : index (re)build online cleanup

TOTAL_WAITS                   : 43143

TOTAL_TIMEOUTS                : 43143

TIME_WAITED                   : 435750

AVERAGE_WAIT                  : 10.1

TIME_WAITED_MICRO             : 4357497403

TOTAL_WAITS_FG                : 43135

TOTAL_TIMEOUTS_FG             : 43135

TIME_WAITED_FG                : 435669

AVERAGE_WAIT_FG               : 10.1

TIME_WAITED_MICRO_FG          : 4356691014

EVENT_ID                      : 3507172204

WAIT_CLASS_ID                 : 4166625743

WAIT_CLASS#                   : 3

WAIT_CLASS                    : Administrative

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

PL/SQL procedure successfully completed.

--我测试执行多次.TOTAL_WAITS很高.
--看来要看v$session_event可以知道每一次有多少次TOTAL_WAITS,TOTAL_TIMEOUTS. 3.再重复测试看看. 回话1,rollback. 回话2,退出,在进入: SQL2> select * from v$mystat where rownum
       SID STATISTIC#      VALUE
---------- ---------- ----------
       192          0          0

--SID依旧是192.

打开新回话2(sys用户),执行:

DECLARE

  isClean BOOLEAN;

BEGIN

  isClean := FALSE;

    isClean := DBMS_REPAIR.ONLINE_INDEX_CLEAN(101585,0);

  EXCEPTION

   WHEN OTHERS THEN

   RAISE;

END;

/

在回话2执行:
SQL2> alter index i_t_id rebuild online ;

回到回话1执行:

SQL1>  exec print_table(q'[select * from v$session_event where event ='index (re)build online cleanup' and sid=192]');

SID                           : 192

EVENT                         : index (re)build online cleanup

TOTAL_WAITS                   : 23

TOTAL_TIMEOUTS                : 23

TIME_WAITED                   : 232

AVERAGE_WAIT                  : 10.08

MAX_WAIT                      : 10

TIME_WAITED_MICRO             : 2319401

EVENT_ID                      : 3507172204

WAIT_CLASS_ID                 : 4166625743

WAIT_CLASS#                   : 3

WAIT_CLASS                    : Administrative

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

PL/SQL procedure successfully completed.

SQL>  exec print_table(q'[select * from v$session_event where event ='index (re)build online cleanup' and sid=192]');

SID                           : 192

EVENT                         : index (re)build online cleanup

TOTAL_WAITS                   : 298

TOTAL_TIMEOUTS                : 298

TIME_WAITED                   : 3009

AVERAGE_WAIT                  : 10.1

MAX_WAIT                      : 10

TIME_WAITED_MICRO             : 30087538

EVENT_ID                      : 3507172204

WAIT_CLASS_ID                 : 4166625743

WAIT_CLASS#                   : 3

WAIT_CLASS                    : Administrative

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

PL/SQL procedure successfully completed.

--可以发现TOTAL_WAITS次数不断增加.最后如下:

SQL>  exec print_table(q'[select * from v$session_event where event ='index (re)build online cleanup' and sid=192]');

SID                           : 192

EVENT                         : index (re)build online cleanup

TOTAL_WAITS                   : 2000

TOTAL_TIMEOUTS                : 2000

TIME_WAITED                   : 20199

AVERAGE_WAIT                  : 10.1

MAX_WAIT                      : 10

TIME_WAITED_MICRO             : 201988369

EVENT_ID                      : 3507172204

WAIT_CLASS_ID                 : 4166625743

WAIT_CLASS#                   : 3

WAIT_CLASS                    : Administrative

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

PL/SQL procedure successfully completed.

--可以推出,按ctrl+c后,oracle不断尝试index (re)build online cleanup,总共2000次,平均时间是10cs(厘秒).
--TIME_WAITED_MICRO的单位应该是us,201988369/1e6约等于202秒,基本于set timing on看到的基本一致.