查询计划中的笛卡尔积

日期： 2009-02-07 14:53:41

分享到

今天遇到一个性能问题，经过定位，最终确定问题的根本原因是一个查询中存在笛卡尔乘积（Cartesian）。在这里，我们先讲一下什么叫笛卡尔乘积以及查询计划中笛卡尔乘积的来由。

首先，我们要知道在查询计划中产生笛卡尔乘积的一个先决条件：笛卡尔乘积只出现在CBO中。

1、没有join条件导致笛卡尔乘积

学过线性代数的人都知道，笛卡尔乘积通俗的说，就是两个集合中的每一个成员，都与对方集合中的任意一个成员有关联。可以想象，在SQL查询中，如果对两张表join查询而没有join条件时，就会产生笛卡尔乘积。这就是我们的笛卡尔乘积导致的性能问题中最常见的案例：开发人员在写代码时遗漏了join条件。

SQL> create table t_test1 as select * from all_objects;

Table created.

SQL> create table t_test2 as select * from all_tables;

Table created.

SQL> set autot trace

SQL> select count(1) from t_test1, t_test2;

Execution Plan

----------------------------------------------------------

   0      SELECT STATEMENT Optimizer=CHOOSE

   1    0   SORT (AGGREGATE)

   2    1     NESTED LOOPS

   3    2       TABLE ACCESS (FULL) OF 'T_TEST2'

   4    2       TABLE ACCESS (FULL) OF 'T_TEST1'

SQL> analyze table t_test1 compute statistics;

Table analyzed.

SQL> analyze table t_test2 compute statistics;

Table analyzed.

SQL>

SQL> select count(1) from t_test1, t_test2;

Execution Plan

----------------------------------------------------------

   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=4169 Card=1)

   1    0   SORT (AGGREGATE)

   2    1     MERGE JOIN (CARTESIAN) (Cost=4169 Card=2585632)

   3    2       TABLE ACCESS (FULL) OF 'T_TEST2' (Cost=4 Card=833)

   4    2       BUFFER (SORT) (Cost=4165 Card=3104)

   5    4         TABLE ACCESS (FULL) OF 'T_TEST1' (Cost=5 Card=3104)

从以上的例子中我们可以看到，只有在CBO中才会产生笛卡尔乘积。

2、优化器代价计算结果，采用笛卡尔积最优

一种情形就是：2个小的结果集分别与一个大的结果集join时，如果代价计算结果中小的结果集的cardinality值都很小（1），则会先让小结果集做笛卡尔积后再和大结果集join。实际上，如果统计数据正确，这种结果是优化器根据代价估算得出的正确结论：2个小结果集本身做笛卡尔积后再和大结果集循环比较所付出的代价是低于大结果集分别和两个结果集做循环的代价更低。

SQL> create table t_test4 as select * from all_users;

Table created.

SQL> create index t_test4_idx1 on t_test4(username) compute statistics;

Index created.

SQL> create index t_test4_idx2 on t_test4(created) compute statistics;

Index created.

SQL> analyze table t_test4 compute statistics for table for all columns for all indexes;

Table analyzed.

SQL> create table t_test5 as select * from all_users where username='SYS';

Table created.

SQL> analyze table t_test2 compute statistics;

Table analyzed.

SQL> set autot trace

SQL> select a.*

  2  from t_test1 a, t_test4 b, t_test5 c

  3  where a.owner = b.username

  4  and a.owner = c.username;

13144 rows selected.

Execution Plan

----------------------------------------------------------

   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=50 Card=1917 Bytes=1

          87866)

   1    0   HASH JOIN (Cost=50 Card=1917 Bytes=187866)

   2    1     MERGE JOIN (CARTESIAN) (Cost=4 Card=45 Bytes=540)

   3    2       TABLE ACCESS (FULL) OF 'T_TEST5' (Cost=2 Card=1 Bytes=        3)

   4    2       BUFFER (SORT) (Cost=2 Card=45 Bytes=405)

   5    4         INDEX (FULL SCAN) OF 'T_TEST4_IDX1' (NON-UNIQUE) (Cost=1 Card=45 Bytes=405)

   6    1     TABLE ACCESS (FULL) OF 'T_TEST1' (Cost=42 Card=30670 Byt

          es=2637620)

