9i_crt_gather_table_stats（生成表的統計資訊收集指令碼，關鍵在引數）

set serverout on;
declare
  v_owner       varchar2(200) := upper('&tab_owner');
  v_table_name  varchar2(200) := upper('&tab_name');
  v_num_rows    number;
  v_message     varchar2(300);
  v_est_percent varchar2(300);
  V_SQLTEXT     VARCHAR2(4000);
  v_degree      number;
  V_COUNT       number;
  V_COUNT2       number; 
  v_statid      varchar2(31);
  v_METHOD_OPT  varchar2(50);
  v_last_analyzed varchar2(30);
  v_sqltext2 varchar2(400);
begin
SELECT SUBSTR(V_TABLE_NAME, 1, 28)||MOD(NVL(MAX(SUBSTR(STATID, -1, 1)), 0) + 1,10)
 INTO v_statid
   FROM DBMGR.CBO_STATS_BAK
 WHERE C1 = V_TABLE_NAME
   AND C5 = v_owner
   AND D1 = (SELECT MAX(D1)
               FROM DBMGR.CBO_STATS_BAK
              WHERE C1 =  V_TABLE_NAME
                AND C5 = v_owner
                AND STATID NOT LIKE 'DATABASE_');
select to_char(last_analyzed,'YYYYMMDD HH24:MI:SS') into v_last_analyzed from dba_tables where table_name=V_TABLE_NAME and wner=v_owner;

  v_sqltext:='begin  dbms_stats.export_table_stats(ownname => '''||v_owner||''','||chr(10)||
                                'tabname => '''||v_table_name||''','||chr(10)||
                                'stattab => ''CBO_STATS_BAK'','||chr(10)||
                                'statown => ''dbmgr'','||chr(10)||
                                'cascade => true,'||chr(10)||
                                'statid  => '''||v_statid||''');'||chr(10)||
                   'end;'||chr(10)||
                   '/';
v_sqltext2:='insert into dbmgr.cbo_backup_info(OWNER,TABLE_NAME,last_analyzed,statid,Backup_Date)  values('''||v_owner||''','''||v_table_name||''',to_date('''||v_last_analyzed||''',''YYYY-MM-DD HH24:MI:SS''),'''||v_statid||''',SYSDATE);';  

  dbms_output.put_line('------------backup_scripts---------------') ;                     
  dbms_output.put_line(v_sqltext) ;
  dbms_output.put_line(v_sqltext2) ;                                    
  dbms_output.put_line('commit;') ;
                      
  select count(*)
    into v_count
    from dbstats.STATS_GATHER_LOG_DETAIL sts
   where sts.object_owner = v_owner
     and sts.object_name = v_table_name
     and sts.gather_message like 'BEGIN%'
     and rownum = 1;
  if v_count > 0 then
    begin
    SELECT replace(gather_message, 'CASCADE => FALSE', 'CASCADE => TRUE')
      into v_sqltext
      FROM DBSTATS.STATS_GATHER_LOG_DETAIL SG
     WHERE SG.GATHER_START_TIME =
           (select MAX(GATHER_START_TIME)
              from dbstats.STATS_GATHER_LOG_DETAIL sts
             where sts.object_owner = v_owner
               and sts.object_name = v_table_name
               and sts.object_type='TABLE'
               and sts.gather_message like 'BEGIN%')
       AND SG.object_owner = v_owner
       AND SG.object_name = v_table_name;
     SELECT COUNT(*)
        INTO V_COUNT2
        FROM DBA_TAB_PARTITIONS
       WHERE table_owner = v_owner
         AND table_name = v_table_name;
       if v_count2=0 then
       v_sqltext:=v_sqltext;
       else
        select REPLACE(V_SQLTEXT,'GRANULARITY => ''DEFAULT''','GRANULARITY => ''ALL''') into V_SQLTEXT from dual;
        end if;
    dbms_output.put_line('------------gather_scripts---------------') ;                     
    dbms_output.put_line(substr(v_sqltext,
                                0,
                                instr(v_sqltext, 'BLOCK_SAMPLE') - 1));
    dbms_output.put_line(substr(v_sqltext,
                                instr(v_sqltext, 'BLOCK_SAMPLE')));                     
   
    end;
  else
    begin
       select num_rows
        into v_num_rows
        from dba_tables
       where wner = v_owner
        AND table_name = v_table_name;
      if v_num_rows is null then
        V_METHOD_OPT:='FOR ALL COLUMNS SIZE 1';
        else v_METHOD_OPT:='FOR ALL COLUMNS SIZE REPEAT';
      end if;     
      if v_num_rows < 5000000  or v_num_rows is null then
        v_est_percent := 20;
      end if;
      if v_num_rows between 5000000 and 10000000 then
        v_est_percent := 10;
      end if;
      if v_num_rows between 10000000 and 100000000 then
        v_est_percent := 5;
      end if;
      if v_num_rows > 100000000 then
        v_est_percent := 1;
      end if;
      if v_num_rows < 1000000 or v_num_rows is null then
        v_degree := 1;
      end if;
      if v_num_rows between 1000000 and 5000000 then
        v_degree := 2;
      end if;
      if v_num_rows between 5000000 and 10000000 then
        v_degree := 4;
      end if;
      if v_num_rows between 10000000 and 50000000 then
        v_degree := 6;
      end if;
      if v_num_rows between 50000000 and 100000000 then
        v_degree := 8;
      end if;
      if v_num_rows > 100000000 then
        v_degree := 12;
      end if;
      SELECT COUNT(*)
        INTO V_COUNT
        FROM DBA_TAB_PARTITIONS
       WHERE table_owner = v_owner
         AND table_name = v_table_name;
      if V_COUNT = 0 then

        V_SQLTEXT := 'BEGIN DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => ''' || V_OWNER || ''',' || CHR(10) ||
                     'TABNAME => ''' || V_TABLE_NAME || ''',' || CHR(10) ||
                     'ESTIMATE_PERCENT => ' || v_est_percent || ',' || CHR(10) ||
                     'METHOD_OPT => '''||V_METHOD_OPT||''',' || CHR(10) ||
                     'DEGREE => ' || v_degree || ',' || CHR(10) ||
                     'CASCADE => TRUE,' || CHR(10) ||
                     'NO_INVALIDATE => FALSE);' || CHR(10) ||
                     'END;';
        dbms_output.put_line('------------gather_scripts---------------') ;                     
        dbms_output.put_line(V_SQLTEXT);
        dbms_output.put_line('------------gather_scripts---------------') ;                             
      else
        V_SQLTEXT := 'BEGIN DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => ''' || V_OWNER || ''',' || CHR(10) ||
                     'TABNAME => ''' || V_TABLE_NAME || ''',' || CHR(10) ||
                     'ESTIMATE_PERCENT => ' || v_est_percent || ',' || CHR(10) ||
                     'METHOD_OPT => '''||V_METHOD_OPT||''',' || CHR(10) ||
                     'DEGREE => ' || v_degree || ',' || CHR(10) ||
                     'GRANULARITY => ''ALL'',' || CHR(10) ||
                     'CASCADE => TRUE,' || CHR(10) ||
                     'NO_INVALIDATE => FALSE);' || CHR(10) ||
                      'END;';
        dbms_output.put_line('------------gather_scripts---------------') ;                     
        dbms_output.put_line(V_SQLTEXT);
        dbms_output.put_line('------------gather_scripts---------------') ;                     
      end if;
    end;
  end if;
exception
  when others then
    v_message := sqlerrm;
    dbms_output.put_line(v_message);
end;
/