从一个笛卡尔连接说起(2010-10-22)

最新推荐文章于 2022-03-21 01:50:08 发布

zhaosj1726

最新推荐文章于 2022-03-21 01:50:08 发布

阅读量888

点赞数

分类专栏：性能优化文章标签： 2010 access table loops nested concatenation

本文链接：https://blog.csdn.net/zhaosj1726/article/details/6054880

版权

性能优化专栏收录该内容

22 篇文章 0 订阅

订阅专栏

今天一开发同事反映一个sql执行特慢,执行了十多分钟还没有出结果,一看执行计划,居然出现了CARTESIAN,也就是说出现了笛卡尔积.仔细查看连接条件,才发现是忘了写两个表的连接条件导致的,加上这样的连接条件之后,执行很快出了结果,并且结果是正确的.
当然这里出现笛卡尔积是因为没有写应该有的连接条件,有时候因为优化器统计信息不准确,本来是很大的表,因为优化器统计信息过时,优化器可能认为它们都是小表,以至于它会执行笛卡尔积,而后把连接条件作为过滤条件去执行,对小表来说这样执行是好的,可对大表来说,这样执行就是一场灾难.
想起以前帮助一网友分析的问题来(原帖见http://www.itpub.net/viewthread.php?tid=1291739&page=1#pid15625632):

IBM AIX下10.2数据，数据量接近一百万的四张表(网友写的是四张表,但我觉得应该是笔误,应该是三张表)相接，在测试库五分钟内执行完，在生成库执行了一个多小时，查询语句如下：

select
       be.customerid,
       be.corpid,
       trim(bc.manageorgid),
       bd.serialno,
       bd.balance*geterate(bd.BusinessCurrency, '01'), --geterate是一个转换汇率的函数
       bd.classifyresult,
       bc.direction,
       bc.sfgjxzhy,
      (case when bd.extendtimes > 0 then bd.actualmaturity
            when bd.businesstype = '1010020' then to_char((to_date(bd.putoutdate, 'yyyy-mm-dd')+90),'yyyy-mm-dd')
            else bd.maturity
       end)
from crr_batch_enter be,
      ods_business_contract bc,
      ods_business_duebill bd
where be.customerid=bc.customerid --customerid已经建索引
      and be.manageorgid = bc.manageorgid
      and bc.serialno=bd.relativeserialno2 --serialno和relativeserialno2已经建索引
      and ( bd.subjectno like '123%'
             or bd.subjectno like '124%'
             or bd.subjectno like '127%'
             or bd.subjectno like '128%'
             or bd.subjectno like '131%'
             or bd.subjectno like '135%'
             or bd.subjectno like '13701%'
             or bd.subjectno like '13703%'
             or bd.subjectno like '147%'
             or bd.subjectno like '143%'
             or bd.subjectno like '126%'
             or bd.subjectno like '158%'
          )
       and bd.subjectno not like '12402%'
       and bd.subjectno not like '12702%'
       and bd.subjectno not like '12802%'
       and bd.subjectno not like '1470302'
       and bd.subjectno not like '1470402'
       and bd.balance>0;

执行计划如下:

  Object name Cost Cardinality Bytes
SELECT STATEMENT, GOAL = ALL_ROWS   1115 12 3204
CONCATENATION
TABLE ACCESS BY INDEX ROWID  ODS_BUSINESS_CONTRACT 89 1 76
   NESTED LOOPS   89 1 267
    MERGE JOIN CARTESIAN   28 1 191
     TABLE ACCESS BY INDEX ROWID  ODS_BUSINESS_DUEBILL 26 1 137
      INDEX RANGE SCAN  ODS_BD_IDX2 25 1
     BUFFER SORT   2 82 4428
      TABLE ACCESS FULL  CRR_BATCH_ENTER 2 82 4428
    BITMAP CONVERSION TO ROWIDS
     BITMAP AND
      BITMAP CONVERSION FROM ROWIDS
       INDEX RANGE SCAN  ODS_BC_IDX0 2 518
      BITMAP CONVERSION FROM ROWIDS
       INDEX RANGE SCAN  ODS_BC_IDX1 2 518
TABLE ACCESS BY INDEX ROWID  ODS_BUSINESS_CONTRACT 90 1 76
   NESTED LOOPS   90 1 267
    MERGE JOIN CARTESIAN   29 1 191
     TABLE ACCESS BY INDEX ROWID  ODS_BUSINESS_DUEBILL 27 1 137
      INDEX RANGE SCAN  ODS_BD_IDX2 26 1
     BUFFER SORT   2 82 4428
      TABLE ACCESS FULL  CRR_BATCH_ENTER 2 82 4428
    BITMAP CONVERSION TO ROWIDS
     BITMAP AND
      BITMAP CONVERSION FROM ROWIDS
       INDEX RANGE SCAN  ODS_BC_IDX0 2 518
      BITMAP CONVERSION FROM ROWIDS
       INDEX RANGE SCAN  ODS_BC_IDX1 2 518
TABLE ACCESS BY INDEX ROWID  ODS_BUSINESS_CONTRACT 91 1 76
   NESTED LOOPS   91 1 267
    MERGE JOIN CARTESIAN   29 1 191
     TABLE ACCESS BY INDEX ROWID  ODS_BUSINESS_DUEBILL 27 1 137
      INDEX RANGE SCAN  ODS_BD_IDX2 26 1
     BUFFER SORT   2 82 4428
      TABLE ACCESS FULL  CRR_BATCH_ENTER 2 82 4428
    BITMAP CONVERSION TO ROWIDS
     BITMAP AND
      BITMAP CONVERSION FROM ROWIDS
       INDEX RANGE SCAN  ODS_BC_IDX0 2 518
      BITMAP CONVERSION FROM ROWIDS
       INDEX RANGE SCAN  ODS_BC_IDX1 2 518
TABLE ACCESS BY INDEX ROWID  ODS_BUSINESS_CONTRACT 92 1 76
   NESTED LOOPS   92 1 267
    MERGE JOIN CARTESIAN   29 1 191
     TABLE ACCESS BY INDEX ROWID  ODS_BUSINESS_DUEBILL 27 1 137
      INDEX RANGE SCAN  ODS_BD_IDX2 26 1
     BUFFER SORT   2 82 4428
      TABLE ACCESS FULL  CRR_BATCH_ENTER 2 82 4428
    BITMAP CONVERSION TO ROWIDS
     BITMAP AND
      BITMAP CONVERSION FROM ROWIDS
       INDEX RANGE SCAN  ODS_BC_IDX0 2 518
      BITMAP CONVERSION FROM ROWIDS
       INDEX RANGE SCAN  ODS_BC_IDX1 2 518
TABLE ACCESS BY INDEX ROWID  ODS_BUSINESS_CONTRACT 92 1 76
   NESTED LOOPS   92 1 267
    MERGE JOIN CARTESIAN   29 1 191
     TABLE ACCESS BY INDEX ROWID  ODS_BUSINESS_DUEBILL 27 1 137
      INDEX RANGE SCAN  ODS_BD_IDX2 26 1
     BUFFER SORT   2 82 4428
      TABLE ACCESS FULL  CRR_BATCH_ENTER 2 82 4428
    BITMAP CONVERSION TO ROWIDS
     BITMAP AND
      BITMAP CONVERSION FROM ROWIDS
       INDEX RANGE SCAN  ODS_BC_IDX0 2 518
      BITMAP CONVERSION FROM ROWIDS
       INDEX RANGE SCAN  ODS_BC_IDX1 2 518
TABLE ACCESS BY INDEX ROWID  ODS_BUSINESS_CONTRACT 93 1 76
   NESTED LOOPS   93 1 267
    MERGE JOIN CARTESIAN   30 1 191
     TABLE ACCESS BY INDEX ROWID  ODS_BUSINESS_DUEBILL 28 1 137
      INDEX RANGE SCAN  ODS_BD_IDX2 27 1
     BUFFER SORT   2 82 4428
      TABLE ACCESS FULL  CRR_BATCH_ENTER 2 82 4428
    BITMAP CONVERSION TO ROWIDS
     BITMAP AND
      BITMAP CONVERSION FROM ROWIDS
       INDEX RANGE SCAN  ODS_BC_IDX0 2 518
      BITMAP CONVERSION FROM ROWIDS
       INDEX RANGE SCAN  ODS_BC_IDX1 2 518
TABLE ACCESS BY INDEX ROWID  ODS_BUSINESS_CONTRACT 93 1 76
   NESTED LOOPS   93 1 267
    MERGE JOIN CARTESIAN   30 1 191
     TABLE ACCESS BY INDEX ROWID  ODS_BUSINESS_DUEBILL 28 1 137
      INDEX RANGE SCAN  ODS_BD_IDX2 27 1
     BUFFER SORT   2 82 4428
      TABLE ACCESS FULL  CRR_BATCH_ENTER 2 82 4428
    BITMAP CONVERSION TO ROWIDS
     BITMAP AND
      BITMAP CONVERSION FROM ROWIDS
       INDEX RANGE SCAN  ODS_BC_IDX0 2 518
      BITMAP CONVERSION FROM ROWIDS
       INDEX RANGE SCAN  ODS_BC_IDX1 2 518
TABLE ACCESS BY INDEX ROWID  ODS_BUSINESS_CONTRACT 94 1 76
   NESTED LOOPS   94 1 267
    MERGE JOIN CARTESIAN   30 1 191
     TABLE ACCESS BY INDEX ROWID  ODS_BUSINESS_DUEBILL 28 1 137
      INDEX RANGE SCAN  ODS_BD_IDX2 27 1
     BUFFER SORT   2 82 4428
      TABLE ACCESS FULL  CRR_BATCH_ENTER 2 82 4428
    BITMAP CONVERSION TO ROWIDS
     BITMAP AND
      BITMAP CONVERSION FROM ROWIDS
       INDEX RANGE SCAN  ODS_BC_IDX0 2 518
      BITMAP CONVERSION FROM ROWIDS
       INDEX RANGE SCAN  ODS_BC_IDX1 2 518
TABLE ACCESS BY INDEX ROWID  ODS_BUSINESS_CONTRACT 94 1 76
   NESTED LOOPS   94 1 267
    MERGE JOIN CARTESIAN   30 1 191
     TABLE ACCESS BY INDEX ROWID  ODS_BUSINESS_DUEBILL 28 1 137
      INDEX RANGE SCAN  ODS_BD_IDX2 27 1
     BUFFER SORT   2 82 4428
      TABLE ACCESS FULL  CRR_BATCH_ENTER 2 82 4428
    BITMAP CONVERSION TO ROWIDS
     BITMAP AND
      BITMAP CONVERSION FROM ROWIDS
       INDEX RANGE SCAN  ODS_BC_IDX0 2 518
      BITMAP CONVERSION FROM ROWIDS
       INDEX RANGE SCAN  ODS_BC_IDX1 2 518
TABLE ACCESS BY INDEX ROWID  ODS_BUSINESS_CONTRACT 95 1 76
   NESTED LOOPS   95 1 267
    MERGE JOIN CARTESIAN   30 1 191
     TABLE ACCESS BY INDEX ROWID  ODS_BUSINESS_DUEBILL 28 1 137
      INDEX RANGE SCAN  ODS_BD_IDX2 27 1
     BUFFER SORT   2 82 4428
      TABLE ACCESS FULL  CRR_BATCH_ENTER 2 82 4428
    BITMAP CONVERSION TO ROWIDS
     BITMAP AND
      BITMAP CONVERSION FROM ROWIDS
       INDEX RANGE SCAN  ODS_BC_IDX0 2 518
      BITMAP CONVERSION FROM ROWIDS
       INDEX RANGE SCAN  ODS_BC_IDX1 2 518
TABLE ACCESS BY INDEX ROWID  ODS_BUSINESS_CONTRACT 95 1 76
   NESTED LOOPS   95 1 267
    MERGE JOIN CARTESIAN   30 1 191
     TABLE ACCESS BY INDEX ROWID  ODS_BUSINESS_DUEBILL 28 1 137
      INDEX RANGE SCAN  ODS_BD_IDX2 27 1
     BUFFER SORT   2 82 4428
      TABLE ACCESS FULL  CRR_BATCH_ENTER 2 82 4428
    BITMAP CONVERSION TO ROWIDS
     BITMAP AND
      BITMAP CONVERSION FROM ROWIDS
       INDEX RANGE SCAN  ODS_BC_IDX0 2 518
      BITMAP CONVERSION FROM ROWIDS
       INDEX RANGE SCAN  ODS_BC_IDX1 2 518
TABLE ACCESS BY INDEX ROWID  ODS_BUSINESS_CONTRACT 96 1 76
   NESTED LOOPS   96 1 267
    MERGE JOIN CARTESIAN   31 1 191
     TABLE ACCESS BY INDEX ROWID  ODS_BUSINESS_DUEBILL 29 1 137
      INDEX RANGE SCAN  ODS_BD_IDX2 28 1
     BUFFER SORT   2 82 4428
      TABLE ACCESS FULL  CRR_BATCH_ENTER 2 82 4428
    BITMAP CONVERSION TO ROWIDS
     BITMAP AND
      BITMAP CONVERSION FROM ROWIDS
       INDEX RANGE SCAN  ODS_BC_IDX0 2 518
      BITMAP CONVERSION FROM ROWIDS
       INDEX RANGE SCAN  ODS_BC_IDX1 2 518

作者说这三个表都是百万级的表,估计结果集也应该是一个大的数量的(当然也可能是一个小的结果集,但这里没有使用任何的聚合函数,这种可能性还是挺小的),但优化器估算只返回了12条记录,而且这12条记录是CONCATENATION的结果,or的每一部分估算只返回了1条记录,而在优化器估算里,1和0经常是可以互相替代的,所以你也可以认为优化器估算这个sql没有返回任何的数据行,总之优化器认为这个sql返回了极少的数据行.全表扫描大表CRR_BATCH_ENTER,cost=2,card=82,因为这里的card是应用了过滤条件之后返回的行数,而这里作者没有给access,filter这些执行计划信息,不是太好判断这个card是否过小了,但从执行计划看,这是一个笛卡尔积的一部分,这里应该是没有应用过滤条件的,所以你可以认为优化器认为这个表只有82行数据,而作者说这3个表都是百万级的大表,显然优化器信息是不准确的,就算应用了过滤条件后实际也确实是返回了极少的数据行,可cost=2不论db_file_multiblock_read_count设置的有多大,对一个百万级的大表来说,都显得太小了,所以你基本上可以认定是优化器统计信息不准确造成的这个问题了.作者最后也确认在生产库上重新收集优化器统计信息之后,这个sql也很快执行完毕了.所以在查看执行计划时,card,bytes都是需要关注的,尤其是card这个很直观的数据值是需要关注的,在一定程度上可以辅助你了解优化器统计信息是否准确.

抛开优化器统计信息准确与否不说,如果认为优化器统计信息是准确的,来看看这个执行计划,看看它是如何来实现的,看看它是否合理,如何来实现更好:
这里的执行计划没有access,filter,所以不是很完整,你的具体索引也不清楚,我只能估计执行计划是这样的:
因为or的前后12个部分,所以它使用了use_concat的实现形式,将or的各个部分和其他的部分合并实现,最后CONCATENATION(想想这种实现方式是否高效,阻止这种实现方式可以使用提示no_expand).
单看每一部分,比如
from crr_batch_enter be,ods_business_contract bc,ods_business_duebill bd
    where be.customerid=bc.customerid --customerid已经建索引
    and be.manageorgid = bc.manageorgid
    and bc.serialno=bd.relativeserialno2 --serialno和relativeserialno2已经建索引
    and bd.subjectno like '123%'
        and bd.subjectno not like '12402%' and bd.subjectno not like '12702%'
        and bd.subjectno not like '12802%' and bd.subjectno not like '1470302'
        and bd.subjectno not like '1470402')
    and bd.balance>0;

