ITPub博客

首页 > Linux操作系统 > Linux操作系统 > ITPUB SQL大赛之BUG(一)

ITPUB SQL大赛之BUG(一)

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

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

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

 

 

下面这个SQL10.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                     |
-------------------------------------------------------

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

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博客 ” ,链接:http://blog.itpub.net/4227/viewspace-691346/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
暂无介绍

注册时间:2007-12-29

  • 博文量
    1954
  • 访问量
    10634731