ITPub博客

首页 > 数据库 > Oracle > [20131221]12c 优化 bug.txt

[20131221]12c 优化 bug.txt

Oracle 作者:lfree 时间:2013-12-23 09:19:25 0 删除 编辑
[20131221]12c 优化 bug.txt

http://connormcdonald.wordpress.com/2013/12/20/the-challenge-of-optimization/

--仅仅自己重复测试看看!

@ver

BANNER                                                                               CON_ID
-------------------------------------------------------------------------------- ----------
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production              0

create table T ( seq number primary key, x number, y number , u number);

declare
  s number := 1;
begin
  for i in 1 .. 9 loop
    for j in i+1 .. 10 loop
      insert into T values (s,i,j, 0);
      s := s + 1;
    end loop;
  end loop;
end;
/

So we've put 45 rows into the table, the values of which are not particularly important. We are going to compare these
rows with those in another table

create table F ( g number, n number, x number, y number );
insert into F values (1,1,3,4);
insert into F values (1,1,5,7);
insert into F values (1,1,7,8);
commit ;
select * from F;

         G          N          X          Y
---------- ---------- ---------- ----------
         1          1          3          4
         1          1          5          7
         1          1          7          8

SCOTT@test01p> select x from F where g = 1 union all select y from F where g = 1;
         X
----------
         3
         5
         7
         4
         7
         8

6 rows selected.


select seq,x,y
from T
where u = 0
and x not in ( 3,5,7,4,7,8)
and y not in ( 3,5,7,4,7,8);

       SEQ          X          Y
---------- ---------- ----------
         1          1          2
         5          1          6
         8          1          9
         9          1         10
        13          2          6
        16          2          9
        17          2         10
        38          6          9
        39          6         10
        45          9         10

10 rows selected.

SCOTT@test01p> @dpc '' ''
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------
SQL_ID  6zuy1wa9dpa99, child number 0
-------------------------------------
select seq,x,y from T where u = 0 and x not in ( 3,5,7,4,7,8) and y not
in ( 3,5,7,4,7,8)

Plan hash value: 1601196873

--------------------------------------------------------
| Id  | Operation         | Name | E-Rows | Cost (%CPU)|
--------------------------------------------------------
|   0 | SELECT STATEMENT  |      |        |     3 (100)|
|*  1 |  TABLE ACCESS FULL| T    |     10 |     3   (0)|
--------------------------------------------------------

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

   1 - filter(("U"=0 AND "X"<>3 AND "X"<>5 AND "X"<>7 AND "X"<>4 AND
              "X"<>8 AND "Y"<>3 AND "Y"<>5 AND "Y"<>7 AND "Y"<>4 AND "Y"<>8))

--执行如下结果应该一样.
select seq,x,y
from T
where u = 0
and x not in ( select x from F where g = 1 union all select y from F where g = 1)
and y not in ( select x from F where g = 1 union all select y from F where g = 1);

       SEQ          X          Y
---------- ---------- ----------
         1          1          2
         2          1          3
         3          1          4
         4          1          5
         5          1          6
         6          1          7
         7          1          8
         8          1          9
         9          1         10
        10          2          3
        11          2          4
        12          2          5
        13          2          6
        14          2          7
        15          2          8
        16          2          9
        17          2         10
        36          6          7
        37          6          8
        38          6          9
        39          6         10
        45          9         10

22 rows selected.
--BUG?

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------
SQL_ID  fawwqt2yn7sb7, child number 0
-------------------------------------
select seq,x,y from T where u = 0 and x not in ( select x from F where
g = 1 union all select y from F where g = 1) and y not in ( select x
from F where g = 1 union all select y from F where g = 1)

Plan hash value: 2519685722

----------------------------------------------------------
| Id  | Operation           | Name | E-Rows | Cost (%CPU)|
----------------------------------------------------------
|   0 | SELECT STATEMENT    |      |        |    11 (100)|
|*  1 |  FILTER             |      |        |            |
|*  2 |   TABLE ACCESS FULL | T    |     45 |     3   (0)|
|   3 |   UNION-ALL         |      |        |            |
|*  4 |    TABLE ACCESS FULL| F    |      3 |     3   (0)|
|*  5 |    TABLE ACCESS FULL| F    |      3 |     3   (0)|
----------------------------------------------------------

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

   1 - filter( IS NULL)
   2 - filter("U"=0)
   4 - filter(("G"=1 AND LNNVL("X"<>:B1)))
   5 - filter(("G"=1 AND LNNVL("Y"<>:B1)))


