深层嵌套子查询分解 (CTE) 的性能缓慢

2021-12-30 00:00:00 sql benchmarking oracle11g oracle

此查询由 16 个相等的步骤组成.
每一步都是在同一个数据集(单行)上做同样的计算，
但最后一步需要太多时间.

This query consists of 16 equal steps.
Every step is doing the same calculation on the same dataset (a single row),
but last steps take too much time for it. 

with t0 as (select 0 as k from dual)
,t1 as (select k from t0 where k >= (select avg(k) from t0))
,t2 as (select k from t1 where k >= (select avg(k) from t1))
,t3 as (select k from t2 where k >= (select avg(k) from t2))
,t4 as (select k from t3 where k >= (select avg(k) from t3))
,t5 as (select k from t4 where k >= (select avg(k) from t4))
,t6 as (select k from t5 where k >= (select avg(k) from t5))
,t7 as (select k from t6 where k >= (select avg(k) from t6))
,t8 as (select k from t7 where k >= (select avg(k) from t7))
,t9 as (select k from t8 where k >= (select avg(k) from t8))
,t10 as (select k from t9 where k >= (select avg(k) from t9))
,t11 as (select k from t10 where k >= (select avg(k) from t10))
,t12 as (select k from t11 where k >= (select avg(k) from t11)) -- 0.5 sec
,t13 as (select k from t12 where k >= (select avg(k) from t12)) -- 1.3 sec
,t14 as (select k from t13 where k >= (select avg(k) from t13)) -- 4.5 sec
,t15 as (select k from t14 where k >= (select avg(k) from t14)) -- 30 sec
,t16 as (select k from t15 where k >= (select avg(k) from t15)) -- 4 min
select k from t16

子查询 t10 立即完成，但整个查询 (t16) 需要 4 分钟才能完成.

Subquery t10 completes immediately, but the entire query (t16) requires 4 minutes to complete.

第一季度.
为什么相同数据的相同子查询的计算时间相差很大?

Q1.
Why calculation times of identical subqueries on the same data are very different?

Q2.
它看起来像一个错误，因为它在 Oracle 9 上运行得非常快，而在 Oracle 11 上运行得非常慢.
事实上，每个带有长而复杂的 with 子句的 select 语句的行为方式都是一样的.
这是一个已知的错误吗?(我无法访问 metalink)
建议采用什么解决方法?