SQL> select a.*

  2  from t_test1 a, t_test4 b, t_test5 c

  3  where a.owner = b.username

  4  and a.owner = c.username

  5  and b.created = sysdate;

no rows selected

Execution Plan

----------------------------------------------------------

   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=50 Card=60 Bytes=6300)

   1    0   HASH JOIN (Cost=50 Card=60 Bytes=6300)

   2    1     MERGE JOIN (CARTESIAN) (Cost=4 Card=1 Bytes=19)

   3    2       TABLE ACCESS (BY INDEX ROWID) OF 'T_TEST4' (Cost=2 Card=1 Bytes=16)

   4    3         INDEX (RANGE SCAN) OF 'T_TEST4_IDX2' (NON-UNIQUE) (Cost=1 Card=1)

   5    2       BUFFER (SORT) (Cost=2 Card=1 Bytes=3)

   6    5         TABLE ACCESS (FULL) OF 'T_TEST5' (Cost=2 Card=1 Bytes=3)

   7    1     TABLE ACCESS (FULL) OF 'T_TEST1' (Cost=42 Card=30670 Bytes=2637620)

在有join条件的情况下，由优化器计算出使用cartesian merge join的查询计划不止这一种情形。对于这样的情况，如果统计数据正确，那么这样的查询计划效率也是已优化的，如果语句本身的性能可以接受，那么这样的查询计划也是可以接受的，无需再优化。

3、10gR2的新特性——条件传递会使有join条件的查询产生笛卡尔积

10gR2中，可以设置在多表join查询时，查询条件通过join条件进行传递。这是什么意思呢？举例说明，看以下查询语句：

SQL> select count(*)

  2  from t_test1 a, t_test2 b

  3  where a.owner = 'OUTLN'

  4  and b.owner = a.owner;

从逻辑上分析，b.owner = a.owner = 'OUTLN' => b.owner = 'OUTLN'，通过条件传递，以上语句就成了：

SQL> select count(*)

  2  from t_test1 a, t_test2 b

  3  where a.owner = 'OUTLN'

  4  and b.owner = 'OUTLN';

根据笛卡尔乘积产生的条件，上述语句也就会产生笛卡尔乘积了。

10gR2中是否进行条件传递，是通过隐含参数_optimizer_transitivity_retain来控制的。默认为true，即不进行传递。看以下示例：

SQL> create table t_test1 as select * from all_objects;

Table created.

SQL> create table t_test2 as select * from all_tables;

Table created.

SQL> create index test1_idx1 on t_test1(owner) compute statistics;

Index created.

SQL> create index test2_idx1 on t_test2(owner) compute statistics;

Index created.

SQL> analyze table t_test1 compute statistics;

Table analyzed.

SQL> analyze table t_test2 compute statistics;

Table analyzed.

SQL> show parameter "_optimizer_transitivity_retain"

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

_optimizer_transitivity_retain       boolean     TRUE

SQL> select count(*)

  2  from t_test1 a, t_test2 b

  3  where a.owner = 'OUTLN'

  4  and b.owner = a.owner;

Execution Plan

----------------------------------------------------------

Plan hash value: 3966990923

-------------------------------------------------------------------------------

| Id  | Operation           | Name       | Rows  | Bytes | Cost (%CPU)| Time |

---------------------------------------------------------------------------------

|   0 | SELECT STATEMENT    |            |     1 |    10 |    23  (14)| 00:00:01|

|   1 |  SORT AGGREGATE     |            |     1 |    10 |            | |

|*  2 |   HASH JOIN         |            |   213K|  2083K|    23  (14)| 00:00:01|

|*  3 |    TABLE ACCESS FULL| T_TEST2    |    99 |   495 |    17   (0)| 00:00:01|

|*  4 |    INDEX RANGE SCAN | TEST1_IDX1 |  2163 | 10815 |     3   (0)| 00:00:01|

