深度嵌套子查询分解(CTE)的性能降低 [英] Slow performance for deeply nested subquery factoring (CTE)
问题描述
此查询包含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?
第二季度.
它看起来像个错误，因为它在Oracle 9上运行非常快而在Oracle 11上运行非常慢.
实际上，每个带有较长且复杂的从句的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?
第3季度.
我必须为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.
Q2: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.
Q3:有两个未公开文件提示: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，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.
更新
-
只需安装11.2.0.4(Win7/32bit)并针对初始查询进行测试.优化器行为没有任何变化.
Just installed 11.2.0.4 (Win7/32bit) and test it against initial query. Nothing changed in optimizer behavior.
即使使用inline(未记录)或RULE(不建议使用)提示，也无法直接影响CBO行为.也许有些大师知道一些变种，但这对我来说(也是Google的最高机密:-).
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.
这篇关于深度嵌套子查询分解(CTE)的性能降低的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
