love wife & love life —Roger的Oracle&MySQL技术博客

Phone:18180207355 提供专业Oracle&MySQL数据恢复、性能优化、迁移升级、紧急救援等服务

怪异的SQL执行计划

本站文章除注明转载外,均为本站原创: 转载自love wife & love life —Roger的Oracle&MySQL技术博客

本文链接地址: 怪异的SQL执行计划

这是道森Oracle 培训班中的一个学生提到的问题,测试了下,发现确实非常之怪异,如下是我的测试过程,大家一起来研究一下。

这个sql无论是用use_nl还是use_nl+order 的hint都无法改变这个执行计划,下面我们来看下为什么Oracle会走出这样的执行计划。
我怀疑可能跟直方图有关系,收集下直方图,发现一样的现象,如下:

我们可以看到,Oracle仍然是走了nest loop semi,而且驱动表是BIG1,这显然是有问题的。 下面我们用10053 event来跟踪下优化器是如何来评估
出这个执行计划的

从10053 trace,我们可以看到,本质上Oracle将其进行了查询转换,修改为如下等价语句:

下面我们来看下详细的计算成本:

从上面的信息来看,我们发现几种类型的连接方式的cost 计算如下:

很明显,根据Oracle的计算,认为这种nest loop的成本代价是197.55,也就是198,认为这是效率最高的。
关于这个cost=198,我们可以很容易进行计算,如下:

根据计算最后的IO COST之和确实是196+2=198,和执行计划是一致的。   但是仍然无法解释为什么在10g中Oracle会这样走?
下面我们来看下110203的测试情况。

将10g的2个表的数据导入到11g环境中,然后重新收集统计信息:

我们发现在11gR2环境中,执行计划完全不一样了,虽然也是走的nest loop,对于小表这里进行了index full scan,而大表
这里进行了index range scan,很显然这里是把小表视为驱动表了,这才是正常的执行计划。

下面我们来看下11.2.0.3版本中的10053 event的跟踪trace信息:

根据trace内容,我们知道这几种join方式的成本如下:

仍然是nest loop的成本是最低的,跟10g的类似,然而差异也比较大。

从10053的trace 看不出有什么异常,我将优化器降低到10.2.0.5版本后,测试发现结果居然也不一致:

我们对比10g和11g的执行计划,发现其实10g是先执行owner=’SYS’的条件,而11g是进行objecT_id=过滤,如下是两个版本的SQL差异:

我们可以发现,10g中oracle查询转换之后object_id的条件在前,owner条件在后。而在11gR2中版本恰好相反(注意,11g中会多2个列,后面查询时去掉)。

当我将查询改写之后的SQL拿到10g的环境测试,奇怪的事情发生了。

大家可以看到,根据10053 event的trace我们发现10gR2版本中查询改写的SQL,如果拿出来单独执行的话,是ok的。

但是为什么执行原始SQL,其执行计划就是不对呢? 真是有点匪夷所思了。

大家一起来讨论下这个问题。目前尚未找到根本的原因。

7 Responses to “怪异的SQL执行计划”

  1. roger Says:

    10.2.0.4 ,windows 7 x64测试如下:

    SQL> select * from table(dbms_xplan.display_cursor(NULL,NULL,'ALLSTATS'));

    PLAN_TABLE_OUTPUT
    ------------------------------------------------------------------------------------------
    ------------------------------------------------------------
    SQL_ID 744z7fvx3unrc, child number 0
    -------------------------------------
    select /*+ gather_plan_statistics */ t.* FROM BIG1 T WHERE T.OBJECT_ID IN(SELECT
    T1.OBJECT_ID FROM SMALL1 T1 WHERE T.OWNER='SYS')

    Plan hash value: 3425505090

    ------------------------------------------------------------------------------------------
    | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
    ------------------------------------------------------------------------------------------
    |* 1 | FILTER | | 2 | | 12 |00:00:00.02 | 47640 |
    | 2 | TABLE ACCESS FULL| BIG1 | 2 | 50330 | 100K|00:00:00.01 | 1396 |
    |* 3 | FILTER | | 100K| | 12 |00:00:00.10 | 46244 |
    |* 4 | INDEX RANGE SCAN| INDSMALL1 | 46244 | 1 | 12 |00:00:00.08 | 46244 |
    ------------------------------------------------------------------------------------------

    Predicate Information (identified by operation id):
    ---------------------------------------------------

    1 - filter( IS NOT NULL)
    3 - filter(:B1='SYS')
    4 - access("T1"."OBJECT_ID"=:B1)

  2. ssaer Says:

    你偷换了一个概念.把自己弄糊涂了.
    在11g中,转换前后的SQL是不等价的.11g中采用了SORT UNIQUE避免了不等价.

  3. roger Says:

    11g这里只是对比下,不是重点,你看我最后的测试,都是在10g测试的呀?

  4. ssaer Says:

    10g下本来就应该是走semi的. 那样的执行计划是正确的.
    11g下应该是优化了算法或者说优化了半连接..

  5. carcase Says:

    你的sql是不是写错了

    SELECT t.*
    FROM BIG1 T
    WHERE T.OBJECT_ID IN
    (SELECT T1.OBJECT_ID FROM SMALL1 T1 WHERE T1.OWNER = ‘SYS’)

  6. 敬勇 Says:

    11g中嵌套循环在通过索引访问被驱动表时使用了向量I/O,从你的11g的执行计划中可以看到两次
    NESTED LOOPS,而在10g中只有一次NESTED LOOPS.我测试的结果是当在11g中指定/*+ optimizer_features_enable(‘10.2.0.5’) */ Hint后,11g中的执行结果与10g中是一样的。

  7. 敬勇 Says:

    可能在10g中如果先访问INDSMALL1索引作为驱动结果集,再过次object_id列上的索引访问BIG1表,是通过object_id列上的索引只能执行单块读取,这样计算出来的成本还不如先全表扫描BIG1表,让它作为驱动结果集。而在11g中对于嵌套循环在通过索引访问被驱动表时使用了向量I/O,在先访问INDSMALL1索引作为驱动结果集,再通过索引访问表BIG1时,就可以使用向量I/O。但这只是我的猜测,当在11g将版本降低为10g相同的版本后,执行结果与10g中一致。

Leave a Reply

You must be logged in to post a comment.