分析表与索引
set head off
set pagesize 0
set feedback off
select 'analyze table '||owner||'.'||table_name||' compute statistics for table for all indexes for all indexed columns;' from all_all_tables where owner in ('CP2');
如果有分区表， analyze方法更不适用。
exec dbms_stats.gather_table_stats(user,'KB01',cascade=>true);
select 'exec dbms_stats.gather_table_stats('''||owner||''','''||table_name||''',cascade=>true)'||';' from sys.dba_tables t where owner not in ('SYS','SYSTEM','PUBLIC','OUTLN');
select 'exec dbms_stats.gather_table_stats('''||table_owner||''','''||table_name||''',partname=>'''||partition_name||''',cascade=>true)'||';'
from dba_tab_partitions t where table_owner not in ('SYS','SYSTEM','PUBLIC','OUTLN');