ORACLE的CBO及表分析

最近接触到了表分析的几个案例，有一张表经过分析之后，在上面的sql语句的执行时间一下子从50分钟锐减到2分钟，不可思议。       

第一部分

什么是表分析？简单的说，就是收集表和索引的信息，CBO根据这些信息决定SQL最佳的执行路径。通过对表的分析，可以产生一些统计信息，通过这些信息oracle的优化程序可以优化。

        用analyze或dbms_stats包做统计表的的分析,分析可以判断表的使用情况,比如行迁移情况等.之前看看这个表的情况,主要是各列的数据.

       select * from dba_tables where table_name='TEST11';
       analyze table test11 compute statistics;
       再查询表dba_tables,你就会发现问题了.
       select * from dba_tables where table_name='TEST11';（备注：表名要大写）

        oracle的联机文档描述了analyze的做用：
Use the ANALYZE statement to collect non-optimizer statistics, for example, to:
    --使用analyze功能可以收集一些没有进行优化的统计信息，例如：
Collect or delete statistics about an index or index partition, table or table partition, 
    --聚集或删除索引或索引分区，表或表分区
index-organized table, cluster, or scalar object attribute.
    --索引表，串，或游标的目标属性等统计信息 
Validate the structure of an index or index partition, table or table partition, index-organized table, 
    --增加下列结构的有效性：索引或索引分区，表或表分区，索引表
cluster, or object reference (REF). 
    --串，目标参照
Identify migrated and chained rows of a table or cluster.
    --鉴别一个表或串中已经被移动的抑或被锁住的列。

dbms_stats的作用主要是替代analyze的收集统计信息这一块的功能，且在这一方面做了相当大程度上的增强。
以你的analyze table abc compute statistics;这条为例，生成的统计信息会存在于user_tables这个视图，查看一下
select * from user_tables where table_name='ABC';
   

        观察一下NUM_ROWS,BLOCKS,AVG_SPACE,AVG_ROW_LEN几列你就会明白，这就是变化。收集统计信息的目的是为了使基于CBO的执行计划更加准确。

        分析完表之后，你会发现DBA_tables 视图中，以前很多列值是空的，现在开始有数据了。
这些数据就是你分析表得到的。至于这些数据有什么用，要看你的初始化参数optimal_mode 怎么设置的，详细内容请查看相关文档！分析更新表的统计信息,,有可能导致执行计划改变.

第二部分

[Q]怎么样分析表或索引？
[A]命令行方式可以采用analyze命令
如Analyze table tablename compute statistics;
Analyze index|cluster indexname estimate statistics;
ANALYZE TABLE tablename COMPUTE STATISTICS
FOR TABLE
FOR ALL [LOCAL] INDEXES
FOR ALL [INDEXED] COLUMNS;
ANALYZE TABLE tablename DELETE STATISTICS
ANALYZE TABLE tablename VALIDATE REF UPDATE
ANALYZE TABLE tablename VALIDATE STRUCTURE 
[CASCADE]|[INTO TableName]
ANALYZE TABLE tablename LIST CHAINED ROWS [INTO TableName] 
等等。
如果想分析整个用户或数据库，还可以采用工具包，可以并行分析
Dbms_utility(8i以前的工具包)
Dbms_stats(8i以后提供的工具包)
如
dbms_stats.gather_schema_stats(User,estimate_percent=>100,cascade=> TRUE);
dbms_stats.gather_table_stats(User,TableName,degree => 4,cascade => true);

这是对命令与工具包的一些总结
1、对于分区表，建议使用DBMS_STATS，而不是使用Analyze语句。 
a) 可以并行进行，对多个用户，多个Table 
b) 可以得到整个分区表的数据和单个分区的数据。 
c) 可以在不同级别上Compute Statistics：单个分区，子分区，全表，所有分区 
d) 可以倒出统计信息 
e) 可以用户自动收集统计信息 
2、DBMS_STATS的缺点 
a) 不能Validate Structure 
b) 不能收集CHAINED ROWS, 不能收集CLUSTER TABLE的信息，这两个仍旧需要使用Analyze语句。 
c) DBMS_STATS 默认不对索引进行Analyze，因为默认Cascade是False，需要手工指定为True 
3、对于oracle 9里面的External Table，Analyze不能使用，只能使用DBMS_STATS来收集信息。

