背景

开发、测试和生产过程中，经常需要收集统计信息，以提高SQL执行效率。这里分享一段我常用的统计信息收集代码。

代码分享

这段SQL代码可以按配置的SCHEMA、样例数据百分比来收集列、索引和统计信息，并把收集日志输出到一张日志表。

CREATE TABLE IF NOT EXISTS STATS_COLLECT_LOG(SQL_TEXT TEXT,SQL_TYPE VARCHAR(50),EXEC_TIME VARCHAR(50));
-- 创建临时表表记录已迁移的索引
CREATE TEMPORARY TABLE IF NOT EXISTS TMP_INDEX_STATS_TOTAL(OWNER VARCHAR,TABLE_NAME VARCHAR,INDEX_NAME VARCHAR);
TRUNCATE TABLE TMP_INDEX_STATS_TOTAL;
DECLARE
    TYPE STR_LIST IS TABLE OF VARCHAR2(20);
    -- 插入索引清单，需要替换OWNER
    OWNERS STR_LIST:= STR_LIST(
                                'TESTDB'
                                );
    -- 选择收集统计信息方式 ： 1 收集所有列，2 收集索引列，3 收集非索引列
    CODE INT := 1;
    STR_SQL VARCHAR2(2000);    
    -- 输入收集的百分比 10~100
    V_ESTIMATE_PERCENT INT := 20;    
BEGIN
    
    -- 插入索引清单，需要替换你的OWNER
    INSERT INTO TMP_INDEX_STATS_TOTAL
    SELECT A.OWNER, 
           A.TABLE_NAME, 
           A.INDEX_NAME
      FROM ALL_INDEXES A 
      JOIN (SELECT OWNER,TABLE_NAME FROM ALL_TABLES) B 
        ON A.OWNER=B.OWNER 
       AND A.TABLE_NAME=B.TABLE_NAME
     WHERE A.OWNER IN (SELECT COLUMN_VALUE FROM TABLE(OWNERS))
    ORDER BY A.OWNER,A.TABLE_NAME,A.INDEX_NAME;

    -- 收集索引
    for rs in (
        SELECT 'CALL DBMS_STATS.GATHER_INDEX_STATS(OWNNAME => ''' ||OWNER ||''', INDNAME => ''' ||INDEX_NAME ||''',ESTIMATE_PERCENT => '||V_ESTIMATE_PERCENT||');' AS "COLLECT_INDEX_STATS" FROM TMP_INDEX_STATS_TOTAL
    ) loop
	  BEGIN
		execute  immediate rs.COLLECT_INDEX_STATS;
	    INSERT INTO STATS_COLLECT_LOG values(rs.COLLECT_INDEX_STATS,'COLLECT INDEX STATS',now());
	  END;
	  COMMIT;
    end loop;
      
      
    IF CODE == 1 THEN  
        -- 收集所有列
		for rs2 in (
			SELECT 'CALL DBMS_STATS.GATHER_TABLE_STATS( OWNNAME => ''' ||OWNER ||''', TABNAME => ''' ||TABLE_NAME ||''',ESTIMATE_PERCENT => '||V_ESTIMATE_PERCENT||',METHOD_OPT => ''FOR ALL COLUMNS SIZE AUTO' ||''', CASCADE => FALSE, DEGREE => 8);' AS "COLLECT_ALL_COL"
			  FROM ALL_TABLES
			 WHERE OWNER IN (SELECT COLUMN_VALUE FROM TABLE(OWNERS))
		 )
		 loop
		  BEGIN
			execute  immediate rs2.COLLECT_ALL_COL;
			INSERT INTO STATS_COLLECT_LOG values(rs2.COLLECT_ALL_COL,'COLLECT ALL COLUMNS',now());
		  END;
		  COMMIT;
		end loop;

    ELSIF CODE == 2 THEN 
        -- 收集索引列
		for rs2 in (
			SELECT 'CALL DBMS_STATS.GATHER_TABLE_STATS( OWNNAME => ''' ||OWNER ||''', TABNAME => ''' ||TABLE_NAME ||''',ESTIMATE_PERCENT => '||V_ESTIMATE_PERCENT||',METHOD_OPT => ''FOR ALL INDEXED COLUMNS SIZE AUTO' ||''', CASCADE => FALSE, DEGREE => 8);' AS "COLLECT_INDEX_COL"
			  FROM ALL_TABLES
			 WHERE OWNER IN (SELECT COLUMN_VALUE FROM TABLE(OWNERS))
		 )
		 loop
		  BEGIN
			execute  immediate rs2.COLLECT_INDEX_COL;
			INSERT INTO STATS_COLLECT_LOG values(rs2.COLLECT_INDEX_COL,'COLLECT INDEX COLUMNS',now());
		  END;
		  COMMIT;
		end loop;
    ELSIF CODE == 3 THEN
        -- 收集非索引列
		for rs2 in (
        SELECT 'CALL DBMS_STATS.GATHER_TABLE_STATS( OWNNAME => ''' ||OWNER ||''', TABNAME => ''' ||TABLE_NAME ||''',ESTIMATE_PERCENT => '||V_ESTIMATE_PERCENT||',METHOD_OPT => ''FOR ALL HIDDEN COLUMNS SIZE AUTO' ||''', CASCADE => FALSE, DEGREE => 8);' AS "COLLECT_HIDDEN_COL"
          FROM ALL_TABLES
         WHERE OWNER IN (SELECT COLUMN_VALUE FROM TABLE(OWNERS))
		 )
		 loop
		  BEGIN
			execute  immediate rs2.COLLECT_HIDDEN_COL;
			INSERT INTO STATS_COLLECT_LOG values(rs2.COLLECT_HIDDEN_COL,'COLLECT HIDDEN COLUMNS',now());
		  END;
		  COMMIT;
		end loop;
    ELSE
        PRINT '未选择收集方式，请重新执行！！！';
    END IF;
    
    EXCEPTION
        WHEN NO_DATA_FOUND THEN
          PRINT 'OWNERS或CODE变量未赋值！！！';
	PRINT '执行完成,可通过STATS_COLLECT_LOG查看详细日志';
END;

使用方式

方式1. 粘贴到DISQL或者manager.exe 直接执行(如果数据量较大，建议用DISQL后台跑)
方式2. 配置定时作业，定时收集。