【sql调优之执行计划】merge join cartesian and buffer sort

最新推荐文章于 2022-10-23 11:26:22 发布

cixlv48826

最新推荐文章于 2022-10-23 11:26:22 发布

阅读量498

点赞数

文章标签：数据库

数据库版本：oracle 9208

有时在执行计划中看到了笛卡尔连接，通常会发现是关联有问题，例如：

SQL> select * from t_bank_class a,t_log_detail b;

no rows selected

Execution Plan

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

0 SELECT STATEMENT ptimizer=CHOOSE (Cost=4 Card=1 Bytes=4652)

1 0 MERGE JOIN (CARTESIAN) (Cost=4 Card=1 Bytes=4652)

2 1 TABLE ACCESS (FULL) OF 'T_LOG_DETAIL' (Cost=2 Card=1 Byt

es=4641)

3 1 BUFFER (SORT) (Cost=2 Card=6 Bytes=66)

4 3 TABLE ACCESS (FULL) OF 'T_BANK_CLASS' (Cost=2 Card=6 B

ytes=66)

Statistics

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

0 recursive calls

0 db block gets

3 consistent gets

1 physical reads

0 redo size

795 bytes sent via SQL*Net to client

237 bytes received via SQL*Net from client

1 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

0 rows processed

SQL>

很多时候需要去检查一下是否是sql逻辑有问题，但也有时并不是因为逻辑问题而导致数据库选择这样的执行计划，

例如：

SQL> select 1 from t_group_policy_product tgpp,

2 t_product_life tpl,

3 t_period_type tpy

4 where tgpp.policy_id = 24

5 and tgpp.product_id = tpl.product_id

6 and tgpp.main_rider = '1'

7 and tpl.period_type =tpy.period_type

8 and tpy.period_type in (1,2)

9 ;

Execution Plan

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

0 SELECT STATEMENT ptimizer=CHOOSE (Cost=3 Card=1 Bytes=20)

1 0 NESTED LOOPS (Cost=3 Card=1 Bytes=20)

2 1 MERGE JOIN (CARTESIAN) (Cost=2 Card=3 Bytes=42)

3 2 TABLE ACCESS (BY INDEX ROWID) OF 'T_GROUP_POLICY_PRODU

CT' (Cost=1 Card=2 Bytes=24)

4 3 INDEX (RANGE SCAN) OF 'UNI_GROUP_POLICY_PRODUCT' (UN

IQUE) (Cost=3 Card=3)

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

6 5 INDEX (FULL SCAN) OF 'PK_T_PERIOD_TYPE' (UNIQUE) (Co

st=1 Card=2 Bytes=4)

7 1 TABLE ACCESS (BY INDEX ROWID) OF 'T_PRODUCT_LIFE' (Cost=

1 Card=1 Bytes=6)

8 7 INDEX (UNIQUE SCAN) OF 'PK_T_PRODUCT_LIFE' (UNIQUE)

Statistics

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

0 recursive calls

0 db block gets

11 consistent gets

0 physical reads

0 redo size

206 bytes sent via SQL*Net to client

244 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

1 sorts (memory)

0 sorts (disk)

1 rows processed

SQL>

使用了MERGE JOIN (CARTESIAN)操作，笛卡尔合并连接，注意到这里INDEX (RANGE SCAN) OF 'UNI_GROUP_POLICY_PRODUCT'只会取到一行数据，

oracle仅仅将这个结果集与后面的 INDEX (FULL SCAN) OF 'PK_T_PERIOD_TYPE'的结果集放到一起（使用笛卡尔乘积），然后将结果与T_PRODUCT_LIFE连接（外层是个nested loop）,

这是因为做笛卡尔乘积的两个表返回行的cardinality 比较小，故而会比较高效。

asktom上面也有个相似的例子：

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:4105951726381

将这个查询语句稍作修改：

SQL> select * from t_group_policy_product tgpp,

2 t_product_life tpl,

