【Oracle】Oracle wrong result一則（優化器問題）

PiscesCanon發表於2018-04-03
Oracle優化
現象如下：
  1. SYS@proc> select * from v$version where rownum=1;

  3. BANNER
  4. --------------------------------------------------------------------------------
  5. Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

  7. SYS@proc> drop table test purge;

  9. Table dropped.

  11. SYS@proc> with t as (select a.*,rownum rn from dba_objects a)
  12.   2 select count(*) from t where t.rn=(select max(rn) from t);

  14.   COUNT(*)
  15. ----------
  16.          1

  18. SYS@proc> create table test as select * from dba_objects;

  20. Table created.

  22. SYS@proc> with t as (select a.*,rownum rn from test a)
  23.   2 select * from t where t.rn=(select max(rn) from t);

  25. no rows selected

先看下兩者的執行計劃：
  1. SYS@proc> set long 9999 pagesize 9999 lines 500
  2. SYS@proc> set autotrace traceonly
  3. SYS@proc> with t as (select a.*,rownum rn from dba_objects a)
  4.   2 select count(*) from t where t.rn=(select max(rn) from t);


  7. Execution Plan
  8. ----------------------------------------------------------
  9. Plan hash value: 1413014202

  11. --------------------------------------------------------------------------------------------------------------
  12. | Id  | Operation                        | Name                      | Rows  | Bytes | Cost (%CPU)| Time     |
  13. --------------------------------------------------------------------------------------------------------------
  14. |   0 | SELECT STATEMENT                 |                           |     1 |    13 |    1336 (1)| 00:00:17 |
  15. |   1 |  TEMP TABLE TRANSFORMATION       |                           |       |       |            |          |
  16. |   2 |   LOAD AS SELECT                 | SYS_TEMP_0FD9D6622_16F270 |       |       |            |          |
  17. |   3 |    COUNT                         |                           |       |       |            |          |
  18. |   4 |     VIEW                         | DBA_OBJECTS               | 86955 |    13M|     301 (1)| 00:00:04 |
  19. |   5 |      UNION-ALL                   |                           |       |       |            |          |
  20. |*  6 |       TABLE ACCESS BY INDEX ROWID| SUM$                      |     1 |     9 |       1 (0)| 00:00:01 |
  21. |*  7 |        INDEX UNIQUE SCAN         | I_SUM$_1                  |     1 |       |       0 (0)| 00:00:01 |
  22. |   8 |       TABLE ACCESS BY INDEX ROWID| OBJ$                      |     1 |    30 |       3 (0)| 00:00:01 |
  23. |*  9 |        INDEX RANGE SCAN          | I_OBJ1                    |     1 |       |       2 (0)| 00:00:01 |
  24. |* 10 |       FILTER                     |                           |       |       |            |          |
  25. |* 11 |        HASH JOIN                 |                           | 86954 |    10M|     299 (2)| 00:00:04 |
  26. |  12 |         TABLE ACCESS FULL        | USER$                     |    94 |  1598 |       3 (0)| 00:00:01 |
  27. |* 13 |         HASH JOIN                |                           | 86954 |  9001K|     296 (2)| 00:00:04 |
  28. |  14 |          INDEX FULL SCAN         | I_USER2                   |    94 |  2068 |       1 (0)| 00:00:01 |
  29. |* 15 |          TABLE ACCESS FULL       | OBJ$                      | 86954 |  7132K|     294 (1)| 00:00:04 |
  30. |  16 |        NESTED LOOPS              |                           |     1 |    29 |       2 (0)| 00:00:01 |
  31. |* 17 |         INDEX SKIP SCAN          | I_USER2                   |     1 |    20 |       1 (0)| 00:00:01 |
  32. |* 18 |         INDEX RANGE SCAN         | I_OBJ4                    |     1 |     9 |       1 (0)| 00:00:01 |
  33. |  19 |       NESTED LOOPS               |                           |     1 |   105 |       2 (0)| 00:00:01 |
  34. |  20 |        TABLE ACCESS FULL         | LINK$                     |     1 |    88 |       2 (0)| 00:00:01 |
  35. |  21 |        TABLE ACCESS CLUSTER      | USER$                     |     1 |    17 |       0 (0)| 00:00:01 |
  36. |* 22 |         INDEX UNIQUE SCAN        | I_USER#                   |     1 |       |       0 (0)| 00:00:01 |
  37. |  23 |   SORT AGGREGATE                 |                           |     1 |    13 |            |          |
  38. |* 24 |    VIEW                          |                           | 86955 |  1103K|     517 (1)| 00:00:07 |
  39. |  25 |     TABLE ACCESS FULL            | SYS_TEMP_0FD9D6622_16F270 | 86955 |    13M|     517 (1)| 00:00:07 |
  40. |  26 |     SORT AGGREGATE               |                           |     1 |    13 |            |          |
  41. |  27 |      VIEW                        |                           | 86955 |  1103K|     517 (1)| 00:00:07 |
  42. |  28 |       TABLE ACCESS FULL          | SYS_TEMP_0FD9D6622_16F270 | 86955 |    13M|     517 (1)| 00:00:07 |
  43. --------------------------------------------------------------------------------------------------------------

  45. Predicate Information (identified by operation id):
  46. ---------------------------------------------------

  48.    6 - filter(BITAND("S"."XPFLAGS",8388608)=8388608)
  49.    7 - access("S"."OBJ#"=:B1)
  50.    9 - access("EO"."OBJ#"=:B1)
  51.   10 - filter("O"."TYPE#"<>4 AND "O"."TYPE#"<>5 AND "O"."TYPE#"<>7 AND "O"."TYPE#"<>8 AND
  52.      "O"."TYPE#"<>9 AND "O"."TYPE#"<>11 AND "O"."TYPE#"<>12 AND "O"."TYPE#"<>13 AND "O"."TYPE#"<>14 AND
  53.      "O"."TYPE#"<>22 AND "O"."TYPE#"<>87 AND "O"."TYPE#"<>88 OR BITAND("U"."SPARE1",16)=0 OR
  54.      ("O"."TYPE#"=4 OR "O"."TYPE#"=5 OR "O"."TYPE#"=7 OR "O"."TYPE#"=8 OR "O"."TYPE#"=9 OR "O"."TYPE#"=10
  55.      OR "O"."TYPE#"=11 OR "O"."TYPE#"=12 OR "O"."TYPE#"=13 OR "O"."TYPE#"=14 OR "O"."TYPE#"=22 OR
  56.      "O"."TYPE#"=87) AND (SYS_CONTEXT('userenv','current_edition_name')='ORA$BASE' AND "U"."TYPE#"<>2 OR
  57.      "U"."TYPE#"=2 AND "U"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id')) OR    EXISTS
  58.      (SELECT 0 FROM SYS."USER$" "U2",SYS."OBJ$" "O2" WHERE "O2"."OWNER#"="U2"."USER#" AND "O2"."TYPE#"=88
  59.      AND "O2"."DATAOBJ#"=:B1 AND "U2"."TYPE#"=2 AND "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_
  60.      edition_id')))))
  61.   11 - access("O"."SPARE3"="U"."USER#")
  62.   13 - access("O"."OWNER#"="U"."USER#")
  63.   15 - filter("O"."TYPE#"<>10 AND "O"."NAME"<>'_NEXT_OBJECT' AND
  64.      "O"."NAME"<>'_default_auditing_options_' AND "O"."LINKNAME" IS NULL AND BITAND("O"."FLAGS",128)=0)
  65.   17 - access("U2"."TYPE#"=2 AND "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id')))
  66.        filter("U2"."TYPE#"=2 AND "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id')))
  67.   18 - access("O2"."DATAOBJ#"=:B1 AND "O2"."TYPE#"=88 AND "O2"."OWNER#"="U2"."USER#")
  68.   22 - access("L"."OWNER#"="U"."USER#")
  69.   24 - filter("T"."RN"= (SELECT MAX("RN") FROM    (SELECT /*+ CACHE_TEMP_TABLE ("T1") */ "C0"
  70.      "OWNER","C1" "OBJECT_NAME","C2" "SUBOBJECT_NAME","C3" "OBJECT_ID","C4" "DATA_OBJECT_ID","C5"
  71.      "OBJECT_TYPE","C6" "CREATED","C7" "LAST_DDL_TIME","C8" "TIMESTAMP","C9" "STATUS","C10"
  72.      "TEMPORARY","C11" "GENERATED","C12" "SECONDARY","C13" "NAMESPACE","C14" "EDITION_NAME","C15" "RN"
  73.      FROM "SYS"."SYS_TEMP_0FD9D6622_16F270" "T1") "T"))


  76. Statistics
  77. ----------------------------------------------------------
  78.     241 recursive calls
  79.        1342 db block gets
  80.        3814 consistent gets
  81.        2628 physical reads
  82.        1216 redo size
  83.     526 bytes sent via SQL*Net to client
  84.     523 bytes received via SQL*Net from client
  85.      2 SQL*Net roundtrips to/from client
  86.      15 sorts (memory)
  87.      0 sorts (disk)
  88.      1 rows processed

  90. SYS@proc> with t as (select a.*,rownum rn from test a)
  91.   2 select * from t where t.rn=(select max(rn) from t);

  93. no rows selected


  96. Execution Plan
  97. ----------------------------------------------------------
  98. Plan hash value: 1063871704

  100. ------------------------------------------------------------------------------
  101. | Id | Operation             | Name | Rows  | Bytes | Cost (%CPU)| Time     |
  102. ------------------------------------------------------------------------------
  103. |  0 | SELECT STATEMENT      |      | 71052 |    14M|     678 (1)| 00:00:09 |
  104. |* 1 |  VIEW                 |      | 71052 |    14M|     339 (1)| 00:00:05 |
  105. |  2 |   COUNT               |      |       |       |            |          |
  106. |  3 |    TABLE ACCESS FULL  | TEST | 71052 |    14M|     339 (1)| 00:00:05 |
  107. |  4 |   SORT AGGREGATE      |      |     1 |    13 |            |          |
  108. |  5 |    VIEW               |      | 71052 |   902K|     339 (1)| 00:00:05 |
  109. |  6 |     COUNT             |      |       |       |            |          |
  110. |  7 |      TABLE ACCESS FULL| TEST | 71052 |       |     339 (1)| 00:00:05 |
  111. ------------------------------------------------------------------------------

  113. Predicate Information (identified by operation id):
  114. ---------------------------------------------------

  116.    1 - filter("T"."RN"= (SELECT MAX("RN") FROM    (SELECT ROWNUM "RN"
  117.      FROM "TEST" "A") "T"))

  119. Note
  120. -----
  121.    - dynamic sampling used for this statement (level=2)


  124. Statistics
  125. ----------------------------------------------------------
  126.      45 recursive calls
  127.      0 db block gets
  128.        2756 consistent gets
  129.        2482 physical reads
  130.      0 redo size
  131.        1407 bytes sent via SQL*Net to client
  132.     512 bytes received via SQL*Net from client
  133.      1 SQL*Net roundtrips to/from client
  134.      2 sorts (memory)
  135.      0 sorts (disk)
  136.      0 rows processed
從第二個執行計劃的Predicate Information處看出，filter裡邊的T表給解析成只有一個欄位"RN"（也應該有結果出來？）。
嘗試加hint讓with as的部分放在temp表，執行結果以及執行計劃如下：

  1. SYS@proc> set autotrace off
  2. SYS@proc> with t as (select /*+MATERIALIZE*/ a.*,rownum rn from test a)
  3.   2 select count(*) from t where t.rn=(select max(rn) from t);

  5.   COUNT(*)
  6. ----------
  7.          1

  9. SYS@proc> set autotrace traceonlu
  10. Usage: SET AUTOT[RACE] {OFF | ON | TRACE[ONLY]} [EXP[LAIN]] [STAT[ISTICS]]
  11. SYS@proc> set autotrace traceonly
  12. SYS@proc> l
  13.   1 with t as (select /*+MATERIALIZE*/ a.*,rownum rn from test a)
  14.   2* select count(*) from t where t.rn=(select max(rn) from t)
  15. SYS@proc> /


  18. Execution Plan
  19. ----------------------------------------------------------
  20. Plan hash value: 340454420

  22. --------------------------------------------------------------------------------------------------------
  23. | Id | Operation                  | Name                      | Rows  | Bytes |  Cost (%CPU)| Time     |
  24. --------------------------------------------------------------------------------------------------------
  25. |  0 | SELECT STATEMENT           |                           |     1 |    13 | 1426     (1)| 00:00:18 |
  26. |  1 |  TEMP TABLE TRANSFORMATION |                           |       |       |             |          |
  27. |  2 |   LOAD AS SELECT           | SYS_TEMP_0FD9D6624_16F270 |       |       |             |          |
  28. |  3 |    COUNT                   |                           |       |       |             |          |
  29. |  4 |     TABLE ACCESS FULL      | TEST                      | 71052 |    14M| 339      (1)| 00:00:05 |
  30. |  5 |   SORT AGGREGATE           |                           |     1 |    13 |             |          |
  31. |* 6 |    VIEW                    |                           | 71052 |   902K| 543      (1)| 00:00:07 |
  32. |  7 |     TABLE ACCESS FULL      | SYS_TEMP_0FD9D6624_16F270 | 71052 |    14M| 543      (1)| 00:00:07 |
  33. |  8 |     SORT AGGREGATE         |                           |     1 |    13 |             |          |
  34. |  9 |      VIEW                  |                           | 71052 |   902K| 543      (1)| 00:00:07 |
  35. | 10 |       TABLE ACCESS FULL    | SYS_TEMP_0FD9D6624_16F270 | 71052 |    14M| 543      (1)| 00:00:07 |
  36. --------------------------------------------------------------------------------------------------------

  38. Predicate Information (identified by operation id):
  39. ---------------------------------------------------

  41.    6 - filter("T"."RN"= (SELECT MAX("RN") FROM    (SELECT /*+ CACHE_TEMP_TABLE ("T1") */ "C0"
  42.      "OWNER","C1" "OBJECT_NAME","C2" "SUBOBJECT_NAME","C3" "OBJECT_ID","C4" "DATA_OBJECT_ID","C5"
  43.      "OBJECT_TYPE","C6" "CREATED","C7" "LAST_DDL_TIME","C8" "TIMESTAMP","C9" "STATUS","C10"
  44.      "TEMPORARY","C11" "GENERATED","C12" "SECONDARY","C13" "NAMESPACE","C14" "EDITION_NAME","C15"
  45.      "RN" FROM "SYS"."SYS_TEMP_0FD9D6624_16F270" "T1") "T"))

  47. Note
  48. -----
  49.    - dynamic sampling used for this statement (level=2)


  52. Statistics
  53. ----------------------------------------------------------
  54.      12 recursive calls
  55.        1340 db block gets
  56.        3876 consistent gets
  57.        3869 physical reads
  58.     764 redo size
  59.     526 bytes sent via SQL*Net to client
  60.     523 bytes received via SQL*Net from client
  61.      2 SQL*Net roundtrips to/from client
  62.      0 sorts (memory)
  63.      0 sorts (disk)
  64.      1 rows processed
結果已經執行出來了。
另外該語句在10g是可以不用加hint就能正常執行：
  1. SYS@proc> select * from v$version where rownum=1;

  3. BANNER
  4. ----------------------------------------------------------------
  5. Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bi

  7. SYS@proc> with t as (select a.*,rownum rn from test a)
  8.   2 select count(*) from t where t.rn=(select max(rn) from t);

  10.   COUNT(*)
  11. ----------
  12.          1

這裡嘗試將11g裡邊的引數optimizer_features_enable修改成10g的，在看看執行計劃以及結果：
  1. SYS@proc> alter system set optimizer_features_enable='10.2.0.4';

  3. System altered.

  5. SYS@proc> show parameter feature

  7. NAME                 TYPE     VALUE
  8. ------------------------------------ ----------- ------------------------------
  9. optimizer_features_enable     string     10.2.0.4
  10. SYS@proc> set pagesize 9999 long 9999 lines 500
  11. SYS@proc> with t as (select a.*,rownum rn from test a)
  12.   2 select count(*) from t where t.rn=(select max(rn) from t);

  14.   COUNT(*)
  15. ----------
  16.          0

  18. SYS@proc> set autotrace traceonly
  19. SYS@proc> l
  20.   1 with t as (select a.*,rownum rn from test a)
  21.   2* select count(*) from t where t.rn=(select max(rn) from t)
  22. SYS@proc> /


  25. Execution Plan
  26. ----------------------------------------------------------
  27. Plan hash value: 4022736097

  29. -------------------------------------------------------------------------------
  30. | Id | Operation              | Name | Rows  | Bytes | Cost (%CPU)| Time     |
  31. -------------------------------------------------------------------------------
  32. |  0 | SELECT STATEMENT       |      |     1 |    13 |  678    (1)| 00:00:09 |
  33. |  1 |  SORT AGGREGATE        |      |     1 |    13 |            |          |
  34. |* 2 |   VIEW                 |      | 71052 |   902K|  339    (1)| 00:00:05 |
  35. |  3 |    COUNT               |      |       |       |            |          |
  36. |  4 |     TABLE ACCESS FULL  | TEST | 71052 |       |  339    (1)| 00:00:05 |
  37. |  5 |    SORT AGGREGATE      |      |     1 |    13 |            |          |
  38. |  6 |     VIEW               |      | 71052 |   902K|  339    (1)| 00:00:05 |
  39. |  7 |      COUNT             |      |       |       |            |          |
  40. |  8 |       TABLE ACCESS FULL| TEST | 71052 |       |  339    (1)| 00:00:05 |
  41. -------------------------------------------------------------------------------

  43. Predicate Information (identified by operation id):
  44. ---------------------------------------------------

  46.    2 - filter("T"."RN"= (SELECT MAX("RN") FROM    (SELECT "A"."OWNER"
  47.      "OWNER","A"."OBJECT_NAME" "OBJECT_NAME","A"."SUBOBJECT_NAME"
  48.      "SUBOBJECT_NAME","A"."OBJECT_ID" "OBJECT_ID","A"."DATA_OBJECT_ID"
  49.      "DATA_OBJECT_ID","A"."OBJECT_TYPE" "OBJECT_TYPE","A"."CREATED"
  50.      "CREATED","A"."LAST_DDL_TIME" "LAST_DDL_TIME","A"."TIMESTAMP"
  51.      "TIMESTAMP","A"."STATUS" "STATUS","A"."TEMPORARY"
  52.      "TEMPORARY","A"."GENERATED" "GENERATED","A"."SECONDARY"
  53.      "SECONDARY","A"."NAMESPACE" "NAMESPACE","A"."EDITION_NAME"
  54.      "EDITION_NAME",ROWNUM "RN" FROM "TEST" "A") "T"))

  56. Note
  57. -----
  58.    - dynamic sampling used for this statement (level=2)


  61. Statistics
  62. ----------------------------------------------------------
  63.      0 recursive calls
  64.      0 db block gets
  65.        2488 consistent gets
  66.        2482 physical reads
  67.      0 redo size
  68.     525 bytes sent via SQL*Net to client
  69.     523 bytes received via SQL*Net from client
  70.      2 SQL*Net roundtrips to/from client
  71.      0 sorts (memory)
  72.      0 sorts (disk)
  73.      1 rows processed
Predicate Information的filter已經沒錯了，但是還是出不來結果。
這裡不是很懂了。




來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/30174570/viewspace-2152578/，如需轉載，請註明出處，否則將追究法律責任。

相關文章