对用户下所有没有统计信息或者统计信息过期的表进行收集统计信息脚本

对用户对用户下所有没有统计信息或者统计信息过期的表进行收集统计信息脚本

注意degree我这里设置为4的，及并行收集

declare
  cursor stale_table is
    select owner,
           segment_name,
           case
             when segment_size < 1 then
              100
             when segment_size >= 1 and segment_size <= 5 then
              50
             when segment_size > 5 then
              30
           end as percent,
           4 as degree　　　　　　　　　　　　--设置收集统计信息的并行度
      from (select owner,
                   segment_name,
                   sum(bytes / 1024 / 1024 / 1024) segment_size
              from DBA_SEGMENTS
             where owner = 'SCOTT'
               and segment_name in
                   (select table_name
                      from DBA_TAB_STATISTICS
                     where (last_analyzed is null or stale_stats = 'YES')
                       and owner = 'SCOTT' AND STATTYPE_LOCKED IS NULL)     　　　　--修改自己的用户名
             group by owner, segment_name);
begin
  dbms_stats.flush_database_monitoring_info;
  for stale in stale_table loop
    dbms_stats.gather_table_stats(ownname          => stale.owner,
                                  tabname          => stale.segment_name,
                                  estimate_percent => stale.percent,
                                  method_opt       => 'for all columns size repeat',　　　　　　　　--表示当前有哪些列收集了直方图，现在就对哪些列收集直方图
                                  degree           => stale.degree,
                                  cascade          => true);
  end loop;
end;
/