3 t_period_type tpy

4 where tgpp.policy_id = 24

5 and tgpp.product_id = tpl.product_id

6 and tgpp.main_rider = '1'

7 and tpl.period_type =tpy.period_type

8 and tpy.period_type in (1,2)

9 ;

Execution Plan

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

0 SELECT STATEMENT ptimizer=CHOOSE (Cost=3 Card=1 Bytes=1411)

1 0 NESTED LOOPS (Cost=3 Card=1 Bytes=1411)

2 1 NESTED LOOPS (Cost=2 Card=2 Bytes=2798)

3 2 TABLE ACCESS (BY INDEX ROWID) OF 'T_GROUP_POLICY_PRODU

CT' (Cost=1 Card=2 Bytes=536)

4 3 INDEX (RANGE SCAN) OF 'UNI_GROUP_POLICY_PRODUCT' (UN

IQUE) (Cost=3 Card=3)

5 2 TABLE ACCESS (BY INDEX ROWID) OF 'T_PRODUCT_LIFE' (Cos

t=1 Card=1 Bytes=1131)

6 5 INDEX (UNIQUE SCAN) OF 'PK_T_PRODUCT_LIFE' (UNIQUE)

7 1 TABLE ACCESS (BY INDEX ROWID) OF 'T_PERIOD_TYPE' (Cost=1

Card=1 Bytes=12)

8 7 INDEX (UNIQUE SCAN) OF 'PK_T_PERIOD_TYPE' (UNIQUE)

Statistics

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

0 recursive calls

0 db block gets

11 consistent gets

1 physical reads

0 redo size

22202 bytes sent via SQL*Net to client

244 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

1 rows processed

虽然cost和card都没有改变，但由于使用了*使得需要从行中返回多个列，使得bytes值变得相对大很多（意味着可能使用更多的memory），

这时候可以看到执行计划已经改变了，oracle使用nested loops代替了之前的merge join(CARTESIAN)。

而buffer (sort）则是指使用内存排序。一些操作（如Merge join 或者order by）需要对行集排序。Buffer sort使用的内存数量与数据量和 sort_area_size初始参数有关（如果workarea_size_policy参数被设置为AUTO，则与pga_aggregate_target参数相关），

为什么这里会使用buffer sort？一般来说，如果优化器倾向于使用IO cost model，谓词中的过滤条件不止一列且没有索引，而过滤条件预期的返回行只有一行，这种情况下可能会使用buffer sort，事实上，这个例子中执行计划已经提示了：

Note: cpu costing is off, PLAN_TABLE' is old version

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT

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

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

| 0 | SELECT STATEMENT | | 1 | 2

| 1 | NESTED LOOPS | | 1 | 2

| 2 | MERGE JOIN CARTESIAN | | 3 | 4

| 3 | TABLE ACCESS BY INDEX ROWID| T_GROUP_POLICY_PRODUCT | 2 | 2

| 4 | INDEX RANGE SCAN | UNI_GROUP_POLICY_PRODUCT | 3 |

| 5 | BUFFER SORT | | 2 |

| 6 | INDEX FULL SCAN | PK_T_PERIOD_TYPE | 2 |

| 7 | TABLE ACCESS BY INDEX ROWID | T_PRODUCT_LIFE | 1 |

| 8 | INDEX UNIQUE SCAN | PK_T_PRODUCT_LIFE | 1 |

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

Note: cpu costing is off, PLAN_TABLE' is old version

16 rows selected

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

转载于:http://blog.itpub.net/16179598/viewspace-671083/

cixlv48826

关注

0
点赞
踩
2

收藏

觉得还不错? 一键收藏
0
评论
【sql调优之执行计划】merge join cartesian and buffer sort

数据库版本：oracle 9208有时在执行计划中看到了笛卡尔连接，通常会发现是关联有问题，例如：SQL> select * from t_bank_class a,t_log_detail b;n...
复制链接

扫一扫