[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看到的基本一致.
时间： 2024-09-20 12:19:16