[20131221]12c 优化 bug.txt

[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)|

时间: 2024-09-12 21:32:16

[20131221]12c 优化 bug.txt的相关文章

[20130809]12c Clustering Factor.txt

[20130809]12c Clustering Factor.txt 以前在11G以前,如果使用assm,表的CF经常会很大,即使你插入的像顺序号这样的字段,由于多个会话同时操作,插入的数据分布的不同的块中,以顺序号为索引的CF也会变得很大,甚至接近记录的数量.这个在<基于成本的优化>里面也有介绍. 但是在12g可以设置一个参数改善这种情况,做一些测试看看. 参考了Richard Foote大师的blog:http://richardfoote.wordpress.com/2013/05/0

优化Robots.txt:扬己之长避己之短

Robots.txt文件是一个简单的TXT文本,但是专注网站建设及网站优化的Seoer们都清楚它的重要性,它的存在可以将不希望搜索引擎抓取的页面屏蔽起来,也可以像是一张地图一样为蜘蛛引路指航.当蜘蛛爬行到一个站点时,首先访问的便是是否存在Robots.txt文件,然后按照内容中的指引来进行索引访问,如果文件不存在的话那么就按照页面中的链接进行顺序的访问.因此我们可以利用它来屏蔽一些不需要搜索引擎要索引的目录,或者将网站地图在Robots.txt中描述引导蜘蛛爬行,这样对于网站安全性上或者节省服务

[20170816]Join Elimination Bug.txt

[20170816]Join Elimination Bug.txt https://jonathanlewis.wordpress.com/2017/08/14/join-elimination-bug/ --//自己重复测试1次. 1.环境: SCOTT@book> @ &r/ver1 PORT_STRING                    VERSION        BANNER ------------------------------ -------------- ---

[20170625]12c Extended statistics.txt

[20170625]12c Extended statistics.txt --//别人的系统12c,awr报表出现大量调用执行如下sql语句. select default$ from col$ where rowid=:1; --//google看了一下,问题出在Extended statistics的问题,12c 会自动收集扩展统计信息.找到如下链接: --//https://blog.dbi-services.com/sql-plan-directives-strike-again/ 1

[20151209]一条sql语句的优化(续).txt

[20151209]一条sql语句的优化(续).txt http://blog.itpub.net/267265/viewspace-1852195/ --上次提到其中1条sql语句: 1.环境: SYSTEM@192.168.99.105:1521/dbcn> @ver1 PORT_STRING                    VERSION        BANNER ------------------------------ -------------- -------------

[20160910]12c sqlldr express.txt

[20160910]12c sqlldr express.txt --Oracle 12c introduces Sql*Loader Express features, which allow users to run sqlldr with minimum configuration.   --通过例子来说明: SCOTT@test01p> @ ver1 PORT_STRING                    VERSION        BANNER                 

[2016026]12c lateral语法.txt

[2016026]12c lateral语法.txt 12c 支持一种LATERAL的写法,例子: SELECT e1.*, e3.avg_sal   FROM scott.emp e1       ,LATERAL (SELECT AVG (e2.sal) avg_sal                   FROM scott.emp e2                  WHERE e1.deptno != e2.deptno) e3; -- 显示emp每行,同时显示其它不是本部门的平均

[20140210]一条sql语句的优化(11g).txt

  [20140210]一条sql语句的优化(11g).txt 今天下午看生产系统数据库,无意中发现一个错误,同时优化也有点小问题,写一个测试脚本. 1.建立测试环境: SCOTT@test> @ver BANNER -------------------------------------------------------------------------------- Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 -

[20140116]视图?隐式转换?sql优化问题.txt

[20140116]视图?隐式转换?sql优化问题.txt 最近一直在优化单位的垃圾数据库,这个数据库可以讲是一个垃圾工程.在有优化的过程遇到视图中存在隐式转化问题,在我的测试环境模 拟出来,提出解决方案: 1.建立测试环境: SCOTT@test> @ver BANNER -------------------------------------------------------------------------------- Oracle Database 11g Enterprise