--------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   2 - access("B"."OWNER"="A"."OWNER")

   3 - filter("B"."OWNER"='OUTLN')

   4 - access("A"."OWNER"='OUTLN')

SQL> alter session set "_optimizer_transitivity_retain" = false;

Session altered.

SQL> select count(*)

  2  from t_test1 a, t_test2 b

  3  where a.owner = 'OUTLN'

  4  and b.owner = a.owner;

Execution Plan

----------------------------------------------------------

Plan hash value: 3047999809

------------------------------------------------------------------------------

| Id  | Operation             | Name     | Rows  | Bytes | Cost (%CPU)| Time  |

--------------------------------------------------------------------------------

|   0 | SELECT STATEMENT      |          |     1 |    10 |   317   (1)| 00:00:04|

|   1 |  SORT AGGREGATE       |          |     1 |    10 |            |   |

|   2 |   MERGE JOIN CARTESIAN|          |   213K|  2083K|   317   (1)| 00:00:04|

|*  3 |    TABLE ACCESS FULL  |T_TEST2   |    99 |   495 |    17   (0)| 00:00:01|

|   4 |    BUFFER SORT        |          |  2163 | 10815 |   300   (1)| 00:00:04|

|*  5 |     INDEX RANGE SCAN  |TEST1_IDX1|  2163 | 10815 |     3   (0)| 00:00:01|

---------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   3 - filter("B"."OWNER"='OUTLN')

   5 - access("A"."OWNER"='OUTLN')

4、强制禁止笛卡尔积

如果强制不允许merge join Cartesian出现，可以通过设置隐含参数“_optimizer_mjc_enabled”（10.1.0版本以后），

SQL> alter session set "_optimizer_mjc_enabled" = false;

Session altered.

SQL> select a.*

  2  from t_test1 a, t_test4 b, t_test5 c

  3  where a.owner = b.username

  4  and a.owner = c.username

  5  and b.created = sysdate;

no rows selected

SQL> select a.*

  2  from t_test1 a, t_test4 b;

1475042 rows selected.

---------------------------------------------------------------------------------

| Id  | Operation          | Name        | Rows  | Bytes | Cost (%CPU)| Time  |

---------------------------------------------------------------------------------

|   0 | SELECT STATEMENT   |             |  1475K|   120M|  4637   (2)| 00:00:56|

|   1 |  NESTED LOOPS      |             |  1475K|   120M|  4637   (2)| 00:00:56|

|   2 |   INDEX FULL SCAN  | T_TEST4_IDX1|    31 |       |     1   (0)| 00:00:01|

|   3 |   TABLE ACCESS FULL| T_TEST1     | 47582 |  3996K|   150   (2)| 00:00:02|

-------------------------------------------------------------------------------

而通过设置隐含参数“_optimizer_cartesian_enabled”（10.2.0版本以后），可以告诉优化器在做优化选择时，是否考虑cartesian。如果该参数设置为false，优化器不会考虑cartesian merge join这条路径。但在无join条件时，优化器是无需计算就知道要采用cartesian merge join。因此，在我们前面提到的第二种情况下，不会出现cartesian merge join，而第一种情况还是会出现cartesian merge join。而前面的参数如果设置为false，在任何情况下都不会出现cartesian。

SQL> alter session set "_optimizer_mjc_enabled" = true;

Session altered.

SQL> select a.*

  2  from t_test1 a, t_test4 b, t_test5 c

  3  where a.owner = b.username

  4  and a.owner = c.username

  5  and b.created = sysdate;

no rows selected

---------------------------------------------------------------------------------

| Id  | Operation                       | Name         | Rows  | Bytes | Cost (%

CPU)| Time     |

---------------------------------------------------------------------------------

|   0 | SELECT STATEMENT                |              |    49 |  4998 |     8

 (0)| 00:00:01 |

|   1 |  TABLE ACCESS BY INDEX ROWID    | T_TEST1      |    98 |  8428 |     4

 (0)| 00:00:01 |

|   2 |   NESTED LOOPS                  |              |    49 |  4998 |     8

 (0)| 00:00:01 |