--x,y的not前后顺序对调。
select seq,x,y
from T
where u = 0
and y not in ( select x from F where g = 1 union all select y from F where g = 1)
and x not in ( select x from F where g = 1 union all select y from F where g = 1);

       SEQ          X          Y
---------- ---------- ----------
         1          1          2
         5          1          6
         8          1          9
         9          1         10
        13          2          6
        16          2          9
        17          2         10
        20          3          6
        23          3          9
        24          3         10
        26          4          6
        29          4          9
        30          4         10
        31          5          6
        34          5          9
        35          5         10
        38          6          9
        39          6         10
        41          7          9
        42          7         10
        43          8          9
        44          8         10
        45          9         10

23 rows selected.

--多了1行,而且结果集合相差很远。

select seq,x,y
from T
where u = 0
and y not in ( select x from F where g = 1 union all select y from F where g = 1)
and x not in ( select x from F where g = 1 union all select y from F where g = 1)
minus
select seq,x,y
from T
where u = 0
and x not in ( select x from F where g = 1 union all select y from F where g = 1)
and y not in ( select x from F where g = 1 union all select y from F where g = 1)

       SEQ          X          Y
---------- ---------- ----------
        20          3          6
        23          3          9
        24          3         10
        26          4          6
        29          4          9
        30          4         10
        31          5          6
        34          5          9
        35          5         10
        41          7          9
        42          7         10
        43          8          9
        44          8         10

13 rows selected.

--乱了套了!
--仔细观察:
select seq,x,y
from T
where u = 0
and x not in ( select x from F where g = 1 union all select y from F where g = 1);

       SEQ          X          Y
---------- ---------- ----------
         1          1          2
         2          1          3
         3          1          4
         4          1          5
         5          1          6
         6          1          7
         7          1          8
         8          1          9
         9          1         10
        10          2          3
        11          2          4
        12          2          5
        13          2          6
        14          2          7
        15          2          8
        16          2          9
        17          2         10
        36          6          7
        37          6          8
        38          6          9
        39          6         10
        45          9         10

22 rows selected.

--与下面的结果一致。
--select seq,x,y
--from T
--where u = 0
--and x not in ( select x from F where g = 1 union all select y from F where g = 1)
--and y not in ( select x from F where g = 1 union all select y from F where g = 1);

select seq,x,y
from T
where u = 0
and y not in ( select x from F where g = 1 union all select y from F where g = 1);

       SEQ          X          Y
---------- ---------- ----------
         1          1          2
         5          1          6
         8          1          9
         9          1         10
        13          2          6
        16          2          9
        17          2         10
        20          3          6
        23          3          9
        24          3         10
        26          4          6
        29          4          9
        30          4         10
        31          5          6
        34          5          9
        35          5         10
        38          6          9
        39          6         10
        41          7          9
        42          7         10
        43          8          9
        44          8         10
        45          9         10

23 rows selected.

--与下面的结果一致。
--select seq,x,y
--from T
--where u = 0
--and y not in ( select x from F where g = 1 union all select y from F where g = 1)
--and x not in ( select x from F where g = 1 union all select y from F where g = 1);


select seq,x,y
from T
where u = 0
and x not in ( select x from F where g = 1)
and x not in ( select y from F where g = 1)
and y not in ( select x from F where g = 1)
and y not in ( select y from F where g = 1)

       SEQ          X          Y
---------- ---------- ----------
        45          9         10
         9          1         10
         1          1          2
         8          1          9
         5          1          6
        17          2         10
        16          2          9
        13          2          6
        39          6         10
        38          6          9

10 rows selected.

