这里并不介绍rebuild index online和非online的区别，有需要了解请参考其它文档，这是我们实际生产环境出现的一次故障，上线之前我们测试环境直接rebuild，而生产环境我们使用了rebuild online，而触发这次故障的原因还有一个条件是我们的表的统计信息是锁定的，所以如果您有类似的操作（rebuild online一个统计信息锁定表的local索引），千万小心索引的统计信息会丢失，而我们之所以锁定表统计信息因为我们不希望改变这个表的统计信息。
下面是我们故障后分析问题的实验：
step1:创建测试分区表和索引：
CREATE TABLE cuichenglei001.test_partition_tab1
( "ASSIGNED_TASK_ID" NUMBER(20,0) CONSTRAINT "NN_ASSIGNEDTASK_TASKID" NOT NULL DISABLE,
"UM_ID" VARCHAR2(100),
"ASSIGNED_ORG_ID" VARCHAR2(10),
"TASK_STATUS_CODE" VARCHAR2(2) CONSTRAINT "NN_ASSIGNEDTASK_TASKSC" NOT NULL DISABLE,
"TASK_TYPE_CODE" VARCHAR2(2) CONSTRAINT "NN_ASSIGNEDTASK_TASKTPC" NOT NULL DISABLE,
"TASK_PRIORITY_CODE" VARCHAR2(2) CONSTRAINT "NN_ASSIGNEDTASK_PRIOC" NOT NULL DISABLE,
"DELIVERY_STATUS_CODE" VARCHAR2(2) CONSTRAINT "NN_ASSIGNEDTASK_DELIVERSC" NOT NULL DISABLE,
"TASK_REASSIGN_STATUS_CODE" VARCHAR2(2),
"TASK_APPOINT_TIME" DATE CONSTRAINT "NN_ASSIGNEDTASK_APPOINT" NOT NULL DISABLE,
"TASK_APPOINT_COMMENTS" VARCHAR2(500),
"BATCH_ID" VARCHAR2(20) CONSTRAINT "NN_ASSIGNEDTASK_BATCH" NOT NULL DISABLE,
"TASK_ASSIGNED_TIME" DATE,
"TASK_FIRST_DIAL_TIME" DATE,
"LAST_CONTACT_TIME" DATE,
"TELESALE_PRIRESULT_CODE" VARCHAR2(2),
"LATEST_TELRESULT_CONTACTHIS_ID" VARCHAR2(20),
"IS_EMPHASIS_REMIND" VARCHAR2(1) CONSTRAINT "NN_ASSIGNEDTASK_ISEMPH" NOT NULL DISABLE,
"LATEST_TASK_CONTACT_HISTORY_ID" VARCHAR2(20),
"LATEST_TASK_SYSTEM_HISTORY_ID" VARCHAR2(20),
"RECLAIM_STATUS" VARCHAR2(2) CONSTRAINT "NN_ASSIGNEDTASK_RECSTAT" NOT NULL DISABLE,
"RECLAIM_PRIORITY_CODE" VARCHAR2(2) CONSTRAINT "NN_ASSIGNEDTASK_RECPRI" NOT NULL DISABLE,
"IS_CONTACTED" VARCHAR2(1) CONSTRAINT "NN_ASSIGNEDTASK_ISCONTACT" NOT NULL DISABLE,
"IS_FOLLOWED_UP" VARCHAR2(1),
"STATUS_CHANGED_TIME" DATE,
"ONLINE_SUBMITTED_BY" VARCHAR2(100),
"ONLINE_SUBMITTED_DATE" DATE,
"RECLAIMED_TIME" DATE,
"CREATED_BY" VARCHAR2(100) CONSTRAINT "NN_ASSIGNEDTASK_CREATEDBY" NOT NULL DISABLE,
"CREATED_DATE" DATE CONSTRAINT "NN_ASSIGNEDTASK_CREATEDT" NOT NULL DISABLE,
"UPDATED_BY" VARCHAR2(100) CONSTRAINT "NN_ASSIGNEDTASK_UPDATEBY" NOT NULL DISABLE,
"UPDATED_DATE" DATE CONSTRAINT "NN_ASSIGNEDTASK_UPDATEDT" NOT NULL DISABLE,
"SHIELD_FLAG" VARCHAR2(1),
"TASK_FIRST_DIALED_BY" VARCHAR2(100),
CONSTRAINT "PK_ASSIGNED_TASK_ID" PRIMARY KEY ("ASSIGNED_TASK_ID") USING INDEX TABLESPACE "PAUSERS" ENABLE)
TABLESPACE "TMRLIFEDATA"
PARTITION BY LIST ("RECLAIM_STATUS")
(PARTITION "PT_ASSITASK_RECSTAT_ACTIVE" VALUES ('01')
PCTFREE 20 PCTUSED 40 INITRANS 1 MAXTRANS 255
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "PAUSERS" NOCOMPRESS ,
PARTITION "PT_ASSITASK_RECSTAT_RECLAIM" VALUES (DEFAULT)
PCTFREE 20 PCTUSED 40 INITRANS 1 MAXTRANS 255
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "PAUSERS" NOCOMPRESS ) ENABLE ROW MOVEMENT;
CREATE INDEX cuichenglei001."IX_ASSIGNED_TASK_BATCHID" ON cuichenglei001.test_partition_tab1("BATCH_ID")
PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE(
BUFFER_POOL DEFAULT) LOCAL
(PARTITION "PT_ASSITASK_RECSTAT_ACTIVE"
PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "PAUSERS" ,
PARTITION "PT_ASSITASK_RECSTAT_RECLAIM"
PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "PAUSERS" );
SQL> select owner,index_name,table_name, num_rows,LAST_ANALYZED,PARTITIONED,status,global_stats from dba_indexes where index_name='IX_ASSIGNED_TASK_BATCHID' ;
OWNER INDEX_NAME TABLE_NAME NUM_ROWS LAST_ANALYZED PARTITIONED STATUS GLOBAL_STATS
--------------- ------------------------------ ------------------------------ ---------- ------------- ----------- -------- ------------
CUICHENGLEI001 IX_ASSIGNED_TASK_BATCHID TEST_PARTITION_TAB1 YES N/A NO
TMRLIFEDATA IX_ASSIGNED_TASK_BATCHID NTL_ASSIGNED_TASK 63282319 2011-2-24 0:2 YES N/A YES
SQL> select index_owner,index_name,PARTITION_NAME,STATUS,NUM_ROWS,LAST_ANALYZED,global_stats from dba_ind_partitions where index_name='IX_ASSIGNED_TASK_BATCHID';
INDEX_OWNER INDEX_NAME PARTITION_NAME STATUS NUM_ROWS LAST_ANALYZED GLOBAL_STATS
------------------------------ ------------------------------ ------------------------------ -------- ---------- ------------- ------------
CUICHENGLEI001 IX_ASSIGNED_TASK_BATCHID PT_ASSITASK_RECSTAT_ACTIVE USABLE NO
CUICHENGLEI001 IX_ASSIGNED_TASK_BATCHID PT_ASSITASK_RECSTAT_RECLAIM USABLE NO
TMRLIFEDATA IX_ASSIGNED_TASK_BATCHID PT_ASSITASK_RECSTAT_ACTIVE USABLE YES
TMRLIFEDATA IX_ASSIGNED_TASK_BATCHID PT_ASSITASK_RECSTAT_RECLAIM USABLE 46658539 2011-2-24 0:2 YES
exec dbms_stats.gather_table_stats(ownname => 'CUICHENGLEI001',tabname => 'TEST_PARTITION_TAB1',cascade => TRUE,force => true)
check again:
SQL> select owner,index_name,table_name, num_rows,LAST_ANALYZED,PARTITIONED,status,global_stats from dba_indexes where index_name='IX_ASSIGNED_TASK_BATCHID' ;
OWNER INDEX_NAME TABLE_NAME NUM_ROWS LAST_ANALYZED PARTITIONED STATUS GLOBAL_STATS
--------------- ------------------------------ ------------------------------ ---------- ------------- ----------- -------- ------------
CUICHENGLEI001 IX_ASSIGNED_TASK_BATCHID TEST_PARTITION_TAB1 0 2011-3-3 15:4 YES N/A YES
TMRLIFEDATA IX_ASSIGNED_TASK_BATCHID NTL_ASSIGNED_TASK 63282319 2011-2-24 0:2 YES N/A YES
SQL>
SQL> select index_owner,index_name,PARTITION_NAME,STATUS,NUM_ROWS,LAST_ANALYZED,global_stats from dba_ind_partitions where index_name='IX_ASSIGNED_TASK_BATCHID';
INDEX_OWNER INDEX_NAME PARTITION_NAME STATUS NUM_ROWS LAST_ANALYZED GLOBAL_STATS
------------------------------ ------------------------------ ------------------------------ -------- ---------- ------------- ------------
CUICHENGLEI001 IX_ASSIGNED_TASK_BATCHID PT_ASSITASK_RECSTAT_ACTIVE USABLE 0 2011-3-3 15:4 YES
CUICHENGLEI001 IX_ASSIGNED_TASK_BATCHID PT_ASSITASK_RECSTAT_RECLAIM USABLE 0 2011-3-3 15:4 YES
TMRLIFEDATA IX_ASSIGNED_TASK_BATCHID PT_ASSITASK_RECSTAT_ACTIVE USABLE YES
TMRLIFEDATA IX_ASSIGNED_TASK_BATCHID PT_ASSITASK_RECSTAT_RECLAIM USABLE 46658539 2011-2-24 0:2 YES
收集完统计信息，global_stats became to yes from no
step2:下面测试表明在table和index在统计信息未被locked情况下，rebuild 和rebuild online的结果相同，都自动重新收集了index partition的统计信息：
SQL> exec dbms_stats.gather_table_stats(ownname => 'CUICHENGLEI001',tabname => 'TEST_PARTITION_TAB1',cascade => TRUE)
SQL> alter index IX_ASSIGNED_TASK_BATCHID rebuild partition PT_ASSITASK_RECSTAT_ACTIVE;
Index altered
SQL> alter index IX_ASSIGNED_TASK_BATCHID rebuild partition PT_ASSITASK_RECSTAT_RECLAIM;
Index altered
SQL> select index_owner,index_name,PARTITION_NAME,STATUS,NUM_ROWS,LAST_ANALYZED,global_stats from dba_ind_partitions where index_name='IX_ASSIGNED_TASK_BATCHID';
INDEX_OWNER INDEX_NAME PARTITION_NAME STATUS NUM_ROWS LAST_ANALYZED GLOBAL_STATS
------------------------------ ------------------------------ ------------------------------ -------- ---------- ------------------------------ ------------
CUICHENGLEI001 IX_ASSIGNED_TASK_BATCHID PT_ASSITASK_RECSTAT_ACTIVE USABLE 99 2011-3-3 16:06:28 YES
CUICHENGLEI001 IX_ASSIGNED_TASK_BATCHID PT_ASSITASK_RECSTAT_RECLAIM USABLE 0 2011-3-3 16:06:38 YES
TMRLIFEDATA IX_ASSIGNED_TASK_BATCHID PT_ASSITASK_RECSTAT_ACTIVE USABLE YES
TMRLIFEDATA IX_ASSIGNED_TASK_BATCHID PT_ASSITASK_RECSTAT_RECLAIM USABLE 46658539 2011-2-24 0:27:01 YES
SQL> alter index IX_ASSIGNED_TASK_BATCHID rebuild partition PT_ASSITASK_RECSTAT_ACTIVE online;
Index altered
SQL> alter index IX_ASSIGNED_TASK_BATCHID rebuild partition PT_ASSITASK_RECSTAT_RECLAIM online;
Index altered
SQL> select index_owner,index_name,PARTITION_NAME,STATUS,NUM_ROWS,LAST_ANALYZED,global_stats from dba_ind_partitions where index_name='IX_ASSIGNED_TASK_BATCHID';
INDEX_OWNER INDEX_NAME PARTITION_NAME STATUS NUM_ROWS LAST_ANALYZED GLOBAL_STATS
------------------------------ ------------------------------ ------------------------------ -------- ---------- ------------------------------ ------------
CUICHENGLEI001 IX_ASSIGNED_TASK_BATCHID PT_ASSITASK_RECSTAT_ACTIVE USABLE 99 2011-3-3 16:09:06 YES
CUICHENGLEI001 IX_ASSIGNED_TASK_BATCHID PT_ASSITASK_RECSTAT_RECLAIM USABLE 0 2011-3-3 16:09:14 YES
TMRLIFEDATA IX_ASSIGNED_TASK_BATCHID PT_ASSITASK_RECSTAT_ACTIVE USABLE YES
TMRLIFEDATA IX_ASSIGNED_TASK_BATCHID PT_ASSITASK_RECSTAT_RECLAIM USABLE 46658539 2011-2-24 0:27:01 YES
lock table and index stats:
SQL> exec dbms_stats.lock_table_stats('CUICHENGLEI001','TEST_PARTITION_TAB1');
PL/SQL procedure successfully completed
SQL> select owner,table_name,index_name,PARTITION_NAME,NUM_ROWS,LAST_ANALYZED,GLOBAL_STATS,STATTYPE_LOCKED from dba_ind_statistics where index_name='IX_ASSIGNED_TASK_BATCHID' ORDER BY owner;
OWNER TABLE_NAME INDEX_NAME PARTITION_NAME NUM_ROWS LAST_ANALYZED GLOBAL_STATS STATTYPE_LOCKED
--------------- ------------------------------ ------------------------------ ------------------------------ ---------- ------------------------------ ------------ ---------------
CUICHENGLEI001 TEST_PARTITION_TAB1 IX_ASSIGNED_TASK_BATCHID 99 2011-3-3 16:16:03 YES ALL
CUICHENGLEI001 TEST_PARTITION_TAB1 IX_ASSIGNED_TASK_BATCHID PT_ASSITASK_RECSTAT_ACTIVE 99 2011-3-3 16:16:03 YES ALL
CUICHENGLEI001 TEST_PARTITION_TAB1 IX_ASSIGNED_TASK_BATCHID PT_ASSITASK_RECSTAT_RECLAIM 0 2011-3-3 16:16:03 YES ALL
TMRLIFEDATA NTL_ASSIGNED_TASK IX_ASSIGNED_TASK_BATCHID PT_ASSITASK_RECSTAT_ACTIVE YES ALL
TMRLIFEDATA NTL_ASSIGNED_TASK IX_ASSIGNED_TASK_BATCHID PT_ASSITASK_RECSTAT_RECLAIM 46658539 2011-2-24 0:27:01 YES ALL
TMRLIFEDATA NTL_ASSIGNED_TASK IX_ASSIGNED_TASK_BATCHID 63282319 2011-2-24 0:27:01 YES ALL
6 rows selected
SQL> exec dbms_stats.gather_table_stats(ownname => 'CUICHENGLEI001',tabname => 'TEST_PARTITION_TAB1',cascade => TRUE,force => true)
PL/SQL procedure successfully completed
step3:下面测试表明在table和index在统计信息被locked情况下，rebuild 和rebuild online的结果不同，后者把index partition的统计信息清空了：
SQL> exec dbms_stats.gather_table_stats(ownname => 'CUICHENGLEI001',tabname => 'TEST_PARTITION_TAB1',cascade => TRUE)
begin dbms_stats.gather_table_stats(ownname => 'CUICHENGLEI001',tabname => 'TEST_PARTITION_TAB1',cascade => TRUE); end;
ORA-20005: object statistics are locked (stattype = ALL)
ORA-06512: at "SYS.DBMS_STATS", line 13437
ORA-06512: at "SYS.DBMS_STATS", line 13457
ORA-06512: at line 1
SQL>
SQL> alter index IX_ASSIGNED_TASK_BATCHID rebuild partition PT_ASSITASK_RECSTAT_ACTIVE;
Index altered
SQL> alter index IX_ASSIGNED_TASK_BATCHID rebuild partition PT_ASSITASK_RECSTAT_RECLAIM;
Index altered
check:
SQL> select index_owner,index_name,PARTITION_NAME,STATUS,NUM_ROWS,LAST_ANALYZED,global_stats from dba_ind_partitions where index_name='IX_ASSIGNED_TASK_BATCHID';
INDEX_OWNER INDEX_NAME PARTITION_NAME STATUS NUM_ROWS LAST_ANALYZED GLOBAL_STATS
------------------------------ ------------------------------ ------------------------------ -------- ---------- ------------------------------ ------------
CUICHENGLEI001 IX_ASSIGNED_TASK_BATCHID PT_ASSITASK_RECSTAT_ACTIVE USABLE 99 2011-3-3 16:24:30 YES
CUICHENGLEI001 IX_ASSIGNED_TASK_BATCHID PT_ASSITASK_RECSTAT_RECLAIM USABLE 0 2011-3-3 16:24:30 YES
TMRLIFEDATA IX_ASSIGNED_TASK_BATCHID PT_ASSITASK_RECSTAT_ACTIVE USABLE YES
TMRLIFEDATA IX_ASSIGNED_TASK_BATCHID PT_ASSITASK_RECSTAT_RECLAIM USABLE 46658539 2011-2-24 0:27:01 YES
SQL> select owner,table_name,index_name,PARTITION_NAME,NUM_ROWS,LAST_ANALYZED,GLOBAL_STATS,STATTYPE_LOCKED from dba_ind_statistics where index_name='IX_ASSIGNED_TASK_BATCHID' ORDER BY owner;
OWNER TABLE_NAME INDEX_NAME PARTITION_NAME NUM_ROWS LAST_ANALYZED GLOBAL_STATS STATTYPE_LOCKED
--------------- ------------------------------ ------------------------------ ------------------------------ ---------- ------------------------------ ------------ ---------------
CUICHENGLEI001 TEST_PARTITION_TAB1 IX_ASSIGNED_TASK_BATCHID 99 2011-3-3 16:24:30 YES ALL
CUICHENGLEI001 TEST_PARTITION_TAB1 IX_ASSIGNED_TASK_BATCHID PT_ASSITASK_RECSTAT_ACTIVE 99 2011-3-3 16:24:30 YES ALL
CUICHENGLEI001 TEST_PARTITION_TAB1 IX_ASSIGNED_TASK_BATCHID PT_ASSITASK_RECSTAT_RECLAIM 0 2011-3-3 16:24:30 YES ALL
TMRLIFEDATA NTL_ASSIGNED_TASK IX_ASSIGNED_TASK_BATCHID PT_ASSITASK_RECSTAT_ACTIVE YES ALL
TMRLIFEDATA NTL_ASSIGNED_TASK IX_ASSIGNED_TASK_BATCHID PT_ASSITASK_RECSTAT_RECLAIM 46658539 2011-2-24 0:27:01 YES ALL
TMRLIFEDATA NTL_ASSIGNED_TASK IX_ASSIGNED_TASK_BATCHID 63282319 2011-2-24 0:27:01 YES ALL
6 rows selected
SQL> alter index IX_ASSIGNED_TASK_BATCHID rebuild partition PT_ASSITASK_RECSTAT_ACTIVE online;
Index altered
SQL> alter index IX_ASSIGNED_TASK_BATCHID rebuild partition PT_ASSITASK_RECSTAT_RECLAIM online;
Index altered
SQL> select index_owner,index_name,PARTITION_NAME,STATUS,NUM_ROWS,LAST_ANALYZED,global_stats from dba_ind_partitions where index_name='IX_ASSIGNED_TASK_BATCHID';
INDEX_OWNER INDEX_NAME PARTITION_NAME STATUS NUM_ROWS LAST_ANALYZED GLOBAL_STATS
------------------------------ ------------------------------ ------------------------------ -------- ---------- ------------------------------ ------------
CUICHENGLEI001 IX_ASSIGNED_TASK_BATCHID PT_ASSITASK_RECSTAT_ACTIVE USABLE YES
CUICHENGLEI001 IX_ASSIGNED_TASK_BATCHID PT_ASSITASK_RECSTAT_RECLAIM USABLE YES
TMRLIFEDATA IX_ASSIGNED_TASK_BATCHID PT_ASSITASK_RECSTAT_ACTIVE USABLE YES
TMRLIFEDATA IX_ASSIGNED_TASK_BATCHID PT_ASSITASK_RECSTAT_RECLAIM USABLE 46658539 2011-2-24 0:27:01 YES
SQL>
SQL> select owner,table_name,index_name,PARTITION_NAME,NUM_ROWS,LAST_ANALYZED,GLOBAL_STATS,STATTYPE_LOCKED from dba_ind_statistics where index_name='IX_ASSIGNED_TASK_BATCHID' ORDER BY owner;
OWNER TABLE_NAME INDEX_NAME PARTITION_NAME NUM_ROWS LAST_ANALYZED GLOBAL_STATS STATTYPE_LOCKED
--------------- ------------------------------ ------------------------------ ------------------------------ ---------- ------------------------------ ------------ ---------------
CUICHENGLEI001 TEST_PARTITION_TAB1 IX_ASSIGNED_TASK_BATCHID 99 2011-3-3 16:24:30 YES ALL
CUICHENGLEI001 TEST_PARTITION_TAB1 IX_ASSIGNED_TASK_BATCHID PT_ASSITASK_RECSTAT_ACTIVE YES ALL
CUICHENGLEI001 TEST_PARTITION_TAB1 IX_ASSIGNED_TASK_BATCHID PT_ASSITASK_RECSTAT_RECLAIM YES ALL
TMRLIFEDATA NTL_ASSIGNED_TASK IX_ASSIGNED_TASK_BATCHID PT_ASSITASK_RECSTAT_ACTIVE YES ALL
TMRLIFEDATA NTL_ASSIGNED_TASK IX_ASSIGNED_TASK_BATCHID PT_ASSITASK_RECSTAT_RECLAIM 46658539 2011-2-24 0:27:01 YES ALL
TMRLIFEDATA NTL_ASSIGNED_TASK IX_ASSIGNED_TASK_BATCHID 63282319 2011-2-24 0:27:01 YES ALL
6 rows selected
因此需要在rebuild index(online)操作后增加一个检查确认：相关table/index partition的统计信息状态是否合理。
最后得出如下表格：
| rebuild index | lcok tab stat ? | statitics values | summary |
| rebuild | locked | keep current statitics |
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/15779287/viewspace-690019/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/15779287/viewspace-690019/
925
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