关于dbms_stats.gather_table_stats的问题？
我用dbms_stats.gather_table_stats分析完一张表，我的CBO现在通过它的分析结果来处理一张表，但是我现在不像使用这个分析结果了，我该如何做呢？
原因是，使用这个分析以后，我的很多查询效率突然大大降低了，而且cpu占用率猛增，我想还原成原来的，不然应用很危险啊
还请大伙帮帮忙~
analyze table table_name delete statistics;
将分析后的统计信息删除就是了。应该可以用analyze table tablename delete statistics;
很少用dbms_stats，所以竟没想到用DELETE_TABLE_STATS来Deletes system statistics。 最好是用这种方式。
SELECT TABLE_NAME, LAST_ANALYZED FROM DBA_TABLES WHERE LAST_ANALYZED IS NOT NULL;
检查是否还存在分析过的表

第三部分 CBO

Rule Based Optimizer（RBO）基于规则
Cost Based Optimizer（CBO）基于成本，或者讲统计信息

ORACLE 提供了CBO、RBO两种SQL优化器。CBO在ORACLE7 引入，但在ORACLE8i 中才成熟。
ORACLE 已经明确声明在ORACLE9i之后的版本中(ORACLE 10G )，RBO将不再支持。因此选择CBO 是必然的趋势。

CBO和 RBO作为不同的SQL优化器，对SQL语句的执行计划产生重大影响，如果要对现有的应用程序从RBO向CBO移植，
则必须充分考虑这些影响，避免SQL语句性能急剧下降；
但是，对新的应用系统，则可以考虑直接使用CBO，在CBO模式下进行SQL语句编写、分析执行计划、性能测试等工作，
这需要开发者对CBO的特性比较熟悉。以下小结几点在CBO下写SQL语句的注意事项：

1、RBO自ORACLE 6版以来被采用，有着一套严格的使用规则，只要你按照它去写SQL语句，无论数据表中的内容怎样，
也不会影响到你的“执行计划”，也就是说对数据不“敏感”；
CBO计算各种可能“执行计划”的“代价”，即cost，从中选用cost最低的方案，作为实际运行方案。
各“执行计划”的cost的计算根据，依赖于数据表中数据的统计分布，ORACLE数据库本身对该统计分布并不清楚，
必须要分析表和相关的索引（使用ANALYZE 命令），才能搜集到CBO所需的数据。

2、使用CBO 时，编写SQL语句时，不必考虑"FROM" 子句后面的表或视图的顺序和"WHERE" 子句后面的条件顺序；
ORACLE自7版以来采用的许多新技术都是基于CBO的，如星型连接排列查询，哈希连接查询，函数索引，和并行查询等。

3、一般而言，CBO所选择的“执行计划”都不会比RBO的“执行计划”差，而且相对而言，CBO对程序员的要求没有RBO那么苛刻，
节省了程序员为了从多个可能的“执行计划”中选择一个最优的方案而花费的调试时间，但在某些场合下也会存在问题。
较典型的问题有：有时，表明明建有索引，但查询过程显然没有用到相关的索引，导致查询过程耗时漫长，占用资源巨大，
这时就需要仔细分析执行计划，找出原因。例如，可以看连接顺序是否允许使用相关索引。
假设表emp的deptno列上有索引，表dept的列deptno上无索引，WHERE语句有emp.deptno=dept.deptno条件。
在做NL连接时，emp做为外表，先被访问，由于连接机制原因，外表的数据访问方式是全表扫描，
emp.deptno上的索引显然是用不上，最多在其上做索引全扫描或索引快速全扫描。

4、如果一个语句使用 RBO的执行计划确实比CBO 好，则可以通过加 " rule" 提示，强制使用RBO。

5、使用CBO 时，SQL语句 "FROM" 子句后面的表，必须全部使用ANALYZE 命令分析过，如果"FROM" 子句后面的是视图，
则此视图的基础表，也必须全部使用ANALYZE 命令分析过；否则，ORACLE 会在执行此SQL语句之前，自动进行ANALYZE 命令分析，
这会极大导致SQL语句执行极其缓慢。

