ITPub博客
首页 博客 专栏 学院 社区 IT新媒体评选
发博

首页 > Linux操作系统 > Linux操作系统 > ITPUB SQL大赛之BUG（一）

ITPUB SQL大赛之BUG（一）

原创 Linux操作系统 作者：yangtingkun 时间：2011-03-30 23:13:46 0 删除 编辑

由于SQL大赛题目相对比较困难，不但需要使用大量的特性，且SQL实现十分复杂，一般运行时间也会比较长，这些因素导致碰到bug的几率直线上升。这里介绍SQL大赛期间碰到的几个bug。

这一篇介绍11.2上优化器处理树形查询的问题。

 

 

下面这个SQL在10.2中运行没有问题：

SQL> var m number
SQL> var n number
SQL> exec :m := 2

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.01
SQL> exec :n := 5

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.00
SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bi
PL/SQL Release 10.2.0.3.0 - Production
CORE    10.2.0.3.0      Production
TNS for Linux: Version 10.2.0.3.0 - Production
NLSRTL Version 10.2.0.3.0 - Production

Elapsed: 00:00:00.02
SQL> with i as
  2  (select rownum i from dual connect by rownum <= :n),
  3  j as
  4  (select rownum j from dual connect by rownum <= :n),
  5  position as
  6  (select i, j
  7  from i, j),
  8  b as
  9  (select rownum - 1 b from dual connect by rownum <= 2),
 10  b_line as
 11  (select replace(sys_connect_by_path(b, ','), ',', '') line
 12  from b
 13  where connect_by_isleaf = 1
 14  connect by level <= :n),
 15  lines as
 16  (select line from b_line
 17  where instr(line, 1, 1, :m) > 0
 18  and instr(line, 0, 1, :n - :m) > 0),
 19  lines_result as
 20  (select replace(sys_connect_by_path(line, ','), ',', '') result
 21  from lines
 22  where connect_by_isleaf = 1
 23  connect by level <= :n)
 24  select :m M, :n N, count(*) AllCnt
 25  from (
 26  select result
 27  from (
 28  select result, j, (j-i) l, (j+i) r, sum(substr(result, (i-1)*:n + j, 1)) c
 29  from lines_result, position
 30  group by grouping sets ((result, j), (result, (j-i)), (result, (j+i))))
 31  group by result
 32  having max(case when l is null and r is null then c end) = :m
 33  and max(case when j is null and l is null then c end) = :m
 34  and max(case when j is null and r is null then c end) = :m
 35  );

         M          N     ALLCNT
---------- ---------- ----------
         2          5         92

Elapsed: 00:00:16.91

而在11.2中则存在问题：

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE    11.2.0.1.0      Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

已用时间:  00: 00: 00.02

SQL> with i as
  2  (select rownum i from dual connect by rownum <= :n),
  3  j as
  4  (select rownum j from dual connect by rownum <= :n),
  5  position as
  6  (select i, j
  7  from i, j),
  8  b as
  9  (select rownum - 1 b from dual connect by rownum <= 2),
 10  b_line as
 11  (select replace(sys_connect_by_path(b, ','), ',', '') line
 12  from b
 13  where connect_by_isleaf = 1
 14  connect by level <= :n),
 15  lines as
 16  (select line from b_line
 17  where instr(line, 1, 1, :m) > 0
 18  and instr(line, 0, 1, :n - :m) > 0),
 19  lines_result as
 20  (select replace(sys_connect_by_path(line, ','), ',', '') result
 21  from lines
 22  where connect_by_isleaf = 1
 23  connect by level <= :n)
 24  select :m M, :n N, count(*) AllCnt
 25  from (
 26  select result
 27  from (
 28  select result, j, (j-i) l, (j+i) r, sum(substr(result, (i-1)*:n + j, 1)) c
 29  from lines_result, position
 30  group by grouping sets ((result, j), (result, (j-i)), (result, (j+i))))
 31  group by result
 32  having max(case when l is null and r is null then c end) = :m
 33  and max(case when j is null and l is null then c end) = :m
 34  and max(case when j is null and r is null then c end) = :m
 35  );

         M          N     ALLCNT
