1. 收集数据字典统计信息(针对SYS,SYSTEM 及其他数据库用户)
EXEC DBMS_STATS.gather_dictionary_stats;
2. 收集基表的统计信息V$,gv$视图
EXEC DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;
select table_name, num_rows, last_analyzed
from dba_tab_statistics
where last_analyzed is not null
order by last_analyzed desc
3. 收集全库统计信息
EXEC DBMS_STATS.gather_database_stats;
--如果数据库很大的话,可以修改采用率或并发,提高收集统计信息速度
EXEC DBMS_STATS.gather_database_stats(estimate_percent => 15);
EXEC DBMS_STATS.gather_database_stats(estimate_percent => 15, cascade => TRUE);
EXEC DBMS_STATS.gather_database_stats(estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,degree => 8);
4. 修改指定用户名统计信息