ITPUB SQL大赛之BUG（一）

最新推荐文章于 2021-04-05 11:44:37 发布

ckawt40802

最新推荐文章于 2021-04-05 11:44:37 发布

阅读量81

点赞数

文章标签：数据库

由于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;

已用时间: 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/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/4227/viewspace-691346/

ckawt40802

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
ITPUB SQL大赛之BUG（一）

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

扫一扫