【Oracle】Oracle wrong result一則(優化器問題)

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

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

  5. SYS@proc> drop table test purge;

  6. Table dropped.

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

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

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

  13. Table created.

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

  16. 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);


  5. Execution Plan
  6. ----------------------------------------------------------
  7. Plan hash value: 1413014202

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

  41. Predicate Information (identified by operation id):
  42. ---------------------------------------------------

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


  69. Statistics
  70. ----------------------------------------------------------
  71.     241 recursive calls
  72.        1342 db block gets
  73.        3814 consistent gets
  74.        2628 physical reads
  75.        1216 redo size
  76.     526 bytes sent via SQL*Net to client
  77.     523 bytes received via SQL*Net from client
  78.      2 SQL*Net roundtrips to/from client
  79.      15 sorts (memory)
  80.      0 sorts (disk)
  81.      1 rows processed

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

  84. no rows selected


  85. Execution Plan
  86. ----------------------------------------------------------
  87. Plan hash value: 1063871704

  88. ------------------------------------------------------------------------------
  89. | Id | Operation             | Name | Rows  | Bytes | Cost (%CPU)| Time     |
  90. ------------------------------------------------------------------------------
  91. |  0 | SELECT STATEMENT      |      | 71052 |    14M|     678 (1)| 00:00:09 |
  92. |* 1 |  VIEW                 |      | 71052 |    14M|     339 (1)| 00:00:05 |
  93. |  2 |   COUNT               |      |       |       |            |          |
  94. |  3 |    TABLE ACCESS FULL  | TEST | 71052 |    14M|     339 (1)| 00:00:05 |
  95. |  4 |   SORT AGGREGATE      |      |     1 |    13 |            |          |
  96. |  5 |    VIEW               |      | 71052 |   902K|     339 (1)| 00:00:05 |
  97. |  6 |     COUNT             |      |       |       |            |          |
  98. |  7 |      TABLE ACCESS FULL| TEST | 71052 |       |     339 (1)| 00:00:05 |
  99. ------------------------------------------------------------------------------

  100. Predicate Information (identified by operation id):
  101. ---------------------------------------------------

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

  104. Note
  105. -----
  106.    - dynamic sampling used for this statement (level=2)


  107. Statistics
  108. ----------------------------------------------------------
  109.      45 recursive calls
  110.      0 db block gets
  111.        2756 consistent gets
  112.        2482 physical reads
  113.      0 redo size
  114.        1407 bytes sent via SQL*Net to client
  115.     512 bytes received via SQL*Net from client
  116.      1 SQL*Net roundtrips to/from client
  117.      2 sorts (memory)
  118.      0 sorts (disk)
  119.      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);

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

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


  14. Execution Plan
  15. ----------------------------------------------------------
  16. Plan hash value: 340454420

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

  32. Predicate Information (identified by operation id):
  33. ---------------------------------------------------

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

  39. Note
  40. -----
  41.    - dynamic sampling used for this statement (level=2)


  42. Statistics
  43. ----------------------------------------------------------
  44.      12 recursive calls
  45.        1340 db block gets
  46.        3876 consistent gets
  47.        3869 physical reads
  48.     764 redo size
  49.     526 bytes sent via SQL*Net to client
  50.     523 bytes received via SQL*Net from client
  51.      2 SQL*Net roundtrips to/from client
  52.      0 sorts (memory)
  53.      0 sorts (disk)
  54.      1 rows processed
結果已經執行出來了。
另外該語句在10g是可以不用加hint就能正常執行:
  1. SYS@proc> select * from v$version where rownum=1;

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

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

  7.   COUNT(*)
  8. ----------
  9.          1

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

  2. System altered.

  3. SYS@proc> show parameter feature

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

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

  13. SYS@proc> set autotrace traceonly
  14. SYS@proc> l
  15.   1 with t as (select a.*,rownum rn from test a)
  16.   2* select count(*) from t where t.rn=(select max(rn) from t)
  17. SYS@proc> /


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

  21. -------------------------------------------------------------------------------
  22. | Id | Operation              | Name | Rows  | Bytes | Cost (%CPU)| Time     |
  23. -------------------------------------------------------------------------------
  24. |  0 | SELECT STATEMENT       |      |     1 |    13 |  678    (1)| 00:00:09 |
  25. |  1 |  SORT AGGREGATE        |      |     1 |    13 |            |          |
  26. |* 2 |   VIEW                 |      | 71052 |   902K|  339    (1)| 00:00:05 |
  27. |  3 |    COUNT               |      |       |       |            |          |
  28. |  4 |     TABLE ACCESS FULL  | TEST | 71052 |       |  339    (1)| 00:00:05 |
  29. |  5 |    SORT AGGREGATE      |      |     1 |    13 |            |          |
  30. 6 |     VIEW               |      | 71052 |   902K|  339    (1)| 00:00:05 |
  31. |  7 |      COUNT             |      |       |       |            |          |
  32. 8 |       TABLE ACCESS FULL| TEST | 71052 |       |  339    (1)| 00:00:05 |
  33. -------------------------------------------------------------------------------

  34. Predicate Information (identified by operation id):
  35. ---------------------------------------------------

  36.    2 - filter("T"."RN"= (SELECT MAX("RN") FROM    (SELECT "A"."OWNER"
  37.      "OWNER","A"."OBJECT_NAME" "OBJECT_NAME","A"."SUBOBJECT_NAME"
  38.      "SUBOBJECT_NAME","A"."OBJECT_ID" "OBJECT_ID","A"."DATA_OBJECT_ID"
  39.      "DATA_OBJECT_ID","A"."OBJECT_TYPE" "OBJECT_TYPE","A"."CREATED"
  40.      "CREATED","A"."LAST_DDL_TIME" "LAST_DDL_TIME","A"."TIMESTAMP"
  41.      "TIMESTAMP","A"."STATUS" "STATUS","A"."TEMPORARY"
  42.      "TEMPORARY","A"."GENERATED" "GENERATED","A"."SECONDARY"
  43.      "SECONDARY","A"."NAMESPACE" "NAMESPACE","A"."EDITION_NAME"
  44.      "EDITION_NAME",ROWNUM "RN" FROM "TEST" "A") "T"))

  45. Note
  46. -----
  47.    - dynamic sampling used for this statement (level=2)


  48. Statistics
  49. ----------------------------------------------------------
  50.      0 recursive calls
  51.      0 db block gets
  52.        2488 consistent gets
  53.        2482 physical reads
  54.      0 redo size
  55.     525 bytes sent via SQL*Net to client
  56.     523 bytes received via SQL*Net from client
  57.      2 SQL*Net roundtrips to/from client
  58.      0 sorts (memory)
  59.      0 sorts (disk)
  60.      1 rows processed
Predicate Information的filter已經沒錯了,但是還是出不來結果。
這裡不是很懂了。




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

相關文章