查看: 24984|回复: 22

[精华] Oracle 12C子查询平坦化的BUG

[复制链接]
认证徽章
论坛徽章:
43
现任管理团队成员
日期:2011-05-07 01:45:08ITPUB元老
日期:2012-09-12 14:50:28版主5段
日期:2014-06-11 02:21:31阿斯顿马丁
日期:2013-11-19 10:38:16祖母绿
日期:2012-11-06 12:43:12路虎
日期:2013-11-20 11:37:53雪佛兰
日期:2013-09-05 13:28:25ITPUB9周年纪念徽章
日期:2010-10-08 09:28:512015中国数据库技术大会纪念徽章
日期:2015-04-24 16:04:24秀才
日期:2015-05-26 13:32:07
发表于 2014-1-10 09:21 | 显示全部楼层 |阅读模式
本帖最后由 myth8860 于 2014-1-10 09:22 编辑

因为达梦7的优化器对子查询采用了平坦化技术,所以比较关注Oracle是否也采用这种技术, 果然,在测试Oracle 12C时,发现Oracle 也在试图引入这一技术, 看来英雄所见略同啊!

不过显然12C对子查询的平坦化技术没有达梦7成熟, 下面是一个非常简单的例子, 发生了查询结果错误:
12C的优化器,在平坦化子查询的过程中,把一个本来应该变换为左外连接的动作,错误地变换为HASH 内连接,造成了查询结果丢失。

这个错误重现步骤如下:

先创建两个表x1, x2, 对x2插入一行,然后提交。
Create table x1(c1 int);
Create table x2(d1 int, d2 int);
Insert into x2 values(1, 2);
Commit;

然后执行下面这两个查询,发现返回结果不一样:
A: Select * from x2 Where (select count(*)from x1 aa where c1 = d1) + d2 > d1;
B: Select * from x2 Where (select count(*)from x1 aa where c1 = d1) > d1 – d2;

A与B的唯一差别是d2在表达式中的位置,学过四则运算的同学都知道它们是等价的,但是12C认为它们不等价,查询A没有结果返回,而查询B则返回一行。
这里查询B的结果是正确的,应该返回一行。

下面是带auto trace的执行的过程:
SQL> set autot on
SQL> select * from x2 where (selectcount(*) from x1 aa where c1=d1) + d2 > d1;

未选定行


执行计划
----------------------------------------------------------
Plan hash value: 1144025758

--------------------------------------------------------------------------------
| Id | Operation            | Name    | Rows | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0| SELECT STATEMENT     |         |    1 |    52 |     4  (0)| 00:00:01 |
|*  1|  HASH JOIN           |         |    1 |    52 |     4  (0)| 00:00:01 |
|   2|   VIEW               | VW_SQ_1 |     1 |   26 |     2   (0)| 00:00:01 |
|   3|    HASH GROUP BY     |        |     1 |    13 |    2   (0)| 00:00:01 |
|   4|     TABLE ACCESS FULL| X1      |    1 |    13 |     2  (0)| 00:00:01 |
|   5|   TABLE ACCESS FULL  | X2     |     1 |    26 |    2   (0)| 00:00:01 |
--------------------------------------------------------------------------------

Predicate Information (identified byoperation id):
---------------------------------------------------

   1- access("ITEM_0"="D1")
      filter("D1"<"COUNT(*)"+"D2")

Note
-----
   -dynamic statistics used: dynamic sampling (level=2)


统计信息
----------------------------------------------------------
         0  recursive calls
         0  db block gets
         3  consistent gets
         0  physical reads
         0  redo size
       403  bytes sent via SQL*Net toclient
       533  bytes received via SQL*Netfrom client
         1  SQL*Net roundtrips to/fromclient
         0  sorts (memory)
         0  sorts (disk)
         0  rows processed

SQL> select * from x2 where (selectcount(*) from x1 aa where c1=d1) > d1 - d2;

       D1         D2
---------- ----------
        1          2


执行计划
----------------------------------------------------------
Plan hash value: 920484371

