ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 10g_crt_gather_table_stats(生成表的统计信息收集脚本,关键在参数)

10g_crt_gather_table_stats(生成表的统计信息收集脚本,关键在参数)

原创 Linux操作系统 作者:ljm0211 时间:2012-07-02 14:45:01 0 删除 编辑

set serverout on;
declare
  v_owner       varchar2(200) := upper('&tab_owner');
  v_table_name  varchar2(200) := upper('&tab_name');
  v_num_rows    int;
  v_message     varchar2(300);
  V_SQLTEXT     VARCHAR2(4000);
  v_degree      number;
  V_COUNT       number;
  V_METHOD_OPT  varchar2(50);
 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 < 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) ||
                     '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(V_SQLTEXT);
      else
        V_SQLTEXT := 'BEGIN DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => ''' || V_OWNER || ''',' || CHR(10) ||
                     'TABNAME => ''' || V_TABLE_NAME || ''',' || 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(V_SQLTEXT);
      end if;
exception
  when others then
    v_message := sqlerrm;
    dbms_output.put_line(v_message);
end;
/

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/11411056/viewspace-734329/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2009-05-14

  • 博文量
    272
  • 访问量
    437101