12c Subquery Bug

于 2014-01-10 12:28:58 发布
阅读量98 收藏
点赞数
文章标签： 数据库
本文探讨了SQL查询在不同Oracle版本中的性能表现，特别是针对特定查询在多个版本下的执行计划、性能指标和优化策略。通过对比11gr2、12cr1等版本的查询结果，揭示了查询优化、性能瓶颈以及版本更新对查询效率的影响。

摘要生成于 C知道 ，由 DeepSeek-R1 满血版支持， 前往体验 >

准备测试数据：
Create table x1(c1 int);

Create table x2(d1 int, d2 int);

Insert into x2 values(1, 2);

Commit;

10205版本：
SQL> select * from x2 where (select   count(*) from x1 aa where c1=d1) > d1 - d2;

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


 Execution Plan
 ----------------------------------------------------------
 Plan hash value: 1543467908

 ----------------------------------------------------------------------------
 | 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 by operation id):
 ---------------------------------------------------

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

 Note
 -----
    - dynamic sampling used for this statement


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

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

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


 Execution Plan
 ----------------------------------------------------------
 Plan hash value: 1543467908

 ----------------------------------------------------------------------------
 | 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 by operation id):
 ---------------------------------------------------

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

 Note
 -----
    - dynamic sampling used for this statement


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

11gr2版本：
SQL> select * from x2 where (select   count(*) from x1 aa where c1=d1) + d2 > d1;

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


 Execution Plan
 ----------------------------------------------------------
 Plan hash value: 1543467908

 ----------------------------------------------------------------------------
 | 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 by operation id):
 ---------------------------------------------------

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

 Note
 -----
    - dynamic sampling used for this statement (level=2)


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

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

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


 Execution Plan
 ----------------------------------------------------------
 Plan hash value: 1543467908

 ----------------------------------------------------------------------------
 | 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 by operation id):
 ---------------------------------------------------

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

 Note
 -----
    - dynamic sampling used for this statement (level=2)


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


 12cr1版本：

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

 no rows selected


 Execution Plan
 ----------------------------------------------------------
 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 by operation id):
 ---------------------------------------------------

    1 - access("ITEM_0"="D1")
        filter("D1"

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


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

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

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


 Execution Plan
 ----------------------------------------------------------
 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 by operation 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)


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

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

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


 Execution Plan
 ----------------------------------------------------------
 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 by operation id):
 ---------------------------------------------------

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

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


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

 

把sql规范修改后，这个问题可以避免，具体修改如下：

原来的sql：

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

no rows selected

修改后：

修改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

但是无论怎么修改避免，总是暴露了12c版本的bug!!
这个bug可以如下进行修改避免：

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

no rows selected

Elapsed: 00:00:00.01

Execution Plan
----------------------------------------------------------
Plan hash value: 1144025758

--------------------------------------------------------------------------------
| Id  | Operation            | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |         |     1 |    32 |     4   (0)| 00:00:01 |
|*  1 |  HASH JOIN           |         |     1 |    32 |     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 |     6 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------------

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

   1 - access("ITEM_0"="D1")
       filter("D1"


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

SQL>  alter session set "_unnest_subquery" = false;

Session altered.

Elapsed: 00:00:00.00
SQL>  alter system set optimizer_features_enable='11.2.0.3' scope=memory;

System altered.

Elapsed: 00:00:00.02
SQL>  alter session set "_fix_control" = '12999577:0';

Session altered.

Elapsed: 00:00:00.01
SQL>  alter session set "_optimizer_squ_bottomup"= FALSE;

Session altered.

Elapsed: 00:00:00.01
SQL> select * from x2 where (select   count(*) from x1 aa where c1=d1) + d2 > d1;

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

Elapsed: 00:00:00.01

Execution Plan
----------------------------------------------------------
Plan hash value: 920484371

----------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |     1 |     6 |     4   (0)| 00:00:01 |
|*  1 |  FILTER             |      |       |       |            |          |
|   2 |   TABLE ACCESS FULL | X2   |     1 |     6 |     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 by operation id):
---------------------------------------------------

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


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

修改隐含参数后，这个问题可以避免。
这个bug目前没有补丁，oracle正在开发此补丁。估计不时就会发布。对于此bug信息参考如下：

Bug 18066615 : WRONG RESULTS ON 11.2.0.4&&12.1 VS. 11.2.0.3

 

 

 

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

转载于:http://blog.itpub.net/13750068/viewspace-1069208/

cppg67031
关注
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值