DBMS_XPLAN.DISPLAY_CURSOR中的sql计划差异

Question

在Oracle12.1上运行，我在他们的计划中寻找具有全表扫描的SQL。

当我看进去的时候：

select * from V$SQL_PLAN where sql_id = '89p47f9wnnwg9'

​

​

我返回了21行，其中一行具有完全的表访问权限

SELECT * FROM TABLE ( DBMS_XPLAN.DISPLAY_CURSOR ('89p47f9wnnwg9', 0, 'ALL'))

​

​

为什么会有这样的差异？

Answer 1

一个查询字符串(由SQL_ID表示)可以有多个不同的执行计划。它们都存储在V$SQL_PLAN中，并由不同的CHILD_NUMBER标识。

函数DBMS_XPLAN.DISPLAY_CURSOR有第二个参数，您可以在其中传递所需的child_number。

要查看实际使用的是哪个子游标，可以检查V$SESSION列SQL_CHILD_NUMBER。

针对12c的更新

对于为什么V$SQL_PLAN的行数比DBMS_XPLAN.DISPLAY_CURSOR多这个问题，这个显而易见的答案是Oracle12c版本之前最可能的答案。12c引入了Adaptive Query Optimization，其中一些操作被优化器标记为非活动。这个选择可以通过执行引擎来切换。这样的计划可以通过操作STATISTICS COLLECTOR来识别。统计信息收集器在执行时测试实际行数，如果实际行数大于from optimize calculated point of inflection，则切换计划。(示例-嵌套循环适用于少数行，但适用于大量行；相反，散列连接适用于大量行，但对于少数行，它具有较高的开销。拐点应该对应于这样的行数，其中成本估计相同)。

不幸的是，V$SQL_PLAN中没有标识非活动操作的列。

此blog观察到，可以使用元素display_map属性@skp在列OTHER_XML中提取此信息

 <display_map>
    <row op="1" dis="1" par="0" prt="0" dep="1" skp="0"/>
    <row op="2" dis="2" par="1" prt="0" dep="2" skp="0"/>
    <row op="3" dis="2" par="2" prt="0" dep="2" skp="1"/>
    ....

Answer 2

乔纳森·刘易斯

这是一个自适应计划-- Oracle可以在两个拐点(由STATISTICS COLLETOR指示)操作中做出运行时决策。

将display_cursor调用中的' all‘更改为'adaptive’，您将看到所有行以及一个说明如何识别“非活动”行的注释

乔纳森·刘易斯

所以

SELECT * FROM TABLE ( DBMS_XPLAN.DISPLAY_CURSOR ('89p47f9wnnwg9', null, 'ADAPTIVE'))

返回行，但被标记为“未使用”。