Oracle CBO（Cost Based Optimizer）是目前Oracle采用的主流优化器种类。
简单的说，SQL语句的执行计划，主要是通过数据库SQL优化器根据统计信息（System Statistical Data和Object Statistical Data），使用预定义公式进行计算，将最后的计算结果作为执行计划路径的成本值Cost。
一个SQL语句，理论上有很多种执行计划可以选择（所谓“条条大路通罗马”），优化器Optimizer最终会选择使用哪个执行计划一定是依据各条路径的计算成本Cost值。注意：Optimizer依据的是计算出的成本值，而不是真实的执行成本。所以，当统计量不能及时反映数据真实情况的时候，SQL执行计划往往就会是低效的。
1、从连接Join顺序说起
关系型数据库的两个基本要素就是数据表和连接。我们将数据依据范式原则拆成若干的数据表。数据信息表达和组织都是通过表间连接完成。所以，Join是关系型数据库的一个重要概念。
在很多的SQL优化和数据库优化过程中，Join的优化占到了很大比例。不同的Join方法、不同的Join顺序，对相同SQL语句执行结果过程有很大的影响。
一个SQL语句中，可能会有很多表的连接Join。各种连接Type种类，如内连接、外连接和全连接，都是将若干表连接在一起。但是在执行计划和实际执行过程中，都是两两数据集合的连接。下面是一个例子中看出：
SQL> select * from v$version;
BANNER
--------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 – Production
三个数据表连接的SQL语句。
SQL> explain plan for select * from scott.bonus a, scott.dept b, scott.emp c where a.ename=c.ename and b.deptno=c.deptno;
Explained
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------
Plan hash value: 1315453310
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Ti
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 97 | 7 (15)| 00
| 1 | NESTED LOOPS | | | | |
| 2 | NESTED LOOPS | | 1 | 97 | 7 (15)| 00
|* 3 | HASH JOIN | | 1 | 77 | 6 (17)| 00
| 4 | TABLE ACCESS FULL | BONUS | 1 | 39 | 2 (0)| 00
| 5 | TABLE ACCESS FULL | EMP | 14 | 532 | 3 (0)| 00
|* 6 | INDEX UNIQUE SCAN | PK_DEPT | 1 | | 0 (0)| 00
| 7 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 20 | 1 (0)| 00
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("A"."ENAME"="C"."ENAME")
6 - access("B"."DEPTNO"="C"."DEPTNO")
20 rows selected
我们的SQL语句，是直接的三个数据表连接。而在执行过程中，我们看到Oracle是先将Bonus与Emp连接（Hash Join），之后将结果集合和PK_DEPT数据集合进行连接（Nested Loop）。Oracle的连接，无论数据表连接数量是多少、Type是什么，Oracle都是老老实实的两两连接。
那么，我们回到两个问题：n个数据表，Oracle怎么知道连接顺序是最合适的？CBO在进行判断的时候采用什么样的准则？这就是Oracle的Join Permutation机制。
2、Join Permutation
Join顺序判断，对于RBO和过去的时候，是比较简单的。我们依然记得有“按照from之后从右到左”这样的经验传言。但是在CBO的时候，这个过程是很复杂的。
如果我们希望对n个数据表进行连接，那么Oracle CBO面对的试算规模理论上是n!条路径。这个量级是非常大的。如果一个SQL需要连接数据表的数量很多，那么生成执行计划的过程也是消耗时间不可控。这个是任何Oracle CBO无法容忍的。
事实上，实际中发生全局路径解析的情况是很少的，并不是所有的n!条路径都是回去计算。Oracle解决这个问题是通过两个手段：
ü 路径成本非完全计算：Oracle CBO在计算路径成本值的时候，一般都有一定的倾向性。也就是说，在确定检索路径的时候，就将可能的最优计划在前面进行估算。当进行后续路径的检索时，如果计算的过程中发现，成本已经高于之前路径的成本值，这条路径就不会计算下去，而是直接放弃；
ü 最大试算Permutation阈值：Oracle并不是直接会将所有的n！全部进行测试。而是系统级别存在一个计算阈值，如果计算次数超过阈值，试算过程终值。采用当前最优执行计划（连接顺序）作为返回的结果。
在很多资料中，我们可以发现对Permutation的计算阈值为80000，但是我们从11gR2版本中，看到这个数字值是2000。
注意：有Permutation的存在，会造成CBO工作的一个情况，就是Oracle CBO返回的连接顺序可能不是最优的。进而，执行计划也不是最优的，是一个相对最优的执行计划。这应该是Oracle进行的一种折中操作。
3、测算过程
我们在11gR2中，可以看到Oracle的Permutation隐含参数。
_optimizer_max_permutations = 2000
存在Permutation，让我们可以存在一个连接表数量n的对应关系。Permutation计算的覆盖比例公式：PERM/n!*100
| n | n！全路径数量 | 当Permutation为80000 | 当Permutation为2000 |
| 1 | 1 | 8000000 | 200000 |
| 2 | 2 | 4000000 | 100000 |
| 3 | 6 | 1333333.333 | 33333.33333 |
| 4 | 24 | 333333.3333 | 8333.333333 |
| 5 | 120 | 66666.66667 | 1666.666667 |
| 6 | 720 | 11111.11111 | 277.7777778 |
| 7 | 5040 | 1587.301587 | 39.68253968 |
| 8 | 40320 | 198.4126984 | 4.96031746 |
| 9 | 362880 | 22.04585538 | 0.551146384 |
| 10 | 3628800 | 2.204585538 | 0.055114638 |
| 11 | 39916800 | 0.200416867 | 0.005010422 |
| 12 | 479001600 | 0.016701406 | 0.000417535 |
| 13 | 6227020800 | 0.001284724 | 3.21181E-05 |
从计算结果看，如果阈值次数是80000，那么在数据表数量大于8之后，查询路径是不完全的。在数据表为9个之后，覆盖范围为22%。如果阈值次数为2000，那么查询的完全数据表为6。超过6个数据表之后，覆盖率降低。
4、应对方法
那么，我们应该怎么样进行处理，包括Oracle官方的推荐建议，有下面几个建议：
ü 使用Hint来指导连接顺序。我们如果发现执行计划的不稳定问题，可以通过order的hint来控制数据表的连接。当然，这样的情况需要对执行计划有一个清晰的分析理解；
ü 调整隐含参数_optimizer_max_permutations，如果系统整体连接比较多，可以尝试调整Permutation试探计算的参数；
下面是笔者几个拙见：
ü 合理化范式设计。我们说：连接Join是SQL性能的一个危险点。Join过多，SQL过于复杂是我们SQL出现问题的一个潜在问题点。如果从根本上看，减少Join次数是避免问题的一个好方法。三范式为基础，适度冗余是一个比较好的方法。连接过多、数据量过大很多时候也没有太多好方法；
ü 避免From后面直接所有的数据表列。虽然使用From后面把数据表都列出来是一个比较容易书写的方法，也带来问题是Order顺序的问题。笔者一系列的经验是使用Left Join等方法一层层的添加连接。这样的写法可以“诱使”Oracle形成更好的执行计划；
5、结论
CBO的连接排列是优化器的一种重要功能。从Permutation次数变化情况看，Oracle也在不断的调整这个数据值。相信这种是一种性能的折中。
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/17203031/viewspace-768748/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/17203031/viewspace-768748/
1760
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