----------------------------------------------------------------------------
| Id | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0| SELECT STATEMENT    |      |    1 |    26 |     4  (0)| 00:00:01 |
|*  1|  FILTER             |      |      |       |            |          |
|   2|   TABLE ACCESS FULL | X2   |    1 |    26 |     2  (0)| 00:00:01 |
|   3|   SORT AGGREGATE    |     |     1 |    13 |            |          |
|*  4|    TABLE ACCESS FULL| X1   |    1 |    13 |     2  (0)| 00:00:01 |
----------------------------------------------------------------------------

Predicate Information (identified byoperation id):
---------------------------------------------------

   1- filter("D1"-"D2"< (SELECT COUNT(*) FROM "X1""AA" WHERE
              "C1"=:B1))
   4- filter("C1"=:B1)

Note
-----
   -dynamic statistics used: dynamic sampling (level=2)


统计信息
----------------------------------------------------------
         0  recursive calls
         0  db block gets
         6  consistent gets
         0  physical reads
         0  redo size
       604  bytes sent via SQL*Net toclient
       544  bytes received via SQL*Netfrom client
         2  SQL*Net roundtrips to/fromclient
         0  sorts (memory)
         0  sorts (disk)
         1  rows processed


测试的版本是Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bitProduction


在11g上,因为没有平坦化技术所以没有这个问题。为什么会发生这个错误呢?分析这个查询,发现子查询:(select count(*) from x1 aa where c1=d1)应该永远返回0,因为x1是个空表,显然在空表上做任何过滤求结果的行数count(*)都应该为0Oracle 12C的优化器试图预先把每一个T2(d1)对应的count(*)都求出来,这个求解过程需要一个JOIN, 但是12C没有考虑到有可能D1没有对应任何C1, 也就是把count(*) = 0这个情况错误地忽略了,观察第一个查询A的计划,发现居然是一个普通的HASH JOIN, 事实上它应该是一个LEFT HASH JOIN

而查询B 只是稍微调整了一下布尔表达式D2的位置, 12C就无法平坦化子查询了,这里可以看出12C的平坦化技术还非常有限,不过传统的迭代求值反而避免了查询结果错误。

虽然对查询优化器来说,这只是一个小BUG, 但是查询结果错误对客户来说却是很严重的问题。这从一个侧面,说明达梦7全面采用彻底的平坦化技术,方向是正确的,走在了Oracle的前面。

转自达梦韩总微博:

http://www.weibo.com/u/1871115162

论坛徽章:
4
2011新春纪念徽章
日期:2011-02-18 11:42:48奥迪
日期:2013-12-07 17:06:42优秀写手
日期:2013-12-18 09:29:09日产
日期:2014-01-15 14:18:32
发表于 2014-1-10 09:59 | 显示全部楼层
达梦威武

使用道具 举报

回复
论坛徽章:
11
SQL极客
日期:2013-12-09 14:13:35SQL数据库编程大师
日期:2013-12-06 13:59:43SQL大赛参与纪念
日期:2013-12-06 14:03:45红孩儿
日期:2012-12-19 11:08:17优秀写手
日期:2013-12-18 09:29:09暖羊羊
日期:2015-04-22 14:41:41
发表于 2014-1-10 10:04 | 显示全部楼层
这个小技术点体现了DM开发的实力,不错。
oracle的第一个版本永远是bug多多,这符合oracle的一贯作风啊

使用道具 举报

回复
认证徽章
论坛徽章:
43
现任管理团队成员
日期:2011-05-07 01:45:08ITPUB元老
日期:2012-09-12 14:50:28版主5段
日期:2014-06-11 02:21:31阿斯顿马丁
日期:2013-11-19 10:38:16祖母绿
日期:2012-11-06 12:43:12路虎
日期:2013-11-20 11:37:53雪佛兰
日期:2013-09-05 13:28:25ITPUB9周年纪念徽章
日期:2010-10-08 09:28:512015中国数据库技术大会纪念徽章
日期:2015-04-24 16:04:24秀才
日期:2015-05-26 13:32:07
发表于 2014-1-10 11:26 | 显示全部楼层
本帖最后由 myth8860 于 2014-1-10 11:29 编辑

子查询拉平是dm7一大创新的地方,能够极大提高复杂子查询的效率。
最大的好处是子查询拉平可以并行执行,MPP下能够很方便的并行。

使用道具 举报