--这回正确了,但是注意输出的seq顺序!
SCOTT@test01p> @dpc '' ''
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------
SQL_ID  81aq0nf2b49c8, child number 0
-------------------------------------
select seq,x,y from T where u = 0 and x not in ( select x from F where
g = 1) and x not in ( select y from F where g = 1) and y not in (
select x from F where g = 1) and y not in ( select y from F where g = 1)

Plan hash value: 4061876635

---------------------------------------------------------------------------------------
| Id  | Operation             | Name | E-Rows | Cost (%CPU)|  OMem |  1Mem | Used-Mem |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |      |        |    15 (100)|       |       |          |
|*  1 |  HASH JOIN ANTI NA    |      |     45 |    15   (0)|  1696K|  1696K| 1031K (0)|
|*  2 |   HASH JOIN ANTI NA   |      |     45 |    12   (0)|  1696K|  1696K| 1168K (0)|
|*  3 |    HASH JOIN ANTI NA  |      |     45 |     9   (0)|  1696K|  1696K|  925K (0)|
|*  4 |     HASH JOIN ANTI NA |      |     45 |     6   (0)|  1696K|  1696K| 1063K (0)|
|*  5 |      TABLE ACCESS FULL| T    |     45 |     3   (0)|       |       |          |
|*  6 |      TABLE ACCESS FULL| F    |      3 |     3   (0)|       |       |          |
|*  7 |     TABLE ACCESS FULL | F    |      3 |     3   (0)|       |       |          |
|*  8 |    TABLE ACCESS FULL  | F    |      3 |     3   (0)|       |       |          |
|*  9 |   TABLE ACCESS FULL   | F    |      3 |     3   (0)|       |       |          |
---------------------------------------------------------------------------------------

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

   1 - access("X"="X")
   2 - access("X"="Y")
   3 - access("Y"="X")
   4 - access("Y"="Y")
   5 - filter("U"=0)
   6 - filter("G"=1)
   7 - filter("G"=1)
   8 - filter("G"=1)
   9 - filter("G"=1)

--执行计划发生了很大变化!使用hash join.


总结:
--看来oracle的版本XX.1.YY的版本最好不再生产系统使用!

--补充在11GR2下测试:
SCOTT@test> @ver
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production


SCOTT@test> @dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  fawwqt2yn7sb7, child number 0
-------------------------------------
select seq,x,y from T where u = 0 and x not in ( select x from F where
g = 1 union all select y from F where g = 1) and y not in ( select x
from F where g = 1 union all select y from F where g = 1)

Plan hash value: 2379973572

----------------------------------------------------------
| Id  | Operation           | Name | E-Rows | Cost (%CPU)|
----------------------------------------------------------
|   0 | SELECT STATEMENT    |      |        |    20 (100)|
|*  1 |  FILTER             |      |        |            |
|*  2 |   TABLE ACCESS FULL | T    |     45 |     3   (0)|
|   3 |   UNION-ALL         |      |        |            |
|*  4 |    TABLE ACCESS FULL| F    |      3 |     3   (0)|
|*  5 |    TABLE ACCESS FULL| F    |      3 |     3   (0)|
|   6 |   UNION-ALL         |      |        |            |
|*  7 |    TABLE ACCESS FULL| F    |      3 |     3   (0)|
|*  8 |    TABLE ACCESS FULL| F    |      3 |     3   (0)|
----------------------------------------------------------

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

   1 - filter(( IS NULL AND  IS NULL))
   2 - filter("U"=0)
   4 - filter(("G"=1 AND LNNVL("X"<>:B1)))
   5 - filter(("G"=1 AND LNNVL("Y"<>:B1)))
   7 - filter(("G"=1 AND LNNVL("X"<>:B1)))
   8 - filter(("G"=1 AND LNNVL("Y"<>:B1)))

--很明显12c缺少
|   6 |   UNION-ALL         |      |        |            |
|*  7 |    TABLE ACCESS FULL| F    |      3 |     3   (0)|
|*  8 |    TABLE ACCESS FULL| F    |      3 |     3   (0)|

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

请登录后发表评论 登录
全部评论
熟悉oracle相关技术,擅长sql优化,rman备份与恢复,熟悉linux shell编程。

注册时间:2008-01-03

  • 博文量
    2674
  • 访问量
    6433349