|   3 |    MERGE JOIN CARTESIAN         |              |     1 |    16 |     4

 (0)| 00:00:01 |

|   4 |     TABLE ACCESS FULL           | T_TEST5      |     1 |     3 |     3

 (0)| 00:00:01 |

|   5 |     BUFFER SORT                 |              |     1 |    13 |     1

 (0)| 00:00:01 |

|   6 |      TABLE ACCESS BY INDEX ROWID| T_TEST4      |     1 |    13 |     1

 (0)| 00:00:01 |

|*  7 |       INDEX RANGE SCAN          | T_TEST4_IDX2 |     1 |       |     1

 (0)| 00:00:01 |

|*  8 |    INDEX RANGE SCAN             | T_TEST1_IDX1 |    98 |       |     2

 (0)| 00:00:01 |

--------------------------------------------------------------------------------

SQL> alter session set "_optimizer_cartesian_enabled" = false;

Session altered.

SQL> select a.*

  2  from t_test1 a, t_test4 b, t_test5 c

  3  where a.owner = b.username

  4  and a.owner = c.username

  5  and b.created = sysdate;

no rows selected

---------------------------------------------------------------------------------

| Id  | Operation                      | Name         | Rows  | Bytes | Cost (%C

PU)| Time     |

---------------------------------------------------------------------------------

|   0 | SELECT STATEMENT               |              |    49 |  4998 |    43

(3)| 00:00:01 |

|*  1 |  HASH JOIN                     |              |    49 |  4998 |    43

(3)| 00:00:01 |

|   2 |   TABLE ACCESS FULL            | T_TEST5      |     1 |     3 |     3

(0)| 00:00:01 |

|   3 |   TABLE ACCESS BY INDEX ROWID  | T_TEST1      |  2163 |   181K|    38

(0)| 00:00:01 |

|   4 |    NESTED LOOPS                |              |  1081 |   104K|    39

(0)| 00:00:01 |

|   5 |     TABLE ACCESS BY INDEX ROWID| T_TEST4      |     1 |    13 |     1

(0)| 00:00:01 |

|*  6 |      INDEX RANGE SCAN          | T_TEST4_IDX2 |     1 |       |     1

(0)| 00:00:01 |

|*  7 |     INDEX RANGE SCAN           | T_TEST1_IDX1 |  2163 |       |     2

(0)| 00:00:01 |

-------------------------------------------------------------------------------

SQL> select a.*

  2  from t_test1 a, t_test4 b;

1475042 rows selected.

---------------------------------------------------------------------------------

| Id  | Operation            | Name        | Rows  | Bytes | Cost (%CPU)| Time  |

---------------------------------------------------------------------------------

|   0 | SELECT STATEMENT     |             |  1475K|   120M|  4637 (2)| 00:00:56|

|   1 |  MERGE JOIN CARTESIAN|             |  1475K|   120M|  4637 (2)| 00:00:56|

|   2 |   INDEX FULL SCAN    | T_TEST4_IDX1|    31 |       |     1 (0)| 00:00:01|

|   3 |   BUFFER SORT        |             | 47582 |  3996K|  4636 (2)| 00:00:56|

|   4 |    TABLE ACCESS FULL | T_TEST1     | 47582 |  3996K|   150 (2)| 00:00:02|

--------------------------------------------------------------------------------

另外，9.2.0.6后，还可以通过设置38043 event来限制cartesian merge join。

5、案例分析

最后再回到我们一开始提到的案例。这个案例的产生原因就是因为相关表的统计数据不正确，导致优化器在做代价分析时产生了错误结果，因而使查询计划采用了笛卡尔乘积。以下是问题查询计划

对表CSS_SS_LATEST_VSL_SCHED重新分析后，查询计划被修正，问题解决。

上面我们分析了产生笛卡尔积的各种情况，但要记住一点，正如Full Table Scan一样，笛卡尔出现在语句中并不一定是坏事，关键是正确的语句、正确的统计数据才能产生正确的查询计划。

上一篇：如何获取长事务的运行情况

下一篇：Oracle中ROWNUM的使用技巧

本类中的所有文章