6、使用CBO 时，SQL语句 "FROM" 子句后面的表的个数不宜太多，因为CBO在选择表连接顺序时，会对"FROM" 子句后面的表进行阶乘运算，
选择最好的一个连接顺序。假如"FROM" 子句后有6个表，则其可选择的连接顺序就是6*5*4*3*2*1 = 720 种，CBO 选择其中一种，
而如果"FROM" 子句后有12个表，则其可选择的连接顺序就是12*11*10*9*8*7*6*5*4*3*2*1= 479001600 种，
可以想象从中选择一种，会消耗多少CPU 时间？如果实在是要访问很多表，则最好使用 ORDER 提示，
强制使用"FROM" 子句表固定的访问顺序。

7、使用CBO 时，SQL语句中不能引用系统数据字典表或视图，因为系统数据字典表都未被分析过，可能导致极差的“执行计划”。
但是不要擅自对数据字典表做分析，否则可能导致死锁，或系统性能严重下降。

8、使用CBO 时，要注意看采用了哪种类型的表连接方式。ORACLE的共有Sort Merge Join（SMJ）、Hash Join（HJ）和
Nested Loop Join（NL）。CBO有时会偏重于SMJ 和 HJ，但在OLTP 系统中，NL 一般会更好，因为它高效的使用了索引。
在两张表连接，且内表的目标列上建有索引时，只有Nested Loop才能有效地利用到该索引。
SMJ即使相关列上建有索引，最多只能因索引的存在，避免数据排序过程。HJ由于须做HASH运算，
索引的存在对数据查询速度几乎没有影响。

9、使用CBO 时，必须保证为表和相关的索引搜集足够的统计数据。
对数据经常有增、删、改的表最好定期对表和索引进行分析，
可用SQL语句“analyze table xxx compute statistics for all indexes;"ORACLE掌握了充分反映实际的统计数据，
才有可能做出正确的选择。

10、使用CBO 时，要注意被索引的字段的值的数据分布，会影响SQL语句的执行计划。
例如：表emp，共有一百万行数据，但其中的emp.deptno列，数据只有4种不同的值，如10、20、30、40。
虽然emp数据行有很多，ORACLE缺省认定表中列的值是在所有数据行均匀分布的，也就是说每种deptno值各有25万数据行与之对应。
假设SQL搜索条件DEPTNO=10，利用deptno列上的索引进行数据搜索效率，往往不比全表扫描的高，
ORACLE理所当然对索引“视而不见”，认为该索引的选择性不高。
我们考虑另一种情况，如果一百万数据行实际不是在4种deptno值间平均分配，其中有99万行对应着值10，
5000行对应值20，3000行对应值30，2000行对应值40。
在这种数据分布图案中对除值为10外的其它deptno值搜索时，毫无疑问，如果索引能被应用，那么效率会高出很多。
我们可以采用对该索引列进行单独分析，或用analyze语句对该列建立直方图，对该列搜集足够的统计数据，
使ORACLE在搜索选择性较高的值能用上索引。

－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－
基于成本的优化器（CBO） 
1、Background 背景

　　为了执行任何一个SQL语句，Oracle都要先导出一个“执行计划(execution plan)”。
它基本上就是Oracle如何检索出符合给定SQL语句要求的数据的执行计划。
　　Oracle7和Oracle8 都有两种可以为SQL语句导出执行计划的优化器: 
　　
    - 基于规则的优化器（RBO） 
　　继承自Oracle6，它使用一系列严格的规则来决定每个SQL语句的执行计划。如果你知道这些规则，
你可以构造一个SQL查询使其以指定的方式访问数据。表的内容对于执行计划没有影响。 
　　这个优化器已经不再被增强了，所以不能使用很多oracle8的特性。 
　　
    - 基于成本的优化器（CBO） 