Q2.
It looks like a bug as it runs very fast on Oracle 9 and very slow on Oracle 11.
In fact, every select statement with long and complex with-clause would behave the same way.
Is it a known bug? (I don't have access to metalink)
What workaround is recommended?

第三季度.
我必须为 Oracle 11 编写代码，并且必须在单个 select 语句中完成所有计算.
我无法将长语句拆分为两个单独的语句以加快速度.
Oracle 中是否存在提示(或者可能是一些技巧)使整个查询 (t16) 在合理的时间(例如，在一秒内)完成?我试图找到这样一个，但无济于事.
顺便说一句，执行计划非常好，成本表现为步数的线性函数(不是指数函数).

Q3.
I must write code for Oracle 11 and I must do all my calculations in single select statement.
I can't split my long statement in two separate statements to speed it up.
Does there exist a hint in Oracle (or maybe some trick) to make the whole query (t16) be completed in a reasonable time (e.g., within one second)? I tried to find such one but to no avail.
BTW, execution plan is excellent, and cost behaves as a linear function (not exponential) of number of steps.

推荐答案

Q1: 似乎与计算时间无关，只是优化器算法中的错误使其在计算最佳执行计划时变得疯狂.

Q1: Seems that there are nothing about calculation time, just bug in optimizer algorithm which make it mad while calculating a best execution plan.

问题 2:Oracle 11.X.0.X 中存在许多与嵌套查询和查询分解优化相关的已知和已修复错误.但是很难找到具体的问题.

Q2: There are a number of known and fixed bugs in Oracle 11.X.0.X related to optimization of nested queries and query factoring. But it's very hard to find a concrete issue.

问题 3:有两个 未记录 提示:materialize 和 inline，但在我尝试您的示例时，没有任何提示对我有用.服务器配置中的某些更改或升级到 11.2.0.3 可能会增加嵌套 with 子句的限制:对我而言(在 11.2.0.3 Win7/x86 上)您的示例工作正常，但增加嵌套的数量到 30 个表会挂起一个会话.

Q3: There are two undocumented hints: materialize and inline but no one of them works for me while I tried your example. It's possible that some changes in server configuration or upgrading to 11.2.0.3 may increase limit of nested with clauses: for me (on 11.2.0.3 Win7/x86) your example works fine, but increasing number of nested tables to 30 hangs a session.

解决方法可能如下所示:

Workaround may look like this:

select k from (
select k, avg(k) over (partition by null) k_avg from ( --t16
  select k, avg(k) over (partition by null) k_avg from ( --t15
    select k, avg(k) over (partition by null) k_avg from ( --t14
      select k, avg(k) over (partition by null) k_avg from ( --t13
        select k, avg(k) over (partition by null) k_avg from ( --t12
          select k, avg(k) over (partition by null) k_avg from ( --t11
            select k, avg(k) over (partition by null) k_avg from ( --t10
              select k, avg(k) over (partition by null) k_avg from ( --t9
                select k, avg(k) over (partition by null) k_avg from ( --t8
                  select k, avg(k) over (partition by null) k_avg from ( --t7
                    select k, avg(k) over (partition by null) k_avg from ( --t6
                      select k, avg(k) over (partition by null) k_avg from ( --t5
                        select k, avg(k) over (partition by null) k_avg from ( --t4
                          select k, avg(k) over (partition by null) k_avg from ( --t3
                            select k, avg(k) over (partition by null) k_avg from ( --t2
                              select k, avg(k) over (partition by null) k_avg from ( -- t1
                                select k, avg(k) over (partition by null) k_avg from (select 0 as k from dual) t0
                              ) where k >= k_avg
                            ) where k >= k_avg
                          ) where k >= k_avg
                        ) where k >= k_avg
                      ) where k >= k_avg
                    ) where k >= k_avg
                  ) where k >= k_avg
                ) where k >= k_avg
              ) where k >= k_avg
            ) where k >= k_avg
          ) where k >= k_avg
        ) where k >= k_avg
      ) where k >= k_avg
    ) where k >= k_avg
  ) where k >= k_avg
) where k >= k_avg
)

至少它在嵌套级别为 30 时对我有用，并且使用 WINDOW BUFFER 和 VIEW 而不是 LOAD TABLE AS SELECT 产生完全不同的执行计划code>、SORT AGGREGATE 和 TABLE ACCESS FULL.

At least it works for me on nesting level of 30 and produces totally different execution plan with WINDOW BUFFER and VIEW instead of LOAD TABLE AS SELECT, SORT AGGREGATE and TABLE ACCESS FULL.

更新

  1. 刚刚安装了 11.2.0.4 (Win7/32bit) 并根据初始查询对其进行测试.优化器行为没有任何变化.

  1. Just installed 11.2.0.4 (Win7/32bit) and test it against initial query. Nothing changed in optimizer behavior.

即使使用 inline(未记录)或 RULE(已弃用)提示，也不可能直接影响 CBO 行为.可能有些大师知道某个变体，但这对我来说是最高机密(谷歌也是 :-).

There are no possibility to directly affect a CBO behavior, even with use of inline (undocumented) or RULE (deprecated) hints. May be some Guru knows a some variant, but it's a Top Secret for me (and Google too :-) .

如果将主 select 语句分成多个部分并放入返回一组行的函数(返回 sys_refcursor 或强类型游标的函数)，则可以在合理的时间内在一个 select 语句中执行操作，但它是如果查询是在运行时构建的，则不是一个选择.

Doing things in a one select statement in reasonable time is possible if a main select statement separated into a parts and placed into the function which returns a set of rows (function returning sys_refcursor or strong typed cursor), but it's not a choice if a query constructed at runtime.

使用 XML 的解决方法是可能的，但是这个变体看起来像是通过屁股洞去除扁桃体(抱歉):

Workaround with usage of XML is possible, but this variant looks like removing a tonsil through the ass hole (sorry):

.

select
  extractvalue(column_value,'/t/somevalue') abc
from 
  table(xmlsequence((
    select t2 from (
      select
        t0,
        t1,
        (   
          select xmlagg(
                   xmlelement("t", 
                     xmlelement("k1",extractvalue(t1t.column_value,'/t/k1')), 
                     xmlelement("somevalue", systimestamp))
                  )
          from 
            table(xmlsequence(t0)) t0t, 
            table(xmlsequence(t1)) t1t  
          where 
            extractvalue(t1t.column_value,'/t/k1') >= (
              select avg(extractvalue(t1t.column_value, '/t/k1')) from table(xmlsequence(t1))
            )                                              
            and 
            extractvalue(t0t.column_value,'/t/k2') > 6
        ) t2
      from (
        select
          t0,
          (
            select xmlagg(
                     xmlelement("t", 
                       xmlelement("k1",extractvalue(column_value,'/t/k1')), 
                       xmlelement("somevalue", sysdate))
                    )
            from table(xmlsequence(t0))   
            where 
              extractvalue(column_value,'/t/k1') >= (
                select avg(extractvalue(column_value, '/t/k1')) from table(xmlsequence(t0))
              )
          ) t1
        from (
          select
            xmlagg(xmlelement("t", xmlelement("k1", level), xmlelement("k2", level + 3))) t0
          from dual connect by level < 5
        )
      )
    )
  )))

关于上述奇怪代码的另一件事是，此变体仅适用于 with 数据集没有大量行的情况.

Another thing about a strange code above is that this variant applicable only if with data sets didn't have a big number of rows.

相关文章