回复
论坛徽章:
401
紫蛋头
日期:2012-05-21 10:19:41迷宫蛋
日期:2012-06-06 16:02:49奥运会纪念徽章:足球
日期:2012-06-29 15:30:06奥运会纪念徽章:排球
日期:2012-07-10 21:24:24鲜花蛋
日期:2012-07-16 15:24:59奥运会纪念徽章:拳击
日期:2012-08-07 10:54:50奥运会纪念徽章:羽毛球
日期:2012-08-21 15:55:33奥运会纪念徽章:蹦床
日期:2012-08-21 21:09:51奥运会纪念徽章:篮球
日期:2012-08-24 10:29:11奥运会纪念徽章:体操
日期:2012-09-07 16:40:00
发表于 2014-1-10 12:25 | 显示全部楼层

with x1 as (select 0 c1 from dual where 1=2),
x2 as (select 1 d1,2 d2 from dual)
Select * from x2 Where (select count(*)from x1 aa where c1 = d1) + d2 > d1;

SQL> with x1 as (select 0 c1 from dual where 1=2),
  2  x2 as (select 1 d1,2 d2 from dual)
  3  Select * from x2 Where (select count(*)from x1 aa where c1 = d1) + d2 > d1;

        D1         D2
---------- ----------
         1          2

SQL> set autot on
SQL> /

        D1         D2
---------- ----------
         1          2


Execution Plan
----------------------------------------------------------
Plan hash value: 2531106075

-----------------------------------------------------------------
| Id  | Operation        | Name | Rows  | Cost (%CPU)| Time     |
-----------------------------------------------------------------
|   0 | SELECT STATEMENT |      |     1 |     2   (0)| 00:00:01 |
|*  1 |  FILTER          |      |       |            |          |
|   2 |   FAST DUAL      |      |     1 |     2   (0)| 00:00:01 |
|   3 |   SORT AGGREGATE |      |     1 |            |          |
|*  4 |    FILTER        |      |       |            |          |
|   5 |     FAST DUAL    |      |     1 |     2   (0)| 00:00:01 |
-----------------------------------------------------------------

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

   1 - filter( (SELECT COUNT(*) FROM "SYS"."DUAL" "DUAL" WHERE NULL IS
              NOT NULL AND NULL IS NOT NULL)+2>1)
   4 - filter(NULL IS NOT NULL AND NULL IS NOT NULL)


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          0  consistent gets
          0  physical reads
          0  redo size
        407  bytes sent via SQL*Net to client
        365  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL> show rel
release 1201000100

使用道具 举报

回复
论坛徽章:
19
ITPUB十周年纪念徽章
日期:2011-11-01 16:26:292015年新春福章
日期:2015-03-04 14:53:16优秀写手
日期:2014-03-19 06:00:24马上有对象
日期:2014-02-18 16:44:082014年新春福章
日期:2014-02-18 16:44:08比亚迪
日期:2013-10-23 21:35:02ITPUB社区12周年站庆徽章
日期:2013-10-08 14:54:39茶鸡蛋
日期:2013-07-25 19:48:40灰彻蛋
日期:2013-05-24 09:42:412013年新春福章
日期:2013-02-25 14:51:24
发表于 2014-1-14 06:30 | 显示全部楼层

啥是 子查询采用了平坦化技术?

使用道具 举报

回复
认证徽章
论坛徽章:
43
现任管理团队成员
日期:2011-05-07 01:45:08ITPUB元老
日期:2012-09-12 14:50:28版主5段
日期:2014-06-11 02:21:31阿斯顿马丁
日期:2013-11-19 10:38:16祖母绿
日期:2012-11-06 12:43:12路虎
日期:2013-11-20 11:37:53雪佛兰
日期:2013-09-05 13:28:25ITPUB9周年纪念徽章
日期:2010-10-08 09:28:512015中国数据库技术大会纪念徽章
日期:2015-04-24 16:04:24秀才
日期:2015-05-26 13:32:07
发表于 2014-1-14 10:58 | 显示全部楼层
ahdong2007 发表于 2014-1-14 06:30
啥是 子查询采用了平坦化技术?

http://blog.sina.com.cn/s/blog_6f86f39a0101cbm5.html

请看这个帖子。

使用道具 举报

