表分析 analyze 及dbms_stats 提高执行计划准确性

最新推荐文章于 2024-06-27 17:57:31 发布

原创

最新推荐文章于 2024-06-27 17:57:31 发布 · 1.2k 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#statistics #oracle #table #schema #structure #sql

本文介绍了在Oracle数据库中，由于频繁的DML操作导致执行计划不准确的问题，以及如何通过使用analyze和dbms_stats包来改善执行效率。通过分析表和索引的统计信息，可以提高SQL查询的性能。文章详细阐述了analyze命令和dbms_stats包的使用方法，包括参数设置和实例应用，特别强调了dbms_stats在处理分区表和自动样本大小、直方图生成方面的优势。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

之前遇到这样一个问题，有个表有3000W多条数据，并且每月都有300W左右的数据量在增加，同时这个表的数据经常需要查询更新和删除。

由于经常进行插入，删除，更新从而导致执行计划很不准，一条很简单的链接查询都要好几分钟。稍微复杂点的查询经常超时。

经过表分析后，同一条查询语句，执行计划变了不少，查询速度也提高了许多。现在将用过的几个语句抄录下来，或许对有的朋友来说用的着。

一.analyze

1、功能
   a.搜集和删除索引、表盒簇的统计信息
   b.验证表、索引和簇的结构
   c.鉴定表、簇和行迁移行连接
   d.针对analyze 的搜集和删除统计信息功能而言，oracle 推荐使用DBMS_STATS 包来搜集优化信息,DBMS_STATS可以并行的搜集

信息，可以搜集分区表的全局信息，进一步来说，按成本的优化器只会使用DBMS_STATS包所统计出来的信息。
2、使用的先决条件：必须在自己的方案中有 analyze any system 的权限

3. 实例

a. sql> Analyze table tablename compute statistics;
b. sql>Analyze index|cluster indexname estimate statistics;

   c.   sql>ANALYZE TABLE tablename DELETE STATISTICS;
   d.   sql>ANALYZE TABLE tablename VALIDATE REF UPDATE;
   e.   sql>ANALYZE TABLE tablename VALIDATE STRUCTURE ;

   3.分析表的限制
    a)不可以分析数据字典表
    b)不可以分析扩展表，但可以用DBMS_STATS来实现这个目的
     c)不可以分析临时表
    d)不可以计算或估计下列字段类型REFs, varrays, nested tables, LOBs (LOBs are not analyzed, they are skipped), LONGs, or object types.

二.dbms_stats包

1.功能简介

dbms_stats是提高SQL执行速度的一种出色机制。通过使用dbms_stats来收集最高质量的统计数据，CBO能够正确判断执行任何SQL查询时的