在 Oracle 数据库中，分析表和索引是优化查询性能的重要步骤。通过分析表和索引，可以收集统计信息，帮助优化器生成更高效的执行计划。以下是如何分析表和索引的详细步骤和示例脚本。
1. 收集表的统计信息
使用 DBMS_STATS 包中的 GATHER_TABLE_STATS 过程来收集表的统计信息。这包括表的行数、列的分布情况等。
示例脚本
BEGINDBMS_STATS.GATHER_TABLE_STATS(ownname => 'SCHEMA_NAME', -- 模式名称tabname => 'TABLE_NAME', -- 表名称estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, -- 采样百分比，AUTO_SAMPLE_SIZE 会自动选择合适的采样比例method_opt => 'FOR ALL COLUMNS SIZE AUTO', -- 统计信息收集方法cascade => TRUE, -- 是否级联收集索引的统计信息degree => 4 -- 并行度);
END;
/
2. 收集索引的统计信息
虽然在收集表的统计信息时可以级联收集索引的统计信息，但也可以单独收集索引的统计信息。
示例脚本
BEGINDBMS_STATS.GATHER_INDEX_STATS(ownname => 'SCHEMA_NAME', -- 模式名称indname => 'INDEX_NAME', -- 索引名称estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, -- 采样百分比degree => 4 -- 并行度);
END;
/
3. 分析整个模式
如果你需要分析整个模式中的所有表和索引，可以使用 GATHER_SCHEMA_STATS 过程。
示例脚本
BEGINDBMS_STATS.GATHER_SCHEMA_STATS(ownname => 'SCHEMA_NAME', -- 模式名称estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, -- 采样百分比method_opt => 'FOR ALL COLUMNS SIZE AUTO', -- 统计信息收集方法cascade => TRUE, -- 是否级联收集索引的统计信息degree => 4 -- 并行度);
END;
/
4. 定期自动收集统计信息
为了确保统计信息始终是最新的，可以设置一个定期任务来自动收集统计信息。可以使用 DBMS_SCHEDULER 创建一个作业。
示例脚本
BEGINDBMS_SCHEDULER.create_job (job_name => 'ANALYZE_TABLE_JOB',job_type => 'PLSQL_BLOCK',job_action => 'BEGIN DBMS_STATS.GATHER_TABLE_STATS(''SCHEMA_NAME'', ''TABLE_NAME'', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt => ''FOR ALL COLUMNS SIZE AUTO'', cascade => TRUE, degree => 4); END;',start_date => SYSTIMESTAMP,repeat_interval => 'FREQ=DAILY; BYHOUR=1; BYMINUTE=0; BYSECOND=0', -- 每天凌晨1点执行enabled => TRUE);
END;
/
5. 查看统计信息
可以通过查询 ALL_TAB_STATISTICS 和 ALL_IND_STATISTICS 视图来查看表和索引的统计信息。
示例查询
-- 查看表的统计信息
SELECT table_name,num_rows,blocks,empty_blocks,avg_space,chain_cnt,avg_row_len,sample_size,last_analyzed
FROM all_tab_statistics
WHERE owner = 'SCHEMA_NAME'AND table_name = 'TABLE_NAME';-- 查看索引的统计信息
SELECT index_name,blevel,leaf_blocks,distinct_keys,avg_leaf_blocks_per_key,avg_data_blocks_per_key,clustering_factor,sample_size,last_analyzed
FROM all_ind_statistics
WHERE owner = 'SCHEMA_NAME'AND index_name = 'INDEX_NAME';
6. 监控和调整
- 监控查询性能：使用
EXPLAIN PLAN和DBMS_XPLAN来查看查询的执行计划，确保优化器选择了最优的执行计划。 - 调整统计信息收集参数：根据实际情况调整
estimate_percent和degree参数，以获得最佳性能。
总结
通过定期收集和更新表和索引的统计信息，可以显著提高查询性能。使用 DBMS_STATS 包中的 GATHER_TABLE_STATS 和 GATHER_INDEX_STATS 过程，可以方便地收集统计信息，可以确保统计信息始终保持最新。