　　从Oracle7开始引入，该优化器尝试找到最低成本的访问数据的方法，为了最大的吞吐量或最快的初始响应时间。
计算使用不同的执行计划的成本，并选择成本最低的一个。关于表的数据内容的统计被用于确定执行计划。 
　　Fundamental Points 基本点 
　　对于每个SQL语句，都有很多可能的执行计划。“最佳计划”永远是“最佳计划”，无论它如何到达。 
　　最佳计划可以由两个意思： 
　　1 此计划使用最小的资源来处理此语句涉及到的所有行。 [叫做ALL_ROWS] 
　　2 此计划以最短的时间返回这个语句的第一行 。 [叫做FIRST_ROWS] 
　　CBO不理解应用的相关特性，也不能完全理解关联表之间的复杂关系的影响。仅有有限的信息可以用来确定最佳计划。 
　　CBO通过计算不同执行方案的估计成本来确定最佳计划，并选用最低成本的计划。因为这个关系到相关成本的假设，
所选的计划不一定是真的最好的计划。这种情况经常被当作BUG报告给oracle 技术支持,因为 CBO没有为一个指定方案选择一个最佳的计划。
人们通常可以证实因为给定的输入统计试有效的并且缺省的“成本”被牵扯进来。所选中的计划被计算成最佳计划，虽然它不是。
无论CBO如何改进提高，总也会有所选的计划不是最优的这种情况。所以，你必须经常地准备优化语句。

    RBO的功能已经不再增强。这就意味着一些执行计划只对CBO有效。然而，RBO还将在Oracle 8中继续存在。

　　Before you Continue 在你继续之前 
　　~~~~~~~~~~~~~~~~~~~ 
　　不建议你在Oracle releases 7.0.X中使用CBO. 
　　本文中的信息适用于Oracle releases 7.1 以上（包括Oracle 8.0）。 
　　Base Statistics 基础统计 
　　~~~~~~~~~~~~~~~

　　为了要给CBO最多的信息（有机会选择好的执行计划），你必须对所有将被查询的表做ANALYZE。 
　　带有ESTIMATE选项的ANALYZE操作对于一些表能够产生不正确的结果，尤其是那些取样较小的表。
这不是个BUG,而是每个统计取样方法的特性。如果所选取样不能代表整个数据集，你就不能期待产生正确的统计。 
　　在Oracle 7.1 和7.2 中，列的值被假定为是均匀分布的。这是在这些版本中的一个重要的限制，完全和精确的统计也不能指出
实际数据的分布情况。这一限制在Oracle release 7.3 以上版本被部分解决了，能够保存列值的分布信息 
- 但是这些额外的信息可能对某些类型的查询没有实际的帮助，请看后面的章节中关于Bind Variables 的注意事项。

　　在考虑使用ANALYZE时，要考虑如下的重要问题： 
　　- 对一个带索引的表的ANALYZE，将分析其相关索引。 （在Oracle 7.3 中可能值分析表而不分析索引。） 
　　- 如果你对一个表进行ANALYZE ... ESTIMATE 分析，那么然后在其相关索引上做ANALYZE COMPUTE分析是很明智的。
这样可以确保被索引字段的统计是准确的。 
　　- 分析索引不用到临时表空间 
　　- 如果分析一个索引而不分析其基表，在这一单一基础上CBO不会被选中。 
　　- 如果你需要使用ESTIMATE- 估计（例如，由于时间的限制），建议你在几个不同的取样大小上进行 ANALYZE ... ESTIMATE， 
来确定每个对象的理想的取样大小。总的目标是找到一个能在最短的时间内产生准确的统计的取样大小。较好的开始点是 10% - 15%。 
　　- 进行超过50%的ANALYZE ... ESTIMATE 就会导致/变成ANALYZE ... COMPUTE。 
　　- 7.1.6 以前的版本在进行ANALYZE ... ESTIMATE 时，会有ORA 600 错。 
　　Optimizer Goal / Mode 优化目标和模式

    采用什么样的优化器和其操作方式是由下面的因素决定的： 