回复
认证徽章
论坛徽章:
58
生肖徽章2007版:马
日期:2009-11-06 23:12:33授权会员
日期:2013-01-10 14:38:592013年新春福章
日期:2013-02-25 14:51:24马自达
日期:2013-08-07 10:54:45红旗
日期:2013-08-09 13:48:48劳斯莱斯
日期:2013-09-12 15:56:37萤石
日期:2013-10-31 08:44:19优秀写手
日期:2013-12-18 09:29:13Jeep
日期:2014-01-14 10:53:432014年新春福章
日期:2014-02-18 16:43:09
发表于 2014-1-14 15:25 | 显示全部楼层
〇〇 发表于 2014-1-10 12:25
with x1 as (select 0 c1 from dual where 1=2),
x2 as (select 1 d1,2 d2 from dual)
Select * from x ...

修改1:SQL>  select * from x2 where (select count(*)+d2 from x1 aa where c1=d1) > d1;

        D1         D2
---------- ----------
          1          2

  

修改2:SQL> select * from x2 b where (select count(*) from x1 a,x2 b where a.c1=b.d1) + b.d2 > b.d1;

        D1         D2
---------- ----------
          1          2

这样是可以的。但是的确暴露了是个bug,修改隐含参数可以避免的。

r1版本就是个噱头和半成品啊。

使用道具 举报

回复
认证徽章
论坛徽章:
58
生肖徽章2007版:马
日期:2009-11-06 23:12:33授权会员
日期:2013-01-10 14:38:592013年新春福章
日期:2013-02-25 14:51:24马自达
日期:2013-08-07 10:54:45红旗
日期:2013-08-09 13:48:48劳斯莱斯
日期:2013-09-12 15:56:37萤石
日期:2013-10-31 08:44:19优秀写手
日期:2013-12-18 09:29:13Jeep
日期:2014-01-14 10:53:432014年新春福章
日期:2014-02-18 16:43:09
发表于 2014-1-14 15:27 | 显示全部楼层
〇〇 发表于 2014-1-10 12:25
with x1 as (select 0 c1 from dual where 1=2),
x2 as (select 1 d1,2 d2 from dual)
Select * from x ...

oo 写sql厉害啊 !考

使用道具 举报

回复
认证徽章
论坛徽章:
43
现任管理团队成员
日期:2011-05-07 01:45:08ITPUB元老
日期:2012-09-12 14:50:28版主5段
日期:2014-06-11 02:21:31阿斯顿马丁
日期:2013-11-19 10:38:16祖母绿
日期:2012-11-06 12:43:12路虎
日期:2013-11-20 11:37:53雪佛兰
日期:2013-09-05 13:28:25ITPUB9周年纪念徽章
日期:2010-10-08 09:28:512015中国数据库技术大会纪念徽章
日期:2015-04-24 16:04:24秀才
日期:2015-05-26 13:32:07
发表于 2014-1-14 15:38 | 显示全部楼层
本帖最后由 myth8860 于 2014-1-14 15:39 编辑
yyp2009 发表于 2014-1-14 15:27
oo 写sql厉害啊 !考

00是大师啊,oracle 12c虽然有bug,不过把子查询拉平的本意是好的,在很多场景下可以提高性能。

软件有bug很正常,国人很多时候能够容忍外国软件的bug,很多时候选择改变自己的写法来迁就一下,
往往对自己国家的软件产品吹毛求疵,让人难以接受。

使用道具 举报

回复

您需要登录后才可以回帖 登录 | 注册

本版积分规则 发表回复

TOP技术积分榜 社区积分榜 徽章 团队 统计 知识索引树 积分竞拍 文本模式 帮助
  ITPUB首页 | ITPUB论坛 | 数据库技术 | 企业信息化 | 开发技术 | 微软技术 | 软件工程与项目管理 | IBM技术园地 | 行业纵向讨论 | IT招聘 | IT文档
  ChinaUnix | ChinaUnix博客 | ChinaUnix论坛
CopyRight 1999-2011 itpub.net All Right Reserved. 北京盛拓优讯信息技术有限公司版权所有 联系我们 
京ICP备09055130号-4  北京市公安局海淀分局网监中心备案编号:11010802021510 广播电视节目制作经营许可证:编号(京)字第1149号
  
快速回复 返回顶部 返回列表