ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 聊聊CBO的连接排列(Join Permutation)

聊聊CBO的连接排列(Join Permutation)

原创 Linux操作系统 作者:realkid4 时间:2013-08-18 19:29:56 0 删除 编辑

Oracle CBOCost Based Optimizer)是目前Oracle采用的主流优化器种类。

 

简单的说,SQL语句的执行计划,主要是通过数据库SQL优化器根据统计信息(System Statistical DataObject 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是先将BonusEmp连接(Hash Join),之后将结果集合和PK_DEPT数据集合进行连接(Nested Loop)。Oracle的连接,无论数据表连接数量是多少、Type是什么,Oracle都是老老实实的两两连接。

 

那么,我们回到两个问题:n个数据表,Oracle怎么知道连接顺序是最合适的?CBO在进行判断的时候采用什么样的准则?这就是OracleJoin Permutation机制。

 

2Join 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中,可以看到OraclePermutation隐含参数。

 

 

_optimizer_max_permutations         = 2000

 

 

存在Permutation,让我们可以存在一个连接表数量n的对应关系。Permutation计算的覆盖比例公式:PERM/n!*100

 

n

n!全路径数量

Permutation80000

Permutation2000

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来指导连接顺序。我们如果发现执行计划的不稳定问题,可以通过orderhint来控制数据表的连接。当然,这样的情况需要对执行计划有一个清晰的分析理解;

ü  调整隐含参数_optimizer_max_permutations,如果系统整体连接比较多,可以尝试调整Permutation试探计算的参数;

 

下面是笔者几个拙见:

 

ü  合理化范式设计。我们说:连接JoinSQL性能的一个危险点。Join过多,SQL过于复杂是我们SQL出现问题的一个潜在问题点。如果从根本上看,减少Join次数是避免问题的一个好方法。三范式为基础,适度冗余是一个比较好的方法。连接过多、数据量过大很多时候也没有太多好方法;

ü  避免From后面直接所有的数据表列。虽然使用From后面把数据表都列出来是一个比较容易书写的方法,也带来问题是Order顺序的问题。笔者一系列的经验是使用Left Join等方法一层层的添加连接。这样的写法可以“诱使”Oracle形成更好的执行计划;

 

5、结论

 

CBO的连接排列是优化器的一种重要功能。从Permutation次数变化情况看,Oracle也在不断的调整这个数据值。相信这种是一种性能的折中。

 

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/17203031/viewspace-768748/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
求道~

注册时间:2010-11-30

  • 博文量
    545
  • 访问量
    7638966