117笔记问答优化Oracle数据库的统计信息收集策略是提高查询性能的关键步骤。以下是一些有效的优化方法：
- 自动统计信息收集：
- Oracle 10g及以后版本支持自动统计信息收集，可以通过设置DBMS_SCHEDULER任务来自动收集统计信息。例如，可以设置一个定时任务，在数据库维护窗口期间自动运行DBMS_STATS.GATHER_DATABASE_STATS_JOB_PROC过程，以收集所有对象的统计信息。
- 确保STATISTICS_LEVEL参数设置为TYPICAL或ALL，以便系统在夜间自动收集统计信息。可以通过查询DBA_SCHEDULER_JOBS视图来查看自动统计信息收集的作业状态。
- 手工收集统计信息：
- 对于更新频繁的对象，手工收集统计信息可能更为合适。可以使用DBMS_STATS包来手工收集表的统计信息，例如：
EXEC DBMS_STATS.GATHER_TABLE_STATS('schema_name', 'table_name');。 - 对于外部表，统计信息不能通过自动统计收集收集，需要使用GATHER_TABLE_STATS在单个表上收集统计信息。
- 统计信息收集的并行性：
- 设置DBMS_STATS的DEGREE参数为DBMS_STATS.AUTO_DEGREE，允许Oracle根据对象的大小和并行性初始化参数的设置选择恰当的并行度。这可以提高统计信息收集的速度。
- 分区对象的统计收集：
- 对于分区表和索引，DBMS_STATS可以收集单独分区的统计和全局分区。对于组合分区，可以收集子分区、分区、表/索引上的统计。通过设置GRANULARITY参数为AUTO，可以同时收集全部信息。
- 直方图和扩展统计信息：
- 使用DBMS_STATS的method_opt参数来控制是否收集直方图和扩展统计信息。例如，method_opt => 'for all columns size auto’将收集所有列的统计信息，包括直方图。
- 锁定统计信息：
- 当统计信息已经过时，可以通过删除并锁住统计信息来强制Oracle在下一次查询时动态收集统计信息。这可以通过DBMS_STATS.DELETE_TABLE_STATS和DBMS_STATS.LOCK_TABLE_STATS过程实现。
- 查看直方图信息：
- 使用DBA_TAB_HISTOGRAMS视图来查看表的统计信息，包括直方图信息。这有助于了解数据分布，从而优化查询。
- 收集数据字典统计信息：
- 使用DBMS_STATS.GATHER_DICTIONARY_STATS过程来收集所有系统模式的统计信息。这对于优化数据库对象的访问非常重要。
- 优化统计信息收集的权限：
- 必须授予普通用户权限才能执行统计信息的收集。这可以通过GRANT命令来实现，例如：
GRANT CONNECT,RESOURCE,ANALYZE ANY TO hr;。
- 统计收集的时间考虑：
- 统计收集使用取样，最小化收集统计的必要资源。Oracle推荐设置DBMS_STATS的ESTIMATE_PERCENT参数为DBMS_STATS.AUTO_SAMPLE_SIZE，以在达到必要的统计精确性的同时最大化性能。
通过上述方法，可以有效地优化Oracle数据库的统计信息收集策略，从而提高查询性能和整体数据库性能。