在遇到一些表数据较大、分区表数量较多的大系统时,收集统计信息一直是前期工作的一个小麻烦,因为经常直接运行 DBMS_STATS.GATHER_SCHEMA_STATS('用户名',100,TRUE,'FOR ALL COLUMNS SIZE AUTO') 收集一个模式的统计信息时,一个晚上无法收集完,甚至需要好几天时间。
另外,以上SQL只能使用一个CPU,在夜晚进行统计时数据库一般处于较闲的状态,没法利用CPU资源。所以我们可以采用多会话的方式来收集。
--登录SYSDBA用户
--创建统计信息记录表
CREATE TABLE MY_TAB_STATS(TABLE_NAME VARCHAR2(100),STAT_FLAG VARCHAR2(5));
--初始化统计信息记录表,如目标是用户DMHR下的所有表
INSERT INTO MY_TAB_STATS(TABLE_NAME) SELECT TABLE_NAME FROM DBA_TABLES WHERE OWNER = 'DMHR';
UPDATE MY_TAB_STATS SET STAT_FLAG = 0;
COMMIT;
--收集统计信息
BEGIN
FOR TB IN (SELECT ROWID,TABLE_NAME FROM MY_TAB_STATS WHERE ROWID<=1000) LOOP
DBMS_STATS.GATHER_TABLE_STATS('FMIS0100',TB.TABLE_NAME,NULL,100,TRUE,'FOR ALL COLUMNS SIZE AUTO');
UPDATE MY_TAB_STATS SET STAT_FLAG = 1 WHERE TABLE_NAME = TB.TABLE_NAME;
COMMIT;
END LOOP;
END;
用ROWID可以用户将表分成多个区间进行收集。比如有20000张表需要收集,分成1~5000、5001~10000、10001~15000和15001~20000来收集的话,就能利用到4个CPU,提高收集统计信息的效率。如果发生报错而中断的情况,能根据表中的STAT_FLAG列来检查进度,以便于下次继续收集。