SQL 调整 2. 理解ORACLE优化器

· 优化器的职责是从多行种执行路径中选择一种最优的执行路径；
· 有两种优化模式：RBO（基于规则），CBO（基于成本）；
· RBO根椐一系列规则来确定执行计划，不考虑表的大小，栏位的集势等统计数据，主要用于早期的版本或者新版本的递归SQL；
· CBO会考查表或索引的统计，然后比较不同执行计划的IO成本，CPU成本，临时表空间的需求，得出一个综合成本最小的执行计划；
· CBO考查的统计包括：表或索引的大小，表或索引的行数，表或索引的数据块数，表行的长度，索引栏位的集势等；
· 默认情况下，字典里并不包含表或索引的统计，这些数据是在分析表，索引，方案或整个数据库的时候写进字典里的。
· 分析可以使用两个方式：COMPUTE STATISTICS（整体分析）， ESTIMATE STATISTICS（样本分析，可以用SAMPLE子句指定样本行数或者比例）；
· 可以分析栏位的柱状图以指示优化器栏位数据的离散分布状况，ANALYZE TABLE table_name COMPUTE STATISTICS FOR COLUMNS column_name SIZE integer_value(1-254)，默认分析选项下只保存栏位的最大最小值，优化器假设栏位值是均匀分布的，在某些不均衡的情况下，优估器可能产生效率极低的执行计划，因此分析柱状图显得相当重要；
· 可以用包程序来分析整个方案或者整个数据库，DBMS_UTILITY， DBMS_STATS；
· 在ORACLE9中推荐使用DBMS_STATS，它有以下新特性：

v 可以在分析前备份现有的统计，用于当更新统计后CBO性能反而下降的情形下恢复以前的统计；
v 样本分析时随机取数据块，而不只是数据行；
v 可以在并行模式下收集统计；
v DBMS_STATS.GATHER_SCHEMA_STATS可以定期自动收集高变更的表的统计，也可以用来自动收集柱状图的统计并自动决定切片数以及哪些栏位需要柱状图统计；
v DBMS_STATS.GATHER_SYSTEM_STATS可以用来收集系统的CPU和IO负载统计，为CBO决策提供参考，避免系统产生CPU或IO瓶颈；
v 可以用于将生产环境的统计转移到开发环境，这一点对于从开发环境调优生产环境相当重要。
· 统计结果存放在下面一些字典里面：DBA_TABLES， DBA_INDEXES， DBA_TAB_COL_STATISTICS， DBA_HISTOGRAMS等。

设置优化器模式

· 可以在下面三种级别配置优化器模式：实例级，会话级，语句级，优先级从低到高；
· 设定优化器行为版本差异的9i新参数：OPTIMIZER_FEATURES_ENABLE；
· OPTIMIZER_MODE初始化参数确定实例中所有会话默认的优化模式，可选值有RULE， CHOOSE（默认值）， FIRST_ROWS， FIRST_ROWS_n， ALL_ROWS（与CHOOSE似乎没有区别？）；
· 会话级的优化模式设置用ALTER SESSION SET OPTIMIZER_MODE = mode，可选项同上;
· 在SQL语句中内嵌提示/*+ mode */ 可用来指示当前语句的优化模式，mode选项可用上面提到的除CHOOSE外的其它四种，另外还有四十多种提示可用；
· 在RULE模式下，如果SQL涉及到下面的特性时还是会用CBO：分区表和分区索引，索引组织表，反向索引，基于函数的索引，位图索引，查询重写，物化视图；
· 在非RULE的其它四种模式下，如果涉及到的所有的表或者索引的统计都不存在，使用RBO模式，其它情况下使用CBO；
· FIRST_ROWS， FIRST_ROWS_n 优化响应时间， ALL_ROWS优化吞吐量；