ORACLE 在执行 SQL 时如果使用成本方式分析则所有的成本分析信息来源依靠于系统的统计分析表( DBA_TABLES 、 DBA_INDEXES 、 DBA_TAB_COLUMNS )数据,如果说统计分析的数据是不准确的,那可能会使 ORACLE 分析出来的路径执行性能极差,所以统计分析数据是影响 ORACLE 性能极重要的信息。
** 统计分析主要包括产生表及索引的统计信息
**
表的统计信息主要包括表的行数,每行的平均长度(字节),空闲块,统计时间等信息
索引的统计信息主要包括行数、层数、叶块数、统计时间等信息。
另外 ORACLE 还可以统计列及数据不对称信息, 9i 还可以统计系统信息( CPU , I/O )
ORACLE 执行成本分析时首先取出所应用表及索引的统计数据进行分析,其中数据行数是一个重要的参数,因为 ORACLE 在分析表大小时行数为主要参数,如果进行两个表联合时, ORACLE 会通过分析表的大小,决定应用小表进行全表查询,而大表执行联合查询,这种性能明显高于先大表进行全表扫描。索引的统计信息对分析也产生比较大的影响,如 ORACLE 通过统计可以分析产生多个索引的优先级及索引的实用性来确定最优的索引策略。 ORACLE 还可以统计列及数据对称信息以产生更精确的分析。如一个表有 A 字段的索引,其中 A 共有两种值 1 和 0 ,共 10000 条记录,为 0 的记录有 10 条,为 1 的记录有 9990 条,这时如果没有进行列数据不对称的统计信息,那么 ORACLE 对 A=0 及 A=1 条件查询都会进行索引,但实际应用对 A=0 的索引性能得到了很大的提高,而 A=1 的索引反而使性能下降。所以说索引特征值分析信息对应用索引产生重大影响,精确的信息使 ORACLE 不会使用不应该用的索引。
** 实际分析
**
zl_cbqc 和 zl_yhjbqk 都没有建立统计信息,执行如下两个 SQL ORACLE 将产生不同的执行计划。
1 select * from dlyx.zl_cbqc b, dlyx.zl_yhjbqk a where a.qc_bh=b.qc_bh
** 执行计划:
**
SELECT STATEMENT, GOAL = CHOOSE
NESTED LOOPS
**TABLE ACCESS FULL DLYX ZL_YHJBQK
**
TABLE ACCESS BY INDEX ROWID DLYX ZL_CBQC
INDEX UNIQUE SCAN DLYX 抄表区册主键
2 select * from dlyx.zl_yhjbqk a,dlyx.zl_cbqc b where a.qc_bh=b.qc_bh
** 执行计划:
**
SELECT STATEMENT, GOAL = CHOOSE
NESTED LOOPS
**TABLE ACCESS FULL DLYX ZL_CBQC
**
TABLE ACCESS BY INDEX ROWID DLYX ZL_YHJBQK
INDEX RANGE SCAN DLYX 区册索引
在对两个表进行了统计分析后
** 3 ** ** select ** * from dlyx.zl_yhjbqk a,dlyx.zl_cbqc b where a.qc_bh=b.qc_bh
** 执行计划: ** **
**
SELECT STATEMENT, GOAL = CHOOSE 159 72853 9689449
HASH JOIN 159 72853 9689449
TABLE ACCESS FULL DLYX ZL_CBQC 1 426 19596
TABLE ACCESS FULL DLYX ZL_YHJBQK 157 72853 6338211
** 4 select ** * from dlyx.zl_cbqc b,dlyx.zl_yhjbqk a where a.qc_bh=b.qc_bh
** 执行计划:
**
SELECT STATEMENT, GOAL = CHOOSE 159 72853 9689449
HASH JOIN 159 72853 9689449
TABLE ACCESS FULL DLYX ZL_CBQC 1 426 19596
TABLE ACCESS FULL DLYX ZL_YHJBQK 157 72853 6338211
从以上测试可以明显看出 ORACLE 的分析结果
第 1 条 SQL 与第 2 条 SQL 在没有统计分析的信息下进行, ORACLE 无法进行判断,只能以规则方法进行分析,根据表的出现先后顺序有明显关系。
第 3 条 SQL 与第 4 条 SQL 在有统计分析的信息下进行, ORACLE 分析与表的出现先后顺序无关,因为它已经知道了表的数据量并且已经确定返回的数据量基本上是两个表所有的数据,所以对表两个表进行了 HASH JOIN (同时取出两个表的数据然后在内存中进行联合产生返回结果)。
** 相关技术
**
** 用 analyze ** ** 语句产生分析数据
**
分析表: analyze table zl_yhjbqk estimate statistics sample 20 percent
分析索引: analyze index 用户资料表主键 compute statistics
分析列: ** analyze ** table zl_yhjbqk compute statistics for columns hbs_bh
分析索引列: ** analyze ** table zl_yhjbqk compute statistics for all indexed **columns
**
** 用 sys.dbms_utility ** ** 包分析数据
**
分析数据库(包括所有的用户对象和系统对象): analyze_database
分析用户所有的对象(包括用户方案内的表、索引、簇): analyze_schema
** 用 sys.dbms_stats ** ** 包处理分析数据
**
分析数据库(包括所有的用户对象和系统对象): gather_database_stats
分析用户所有的对象(包括表、索引、簇): gather_schema_stats
分析表: gather_table_stats
分析索引: gather_index_stats
删除数据库统计信息: delete_database_stats
删除用户方案统计信息: delete_schema_stats
删除表统计信息: delete_table_stats
删除索引统计信息: delete_index_stats
删除列统计信息: delete_column_stats
设置表统计信息: set_table_stats
设置索引统计信息: set_index_stats
设置列统计信息: set_column_stats
ORACLE 推荐用户采用 sys.dbms_stats 包体进行分析,因为在 ORACLE9i 及其以上的版本全面扩充的此包体的功能。 sys.dbms_utility 包体进行分析时会对所有的信息全部分析一遍,时间比较长,而在 9i 中 sys.dbms_stats 可以利用表修改监控技术来判断需统计分析的表进行,节省了用户的分析资源。
** 备注:
**
ORACLE 在得到了表的统计分析数据后才会进行成本分析,否则采用规则分析。
并不是所有的统计数据都会对 ORACLE 分析产生影响,有些统计数据 ORACLE 并不处理,只是提供给用户一个参考信息,也可能在 ORACLE 以后的版本中利用这些信息进行分析。
分析表的登录用户必须对表拥有访问权限或具有 DBA 或 Analyze any 权限