---------- ---------- ----------
         2          5        931

已用时间:  00: 01: 29.87

检查执行计划，发现10.2中执行计划为：

---------------------------------------------------
| Id  | Operation                                 |
---------------------------------------------------
|   0 | SELECT STATEMENT                          |
|   1 |  TEMP TABLE TRANSFORMATION                |
|   2 |   LOAD AS SELECT                          |
|   3 |    COUNT                                  |
|*  4 |     CONNECT BY WITHOUT FILTERING          |
|   5 |      FAST DUAL                            |
|   6 |   LOAD AS SELECT                          |
|*  7 |    VIEW                                   |
|*  8 |     FILTER                                |
|*  9 |      CONNECT BY WITHOUT FILTERING         |
|  10 |       COUNT                               |
|  11 |        VIEW                               |
|  12 |         TABLE ACCESS FULL                 |
|  13 |   SORT AGGREGATE                          |
|  14 |    VIEW                                   |
|* 15 |     FILTER                                |
|  16 |      HASH GROUP BY                        |
|  17 |       VIEW                                |
|  18 |        TEMP TABLE TRANSFORMATION          |
|  19 |         LOAD AS SELECT                    |
|  20 |          MERGE JOIN CARTESIAN             |
|  21 |           MERGE JOIN CARTESIAN            |
|  22 |            VIEW                           |
|* 23 |             FILTER                        |
|* 24 |              CONNECT BY WITHOUT FILTERING |
|  25 |               TABLE ACCESS FULL           |
|  26 |            BUFFER SORT                    |
|  27 |             VIEW                          |
|  28 |              COUNT                        |
|* 29 |               CONNECT BY WITHOUT FILTERING|
|  30 |                FAST DUAL                  |
|  31 |           BUFFER SORT                     |
|  32 |            VIEW                           |
|  33 |             COUNT                         |
|* 34 |              CONNECT BY WITHOUT FILTERING |
|  35 |               FAST DUAL                   |
|  36 |         LOAD AS SELECT                    |
|  37 |          HASH GROUP BY                    |
|  38 |           TABLE ACCESS FULL               |
|  39 |         LOAD AS SELECT                    |
|  40 |          HASH GROUP BY                    |
|  41 |           TABLE ACCESS FULL               |
|  42 |         LOAD AS SELECT                    |
|  43 |          HASH GROUP BY                    |
|  44 |           TABLE ACCESS FULL               |
|  45 |         VIEW                              |
|  46 |          TABLE ACCESS FULL                |
---------------------------------------------------

而11.2的执行计划为：

-------------------------------------------------------
| Id  | Operation                                     |
-------------------------------------------------------
|   0 | SELECT STATEMENT                              |
|   1 |  SORT AGGREGATE                               |
|   2 |   VIEW                                        |
|*  3 |    FILTER                                     |
|   4 |     HASH GROUP BY                             |
|   5 |      VIEW                                     |
|   6 |       TEMP TABLE TRANSFORMATION               |
|   7 |        LOAD AS SELECT                         |
|   8 |         MERGE JOIN CARTESIAN                  |
|   9 |          MERGE JOIN CARTESIAN                 |
|  10 |           VIEW                                |
|* 11 |            FILTER                             |
|* 12 |             CONNECT BY WITHOUT FILTERING      |
|  13 |              VIEW                             |
|* 14 |               FILTER                          |
|* 15 |                CONNECT BY WITHOUT FILTERING   |
|  16 |                 VIEW                          |
|  17 |                  COUNT                        |
|* 18 |                   CONNECT BY WITHOUT FILTERING|
|  19 |                    FAST DUAL                  |
|  20 |           BUFFER SORT                         |
|  21 |            VIEW                               |
|  22 |             COUNT                             |
|* 23 |              CONNECT BY WITHOUT FILTERING     |
|  24 |               FAST DUAL                       |
|  25 |          BUFFER SORT                          |
|  26 |           VIEW                                |
|  27 |            COUNT                              |
|* 28 |             CONNECT BY WITHOUT FILTERING      |
|  29 |              FAST DUAL                        |
|  30 |        LOAD AS SELECT                         |
|  31 |         HASH GROUP BY                         |
|  32 |          TABLE ACCESS FULL                    |
|  33 |        LOAD AS SELECT                         |
|  34 |         HASH GROUP BY                         |
|  35 |          TABLE ACCESS FULL                    |
|  36 |        LOAD AS SELECT                         |
|  37 |         HASH GROUP BY                         |
|  38 |          TABLE ACCESS FULL                    |
|  39 |        VIEW                                   |
|  40 |         TABLE ACCESS FULL                     |
-------------------------------------------------------

