尝试oracle表的rebuild操作

生产系统中有个短信发送表，平均每个月会大批量发送一次群体短信（30～90万条记录），日常应用是一些即时短信，每天发送量在1000条～10000条左右。前几天突然发现查询很慢（在PL/SQL Developer工具中查出第一页要1秒左右，select * from tox.sm_send_sm_list，而一般的表只需0.01秒左右）。

经检查，发现表上没有收集统计信息。查看表统计信息的查询语句如下：

SELECT *
FROM All_All_Tables t
WHERE 1 = 1
AND t.owner = 'TOX'
AND t.table_name = 'SM_SEND_SM_LIST'

于是调用ANALYZE TABLE tox.sm_send_sm_list ESTIMATE STATISTICS;收集统计信息，统计了(NUM_ROWS BLOCKS EMPTY_BLOCKS AVG_SPACE CHAIN_CNT AVG_ROW_LEN AVG_SPACE_FREELIST_BLOCKS NUM_FREELIST_BLOCKS)等几个关键的指标。其中BLOCKS=19201, EMPTY_BLOCKS=4251，块大小为8K。现在查询，耗时在0.415秒左右。

第二天，找到DBA，在他的帮助下对表进行了重建。重建后，查询出第一页只需0.015秒，恢复正常。可以看出，对于这种短信发送表，经常大批量插入记录，并逐行删除到空记录，收缩性很强，会导致占用很多的块，以及空闲块。可能需要阶段性的将表重建一下，光靠统计信息收集还不太管用。

附录A：重建表的脚本（在TOAD for Oracle 8.6.0.38中，点击rebuild table按钮，自动生成脚本）
注意：重建时，要充分考虑主键/外键，表中数据，权限，依赖项重边缘，trigger等，所以个人推荐用Toad的脚本自动生成功能。

SET LINESIZE 200
-- 
--  Make backup copy of original table
ALTER TABLE TOX.SM_SEND_SM_LIST RENAME TO SM_SEND_SM_LIST_X ;

-- Drop all user named constraints
-- (none)

--  Recreate original table
CREATE TABLE TOX.SM_SEND_SM_LIST
(
  SERIALNO            NUMBER,
  SERVICEID           VARCHAR2(50 BYTE),
  SMCONTENT           VARCHAR2(1000 BYTE),
  SENDTARGET          VARCHAR2(20 BYTE),
  PRIORITY            NUMBER,
  RCOMPLETETIMEBEGIN  DATE,
  RCOMPLETETIMEEND    DATE,
  RCOMPLETEHOURBEGIN  NUMBER,
  RCOMPLETEHOUREND    NUMBER,
  REQUESTTIME         DATE,
  ROADBY              NUMBER,
  SENDTARGETDESC      VARCHAR2(100 BYTE),
  FEEVALUE            FLOAT(126),
  LINKID              VARCHAR2(100 BYTE),
  PAD1                VARCHAR2(50 BYTE),
  PAD2                VARCHAR2(100 BYTE),
  PAD3                VARCHAR2(200 BYTE),
  PAD4                VARCHAR2(500 BYTE),
  PAD5                VARCHAR2(1000 BYTE)
)
TABLESPACE TS_SMS
PCTUSED    0
PCTFREE    10
INITRANS   1
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            MINEXTENTS       1
            MAXEXTENTS       2147483645
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
LOGGING
NOCOMPRESS
NOCACHE
NOPARALLEL
NOMONITORING;
 
--  Copy the data from the renamed table 
INSERT /*+ APPEND */
INTO TOX.SM_SEND_SM_LIST INS_TBL
(SERIALNO, SERVICEID, SMCONTENT, SENDTARGET,
PRIORITY, RCOMPLETETIMEBEGIN, RCOMPLETETIMEEND, RCOMPLETEHOURBEGIN,
RCOMPLETEHOUREND, REQUESTTIME, ROADBY, SENDTARGETDESC,
FEEVALUE, LINKID, PAD1, PAD2,
PAD3, PAD4, PAD5)
SELECT
SERIALNO, SERVICEID, SMCONTENT, SENDTARGET,
PRIORITY, RCOMPLETETIMEBEGIN, RCOMPLETETIMEEND, RCOMPLETEHOURBEGIN,
RCOMPLETEHOUREND, REQUESTTIME, ROADBY, SENDTARGETDESC,
FEEVALUE, LINKID, PAD1, PAD2,
PAD3, PAD4, PAD5
FROM TOX.SM_SEND_SM_LIST_X SEL_TBL ;
 
Commit ;
 
-- Drop all other user named indexes
-- (none)

--  Recreate Indexes, Constraints, and Grants

ALTER TABLE TOX.SM_SEND_SM_LIST ADD (
  PRIMARY KEY
 (SERIALNO)
    USING INDEX
    TABLESPACE TS_SMS
    PCTFREE    10
    INITRANS   2
    MAXTRANS   255
    STORAGE    (
                INITIAL          64K
                MINEXTENTS       1
                MAXEXTENTS       2147483645
                PCTINCREASE      0
               ));

GRANT DELETE, INSERT, SELECT, UPDATE ON  TOX.SM_SEND_SM_LIST TO CALLCENTER;
GRANT INSERT, SELECT ON  TOX.SM_SEND_SM_LIST TO JSKM;
GRANT ALTER, DELETE, INDEX, INSERT, REFERENCES, SELECT, UPDATE, ON COMMIT REFRESH, QUERY REWRITE, DEBUG, FLASHBACK ON  TOX.SM_SEND_SM_LIST TO TA;
GRANT INSERT, SELECT ON  TOX.SM_SEND_SM_LIST TO WEB;
 
--  There are no FKeys that reference the new table to recreate.
 
--  Recompile any dependent objects
ALTER PACKAGE  "TA"."SMSSERVICE" COMPILE PACKAGE ;
ALTER PACKAGE  "TOX"."SMSSERVICE" COMPILE PACKAGE ;
ALTER PROCEDURE "TA"."P_NAVSMS" COMPILE ;
ALTER PROCEDURE "TA"."P_SENDSMS" COMPILE ;
 
--  Rebuild triggers for the new table.
--  *** WARNING *** WARNING *** WARNING *** WARNING *** WARNING ***
--  ***
--  *** You may need to run the 'create trigger'
--  *** statements as the table owner!

DROP TRIGGER TOX.BFINSERT_SM_SEND;

CREATE OR REPLACE TRIGGER TOX."BFINSERT_SM_SEND" BEFORE
INSERT ON "SM_SEND_SM_LIST"
    FOR EACH ROW
begin
  select SendSNo.nextval into :new.serialno from dual;
end;
/
SHOW ERRORS;