統計情報をOracle任せにした際、対象となるオブジェクトを確認する

以下のプロシージャではOPTIONS　＝＞’GATHER AUTO’ と指定した場合、統計情報の取得対象をOracle任せにする事ができます。運用に携わる側としてはお気楽なのですが、たまに『実際には何が対象になるのだろうか』と確認したくなる時があります。そんな時は OBJLISTを指定して事前に対象オブジェクトの一覧を確認する事ができます。
DBMS_STATS.GATHER_DICTIONARY_STATS
DBMS_STATS.GATHER_DATABASE_STATS
DBMS_STATS.GATHER_SCHEMA_STATS

DECLARE 
	filter_lst   DBMS_STATS.OBJECTTAB;
	outputline   varchar2(120) := null;
BEGIN
	DBMS_STATS.GATHER_SCHEMA_STATS (
    	   OPTIONS => 'LIST AUTO'
   	  ,OWNNAME => 'SOONI'
    	  ,OBJLIST => filter_lst
	);
	-- 見出し
	outputline := concat(outputline,'OBJTYPE'||',');
	outputline := concat(outputline,'OWNNAME'||',');
 	outputline := concat(outputline,'OBJNAME'||',');
 	outputline := concat(outputline,'PARTNAME'||',');
 	outputline := concat(outputline,'SUBPARTNAME');
	DBMS_OUTPUT.PUT_LINE(outputline);
  
	FOR i IN 1..filter_lst.COUNT LOOP
  		outputline := null;
  		outputline := concat(outputline,filter_lst(i).OBJTYPE||',');
  		outputline := concat(outputline,filter_lst(i).OWNNAME||',');
  		outputline := concat(outputline,filter_lst(i).OBJNAME||',');
  		outputline := concat(outputline,filter_lst(i).PARTNAME||',');
  		outputline := concat(outputline,filter_lst(i).SUBPARTNAME);
  		DBMS_OUTPUT.PUT_LINE(outputline);
  	END LOOP;
END;

SQL> set lin 200
SQL> DECLARE
  2  filter_lst   DBMS_STATS.OBJECTTAB;
  3  outputline   varchar2(120) := null;
  4  BEGIN
  5  DBMS_STATS.GATHER_SCHEMA_STATS (
  6       OPTIONS => 'LIST AUTO'
  7     ,OWNNAME => 'SOONI'
  8      ,OBJLIST => filter_lst
  9  );
 10  -- 見出し
 11  outputline := concat(outputline,'OBJTYPE'||',');
 12  outputline := concat(outputline,'OWNNAME'||',');
 13   outputline := concat(outputline,'OBJNAME'||',');
 14   outputline := concat(outputline,'PARTNAME'||',');
 15   outputline := concat(outputline,'SUBPARTNAME');
 16  DBMS_OUTPUT.PUT_LINE(outputline);
 17
 18  FOR i IN 1..filter_lst.COUNT LOOP
 19    outputline := null;
 20    outputline := concat(outputline,filter_lst(i).OBJTYPE||',');
 21    outputline := concat(outputline,filter_lst(i).OWNNAME||',');
 22    outputline := concat(outputline,filter_lst(i).OBJNAME||',');
 23    outputline := concat(outputline,filter_lst(i).PARTNAME||',');
 24    outputline := concat(outputline,filter_lst(i).SUBPARTNAME);
 25    DBMS_OUTPUT.PUT_LINE(outputline);
 26    END LOOP;
 27  END;
 28  /
OBJTYPE,OWNNAME,OBJNAME,PARTNAME,SUBPARTNAME
TABLE,SOONI,TBL26,,

PL/SQLプロシージャが正常に完了しました。

SQL>

分割実行

DBMS_STATS.GATHER_DICTIONARY_STATSは、めったに実行するようなものでもないし、どれほど時間がかかるかも不安だったので、分割実行のスクリプトを書いてみました。以下は５００個までのテーブルについて統計情報を取得しています

DECLARE 
 filter_lst   DBMS_STATS.OBJECTTAB;
 outputline   varchar2(120) := null;
 subcount     number ;
 exesql varchar2(800);
 exeobj varchar2(80);
BEGIN
 DBMS_STATS.GATHER_DICTIONARY_STATS (
        OPTIONS => 'LIST AUTO'
       ,OBJLIST => filter_lst
 );
 -- 見出し
 outputline := concat(outputline,'OBJTYPE'||',');
 outputline := concat(outputline,'OWNNAME'||',');
 outputline := concat(outputline,'OBJNAME'||',');
 outputline := concat(outputline,'PARTNAME'||',');
 outputline := concat(outputline,'SUBPARTNAME');
 DBMS_OUTPUT.PUT_LINE(outputline);
 subcount :=0;

 FOR i IN 1..filter_lst.COUNT LOOP
    outputline := null;
    outputline := concat(outputline,filter_lst(i).OBJTYPE||',');
    outputline := concat(outputline,filter_lst(i).OWNNAME||',');
    outputline := concat(outputline,filter_lst(i).OBJNAME||',');
    outputline := concat(outputline,filter_lst(i).PARTNAME||',');
    outputline := concat(outputline,filter_lst(i).SUBPARTNAME);
    subcount := subcount + 1;
    if subcount > 500 then 
        exit;
    end if;

    if filter_lst(i).OBJTYPE = 'TABLE' then
        exesql := 'begin DBMS_STATS.GATHER_TABLE_STATS( OWNNAME => ''"'|| filter_lst(i).OWNNAME||'"'',TABNAME => ''"'||filter_lst(i).OBJNAME||'"'',METHOD_OPT => ''FOR ALL COLUMNS SIZE AUTO''); end;';
        EXECUTE IMMEDIATE exesql;
    end if;
    DBMS_OUTPUT.PUT_LINE(outputline);
    END LOOP;
END;