显然是11g的CBO在尝试优化树形查询的时候出现了错误，解决方法也比较简单，可以利用ROWNUM固定结果集的特性，避免11g的CBO优化执行计划：

SQL> SELECT * FROM V$VERSION;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE    11.2.0.1.0      Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

已用时间:  00: 00: 00.00
SQL> with i as
  2  (select rownum i from dual connect by rownum <= :n),
  3  j as
  4  (select rownum j from dual connect by rownum <= :n),
  5  position as
  6  (select i, j
  7  from i, j),
  8  b as
  9  (select rownum - 1 b from dual connect by rownum <= 2),
 10  b_line as
 11  (select replace(sys_connect_by_path(b, ','), ',', '') line
 12  from b
 13  where connect_by_isleaf = 1
 14  connect by level <= :n),
 15  lines as
 16  (select rownum, line from b_line
 17  where instr(line, 1, 1, :m) > 0
 18  and instr(line, 0, 1, :n - :m) > 0),
 19  lines_result as
 20  (select replace(sys_connect_by_path(line, ','), ',', '') result
 21  from lines
 22  where connect_by_isleaf = 1
 23  connect by level <= :n)
 24  select :m M, :n N, count(*) AllCnt
 25  from (
 26  select result
 27  from (
 28  select result, j, (j-i) l, (j+i) r, sum(substr(result, (i-1)*:n + j, 1)) c
 29  from lines_result, position
 30  group by grouping sets ((result, j), (result, (j-i)), (result, (j+i))))
 31  group by result
 32  having max(case when l is null and r is null then c end) = :m
 33  and max(case when j is null and l is null then c end) = :m
 34  and max(case when j is null and r is null then c end) = :m
 35  );

         M          N     ALLCNT
---------- ---------- ----------
         2          5         92

已用时间:  00: 00: 25.12

除了利用ROWNUM之外，还可以使用OPTIMIZER_FEATURES_ENABLE提示，或者在会话级或实例级设置OPTIMIZER_FEATURES_ENABLE参数的方式，将优化器属性降低到11.1.0.7或以下的版本。

 

来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/4227/viewspace-691346/ ，如需转载，请注明出处，否则将追究法律责任。

0
0
上一篇： ITPUB SQL大赛第二期（二）
下一篇： 流高级队列用户手册总结
请登录后发表评论 登录
全部评论
yangtingkun
暂无介绍

注册时间：2007-12-29

  • 博文量
    1964
  • 访问量
    11076920

最新文章

支持我们 作者招募 用户协议 FAQ Contact Us 编辑准则

北京盛拓优讯信息技术有限公司. 版权所有  京ICP备16024965号-8 北京市公安局海淀分局网监中心备案编号：11010802021510 niuxiaotong@pcpop.com 17352615567 未成年人举报专区

广播电视节目制作经营许可证(京) 字第1234号 中国互联网协会会员

违法和不良信息举报电话:18101376593