数据库应用优化分析..docVIP

数据库优化分析 --孙锛 第一部分 SQL优化器 Oracle在执行一个SQL之前，首先要分析一下语句的执行计划（PLSQL 按F5看执行计划），然后按照执行计划去执行。分析语句的执行计划工作是由优化器（optimizer）来完成的。不同的情况，一条SQL可能有多种执行计划，但在某一时间点，一定只有一种执行计划是最优的，花费时间是最少的。 Oracle的优化器共有两种的优化方式，即基于规则的优化方式（Rule-Based Optimization，简称RBO）和基于代价的优化方式（Cost-Based Optimization，简称CBO）。 RBO方式 RBO优化器分析SQL语句时，遵循的是Oracle内部预定的一些规则。 简单讲主要是下面两点： 索引扫描永远优于全表扫描的规则 全表扫描为所有访询路径中最慢之规则 显然RBO是一个简单且易于理解的优化器，但是由于简单制约了灵活性，没办法根据实际情况选择执行计划。 举例：比如一个表只有两条数据，一次IO就可以完成全表的检索，而此时走索引时则需要两次IO，这时对这个表做全表扫描（full table scan）是最好的。但是如果使用的是RBO，就总是会选择走索引。 以上是主要是Oracle公司的解释，但是从实际使用来看，CBO方式对于一些情况会出现问题。 比如表A数据量1000万，每天新增数据10万，在不收集统计信息的情况下使用CBO方式，在系统运行一年后，CBO的执行计划出现变化，执行时间变化超过原来10倍，而在确定要使用索引的情况下，通过加 rule 提示，RBO是更好的方案。 有时，表明明建有索引，但查询过程显然没有用到相关的索引，导致查询过程耗时漫长，占用资源巨大，这时就需要仔细分析执行计划，找出原因。例如，可以看连接顺序是否允许使用相关索引。假设表emp的deptno列上有索引，表dept的列deptno上无索引，WHERE语句有emp.deptno=dept.deptno条件。在做NL连接时，emp做为外表，先被访问，由于连接机制原因，外表的数据访问方式是全表扫描，emp.deptno上的索引显然是用不上，最多在其上做索引全扫描或索引快速全扫描。 CBO方式 依词义可知，CBO是看语句的代价（Cost），这里的代价主要指IO,CPU和内存。优化器在这种模式下，主要参考的是表及索引的统计信息。统计信息给出表的大小，有多少行，每行的长度等信息。这些统计信息起初在库内是没有，是你在主动收集统计信息后才出现的（比如用dbms_stats,或者做了analyze后），一般来说过时的统计信息会令优化器选择一个错误的执行计划，因此我们应及时更新这些信息。在ORACLE8以后，ORACLE推荐用CBO方式。 CBO以以下步骤执行： 优化器基于可用的access path及hints，对SQL语句生成一组潜在的执行计划 基于数据字典中的统计数据中关于语句访问的每个表，列，索引，partitions的数据分布及存储特征，估计每个计划的cost 将plan的cost进行比较，并选择具有最低的cost的plan CBO中成本的概念及计算 CBO COST最小化，实际上是对服务器资源的最小化。 不同访问路径成本的计算 Full Table Scan（全表扫描的成本） 计算公式：blocks/k 其中： Blocks：表的数据块数量 K：系数，受MBRC的影响,系统根据实际情况修正 Index Access（索引扫描）的成本 计算公式： Unique scan：blevel+1 Range scan：blevel+FF*leaf_blocks+FF*clustering_factor 其中 Blevel 索引的层数 FF 根据选择度得到的计算因子 Leaf_blocks 索引的叶节点块数 Clustering_factor 确定通过索引定位数据块的系数 CBO的选择度 选择度，即selectivity，为根据条件将返回的记录占总记录数的比率，其值为0-1之间，越接近0，表示选择性越高，查询的效率就越高。 CBO使用 使用CBO 时，编写SQL语句时，不必考虑FROM 子句后面的表或视图的顺序和WHERE 子句后面的条件顺序。 使用CBO 时，SQL语句 FROM 子句后面的表，必须全部分析过，如果FROM 子句后面的是视图，则此视图的基础表，也必须全部分析过；否则，ORACLE 会在执行此SQL语句之前，自动进行ANALYZE 命令分析，这会极大导致SQL语句执行极其缓慢。 使用CBO 时，SQL语句中不能引用系统数据字典表或视图，因为系统数据字典表