这里咱们的思维是:bd的这么多条件过滤出一部分数据来,然后关联bc,然后bc和be关联,得出最终的结果.
可这里oracle不是这样做的,而是这样实现的:
bd过滤出一部分数据来,和be关联,但bd和be是通过bc才关联起来的,而这两者不是直接关联的,所以这里要让它们关联的话,只能是笛卡尔积了,所以be是全表扫描,bd,be都有数据之后,be.customerid=bc.customerid and bc.serialno=bd.relativeserialno2,也就是bc.customerid=:1 and bc.serialno=:2,这里customerid和serialno都是单列索引,所以它使用了index_combine的实现方式,所以有了你看到的 BITMAP CONVERSION FROM ROWIDS和BITMAP CONVERSION TO ROWIDS,最后它使用be.manageorgid = bc.manageorgid作为一个过滤条件.
其实你看看这个逻辑就知道它明显是不合理的.(其实对于小表来说也许是合适的,但对于大表明显是不合适的)

先不说测试数据库是如何实现的,我们可以想想这样是不是可以实现:
这里咱们的思维是:bd的这么多条件过滤出一部分数据来,然后关联bc,然后再和be关联,得出最终的结果.bc的serialno上建立索引,be的(customerid,manageorgid)上建立复合索引.bd上这么多的条件,不清楚11个or的总体的选择性如何,如果不高的话(返回很多的数据行),可以考虑使用no_expand,bd的这么多的条件一起使用得出bd上的结果.

你说测试库上还是比较快的,数据量一样吗?
如果数据量是大致相当的话,可以查看它们的索引结构是否相同,优化器统计信息是否相同,索引结构让它们一致,优化器统计信息也让它们尽量一致,都能反应实际数据的情况。
但如果这样了,效率还是差别很大的话,估计它们的执行计划应该是不同的,可以考虑使用outline,说白了就是使用测试库上的hints集合来固定生产库上的执行计划和测试库上的一致.所以也可以直接得到测试库上的hints,然后直接添加到生产库上的,
可以在测试库上执行
select * from table(dbms_xplan.display_cursor(:sql_id,:child_number,'advanced'));
使用advanced得到测试库上实际的执行计划,特别是hints,添加到生产库上就可以了.当然在测试库上执行一个10053事件,跟踪文件中也有outline信息的.

当然,这里的实际问题就在于优化器认为这三个表都是小表,所以使用了对于小表来说适用但对于大表却不适用的笛卡尔连接.

关于笛卡尔连接,可以看看杨版主的这篇文章:http://yangtingkun.itpub.net/post/468/233091