实验环境
搭建平台：VMware Workstation
OS：RHEL 6.10
Grid&DB：Oracle 11.2.0.4 SQL参考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 --超过5G的大表收集比例一般不超过30% 30 end as percent, 4 as degree from (select owner, segment_name, sum(bytes / 1024 / 1024 / 1024) segment_size from dba_segments where owner = 'TEST' and segment_name in (select table_name from dba_tab_statistics where (last_analyzed is null or stale_stats = 'YES') and owner = 'TEST') 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;/ 单独拆分成一条关键语句：SQL> exec dbms_stats.gather_table_stats(ownname => 'test',tabname => 'tab',estimate_percent => 50,method_opt => 'for all columns size auto',no_invalidate => false,degree => 6,cascade => true);