　　Object Type 对象类型 
　　- 某些对象类型是基于规则的优化所不知道的。例如：索引表（IOT）RBO根本不认识，在牵扯IOT的查询中将自动使用CBO. 
　　Parallel Degree > 1 on a table 表上的并行度大于1 
　　- 如果查询中的某个表的并行度大于一，CBO都将被采用而不管提示、OPTIMIZER_MODE或OPTIMIZER_GOAL的值是否为"RULE"。
适用于Oracle 7.3 以上。 
　　- 在Oracle 8.0.5 和 Oracle 8.1.5 releases 中如果任何索引的并行度超过1，也将采用CBO。仅适用于Oracle 8.0.5和Oracle 8.1.5。 
　　Hints 提示 
　　- 除了RULE之外的任何提示都会导致使用CBO。HINT不能被任何参数关掉，这一点非常重要。 
　　Session level会话级 OPTIMIZER_GOAL 
　　- 如果没有给定以上的条件，优化器的选用由会话级的参数OPTIMIZER_GOAL决定。如果上面的一个条件给定了，OPTIMIZER_GOAL就不起作用了。 
　　如果OPTIMIZER_GOAL设为RULE，将采用RBO，而不管任何表的统计。 
　　如果OPTIMIZER_GOAL设为CHOOSE，对于只要有一个表被分析过的查询，都将选用ALL_ROWS 。 
　　Init.Ora OPTIMIZER_MODE 参数 
　　- 会话级的OPTIMIZER_GOAL参数的缺省设置是init.ora文件中的OPTIMIZER_MODE的值。 
　　PL/SQL 块（包括匿名块和存储过程）应使用显式的提示(hint)来确定实际的优化方法。没有指定提示、并行的或“CBO-only”的对象的情况下，
PL/SQL 块中的SQL语句采用的优化器，见下： 
　　INIT.ORA OPTIMIZER_MODE 
　　Mode used in PLSQL 
　　RULE 
　　RULE 
　　CHOOSE 
　　ALL_ROWS 
　　ALL_ROWS 
　　ALL_ROWS 
　　FIRST_ROWS 
　　ALL_ROWS 
　　Summary Optimizer Mode: 优化模式的总结 
　　~~~~~~~~~~~~~~~~~~~~~~~ 
　　对于以上的文章使我们清楚的确定采用何种优化器的一些事情，总结如下： 
　　Description 
　　Table Statistics 
　　Mode Used 
Non-RBO Object(Eg:IOT) 
　　n/a 
　　#1 
　　Parallelism > 1 
　　n/a 
　　#1 
　　RULE hint 
　　n/a 
　　RULE 
　　ALL_ROWS hint 
　　n/a 
　　ALL_ROWS 
　　FIRST_ROWS hint 
　　n/a 
　　FIRST_ROWS 
　　*Other Hint 
　　n/a 
　　#1 
　　OPTIMIZER_GOAL=RULE 
　　n/a 
　　RULE 
　　OPTIMIZER_GOAL=ALL_ROWS 
　　n/a 
　　ALL_ROWS 
　　OPTIMIZER_GOAL=FIRST_ROWS 
　　n/a 
　　FIRST_ROWS 
　　OPTIMIZER_GOAL=CHOOSE 
　　NO 
　　RULE 
　　OPTIMIZER_GOAL=CHOOSE 
　　YES 
　　ALL_ROWS 
　　#1 除非OPTIMIZER_GOAL 设置为FIRST_ROWS,都将采用ALL_ROWS 。在PLSQL中，将采用ALL_ROWS。 
　　*Other Hint 其他提示的意思是指除了RULE, ALL_ROWS 或FIRST_ROWS之外的提示。 
　　General Optimizer Notes 优化器的一般注意事项 
　　~~~~~~~~~~~~~~~~~~~~~~ 
　　在看待优化器问题的时候，应考虑如下几点： 
　　- ALL_ROWS 倾向于全表扫描（full table scans）。 
　　- FIRST_ROWS 倾向于索引访问（ index access）。 
　　- CBO缺省使用ALL_ROWS计算成本。 
　　- 在Oracle 7.3之前，CBO不会为了迎合并行查询（ Parallel Queries）而调整成本。 
　　- 在Oracle 7.3之前，CBO认为字段的值载最大和最小之间是均匀分布的；这之后，可以根据请求存储柱状图统计。 
　　- 所有等于RBO的情况，以表在FROM子句中从右到左的顺序为驱动顺序(Driving Order)。 
　　CBO根据由收集到的统计信息而导出的成本，来确定连接顺序(Join Order). 
　　如果没有统计信息，CBO就将以表在FROM子句中从左到右的顺序为驱动顺序(Driving Order),正好和RBO相反。 
　　- CBO将结合当前表的高水位信息使用ANALYZE信息。因此，一个语句的执行计划是可能因时间的不同而改变的。 
    - 注意：TRUNCATE重置了表的“高水位”，但是不修改表的统计信息，而是留下了该表的旧的CBO信息。 
　　- 当执行各种连接时，一些连接组合将被排除以降低确定一个执行计划所需要的整体时间花费。
总之，每个连接顺序都要和目前为止最好的一个做比较，显然部分优化的方案将被排除。 
　　Problem SQL Statements 问题SQL语句