Oracle mechanics

21.09.2012

Влияние параметра optimizer_index_cost_adj на оптимизатор версии 11.1

Filed under: CBO,heuristics,Oracle,parameters — Игорь Усольцев @ 00:29
Tags: , ,

Традиционно начинаю с неэффективно работающего запроса:

11.1.0.7.@ SQL> Select
Distinct ("EXP")
  From "SOME_PARTITIONED_TABLE"
 Where "PATH__LVL" < 10
   And ("PATH" In (Chr(9) || 'R' || Chr(9),
                   Chr(9) || 'R' || Chr(9) || 'v11' || Chr(9)))
/

------------------------------------------------------------+-----------------------------------+---------------+
| Id  | Operation              | Name                       | Rows  | Bytes | Cost  | Time      | Pstart| Pstop |
------------------------------------------------------------+-----------------------------------+---------------+
| 0   | SELECT STATEMENT       |                            |       |       |  6723 |           |       |       |
| 1   |  RESULT CACHE          | 9qyjpzzpk6tyu0r3wpj8un8pbd |       |       |       |           |       |       |
| 2   |   HASH UNIQUE          |                            |    21 |  1155 |  6723 |  00:02:35 |       |       |
| 3   |    PARTITION RANGE ALL |                            |    59 |  3245 |  6722 |  00:02:35 | 1     | 5     |
| 4   |     INDEX FULL SCAN    | MULTIFIELDS_INDX           |    59 |  3245 |  6722 |  00:02:35 | 1     | 5     |
------------------------------------------------------------+-----------------------------------+---------------+
Predicate Information:
----------------------
4 - access("PATH__LVL"<10)
4 - filter((INTERNAL_FUNCTION("PATH") AND "PATH__LVL"<10))

Строка RESULT CACHE в плане значения не имеет и определяется установленным в системе параметром:

SQL> show parameter result_cache_mode

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
result_cache_mode                    string      FORCE

Показательно, хотя и не очень важно в этом случае, что тот же запрос с одним условием выполняется по тому же «плохому» плану:

SQL> explain plan for
  2  Select--+ OPT_PARAM('optimizer_index_cost_adj' 10) no_result_cache
  3  Distinct ("EXP")
  4    From "SOME_PARTITIONED_TABLE" t
  5   Where --"PATH__LVL" < 10 And
  6   ("PATH" In (Chr(9) || 'R' || Chr(9),
  7                     Chr(9) || 'R' || Chr(9) || 'v11' || Chr(9)))
  8  /

---------------------------------------------------------------------------------------------------------
| Id  | Operation            | Name             | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
---------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |                  |    21 |  1092 |  6723   (1)| 00:01:35 |       |       |
|   1 |  HASH UNIQUE         |                  |    21 |  1092 |  6723   (1)| 00:01:35 |       |       |
|   2 |   PARTITION RANGE ALL|                  |    59 |  3068 |  6722   (1)| 00:01:35 |     1 |     5 |
|*  3 |    INDEX FULL SCAN   | MULTIFIELDS_INDX |    59 |  3068 |  6722   (1)| 00:01:35 |     1 |     5 |
---------------------------------------------------------------------------------------------------------

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

   3 - filter("PATH"='  R       ' OR "PATH"='   R       v11     ')

План же очевидно плох медленной операцией INDEX FULL SCAN (чтение всех блоков индекса по одному) по большому партиционированному по дате индексу MULTIFIELDS_INDX(FIELDDATE(date), EXP(varchar2(3000)), PATH__LVL(number), PATH(varchar2(4000)))

При этом в наличии – отличный индекс по одному полю с высокой исбирательностью PATH_INDX(PATH(varchar2(4000)))

Интересно, что запрос по этому индексу, искусственно стимулированный подсказкой, выполняется не только быстро, но по более дешёвому плану:

SQL> Select--+ index(t PATH_INDX) no_result_cache
  2  Distinct ("EXP")
  3    From "SOME_PARTITIONED_TABLE" t
  4   Where "PATH__LVL" < 10
  5     And ("PATH" In (Chr(9) || 'R' || Chr(9),
  6                     Chr(9) || 'R' || Chr(9) || 'v11' || Chr(9)))
  7  /

22 rows selected.

Elapsed: 00:00:19.33

-------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name                   | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |                        |    21 |  1155 |     7  (15)| 00:00:01 |       |       |
|   1 |  HASH UNIQUE                         |                        |    21 |  1155 |     7  (15)| 00:00:01 |       |       |
|   2 |   INLIST ITERATOR                    |                        |       |       |         |             |       |       |
|*  3 |    TABLE ACCESS BY GLOBAL INDEX ROWID| SOME_PARTITIONED_TABLE |    59 |  3245 |     6   (0)| 00:00:01 | ROWID | ROWID |
|*  4 |     INDEX RANGE SCAN                 | PATH_INDX              |    59 |       |     1   (0)| 00:00:01 |       |       |
-------------------------------------------------------------------------------------------------------------------------------

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

   3 - filter("PATH__LVL"<10)
   4 - access("PATH"='  R       ' OR "PATH"='   R       v11     ')

Почему же оптимизатор при наличии дешёвого и быстрого плана (Cost = 7) выбирает дорогой и медленный со стоимостью 6723 ?

В трейсе оптимизатора (10053 event) для медленного запроса можно найти неочевидное объяснение феномена:)

***************************************
BASE STATISTICAL INFORMATION
***********************
Table Stats::
  Table: SOME_PARTITIONED_TABLE  Alias:  T  (Using composite stats)
    #Rows: 23260395  #Blks:  68301  AvgRowLen:  79.00
Index Stats::
  Index: MULTIFIELDS_INDX  Col#: 2 1 3 4                                                      -- "плохой" индекс
    USING COMPOSITE STATS
    LVLS: 2  #LB: 66956  #DK: 23009757  LB/K: 1.00  DB/K: 1.00  CLUF: 22968387.00
...
  Index: PATH_INDX  Col#: 4                                                                   -- "хороший" индекс
    LVLS: 3  #LB: 93317  #DK: 367045  LB/K: 1.00  DB/K: 63.00  CLUF: 23238581.00
  Column (#1):                                                                                -- про столбец EXP
    NewDensity:0.000091, OldDensity:0.000000 BktCnt:5518, PopBktCnt:5517, PopValCnt:19, NDV:22

Access path analysis for SOME_PARTITIONED_TABLE
***************************************
SINGLE TABLE ACCESS PATH
  Single Table Cardinality Estimation for SOME_PARTITIONED_TABLE[T]

  Column (#4):
    NewDensity:0.000001, OldDensity:0.001182 BktCnt:254, PopBktCnt:136, PopValCnt:20, NDV:369248
  Using density: 0.000001 of col #4 as selectivity of unpopular value pred
  Using density: 0.000001 of col #4 as selectivity of unpopular value pred
  Column (#3):
    NewDensity:0.000091, OldDensity:0.000000 BktCnt:5518, PopBktCnt:5517, PopValCnt:6, NDV:14
  Table: SOME_PARTITIONED_TABLE  Alias: T
    Card: Original: 23260395.000000  Rounded: 59  Computed: 58.53  Non Adjusted: 58.53
  Access Path: TableScan
    Cost:  21312.98  Resp: 21312.98  Degree: 0                             -- интересно, что стоимость FULL SCAN по таблице < первоначальной стоимости FullScan по MULTIFIELDS_INDX (66958)
      Cost_io: 21041.00  Cost_cpu: 8234266307
      Resp_io: 21041.00  Resp_cpu: 8234266307
...
  Access Path: index (FullScan)                                            -- "плохой" индекс, кот. будет выбран для плана
    Index: MULTIFIELDS_INDX
    resc_io: 66958.00  resc_cpu: 5605367918
    ix_sel: 1.000000  ix_sel_with_filters: 1.000000
 ***** Logdef predicate Adjustment ******
 Final IO cst 0.00 , CPU cst 100.00
 ***** End Logdef Adjustment ******
...
    Cost: 6721.91  Resp: 6721.91  Degree: 1                                -- стоимость уменьшается в соотв. с optimizer_index_cost_adj
...
  Access Path: index (RangeScan)                                           -- "хороший", но отвергнутый индекс
    Index: PATH_INDX
    resc_io: 61.00  resc_cpu: 930762
    ix_sel: 0.000003  ix_sel_with_filters: 0.000003
    Cost: 6.10  Resp: 6.10  Degree: 1                                      -- стоимость уменьшается в соотв. с optimizer_index_cost_adj
New AP rejected (non-guess): favored index only range scan                 -- вероятная причина: индекс PATH_INDX (New Access Path) не будет использован, т.к. есть индекс MULTIFIELDS_INDX с index only доступом
  ****** trying bitmap/domain indexes ******
  ****** finished trying bitmap/domain indexes ******
  Best:: AccessPath: IndexRange
  Index: MULTIFIELDS_INDX                                                  -- "плохой" индекс назначается победителем
         Cost: 6721.91  Degree: 1  Resp: 6721.91  Card: 58.53  Bytes: 0

Grouping column cardinality [       EXP]    21
***************************************
...
  /*+
    BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('11.1.0.7')
      DB_VERSION('11.1.0.7')
      OPT_PARAM('optimizer_index_cost_adj' 10)
...
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$1")
      INDEX(@"SEL$1" "T"@"SEL$1" ("SOME_PARTITIONED_TABLE"."FIELDDATE" "SOME_PARTITIONED_TABLE"."EXP" "SOME_PARTITIONED_TABLE"."PATH__LVL" "SOME_PARTITIONED_TABLE"."PATH"))
      USE_HASH_AGGREGATION(@"SEL$1")
    END_OUTLINE_DATA
  */

Вышеуказанное правило CBO favored index only range scan оказалось следствием недефолтного значения параметра optimizer_index_cost_adj=10, установленного на уровне системы, очевидно, для стимуляции индексного доступа в стародавние времена

Как оказалось, оптимизатор применяет это правило при установке в значения optimizer_index_cost_adj < 31

При  значениях параметра >= 31 (включая значение по умолчанию 100), указанное правило не работает, и оптимизатор успешно выбирает быстрый и правильный план в полном соответствии со стоимостным подходом:

SQL> Select
  2  /*+ OPT_PARAM('optimizer_index_cost_adj' 31)
  3        no_result_cache */
  4  Distinct ("EXP")
  5    From "SOME_PARTITIONED_TABLE" t
  6   Where "PATH__LVL" < 10
  7     And ("PATH" In (Chr(9) || 'R' || Chr(9),
  8                     Chr(9) || 'R' || Chr(9) || 'v11' || Chr(9)))
  9  /

22 rows selected.

Elapsed: 00:00:10.68 -- cold buffer cache
Elapsed: 00:00:00.16 -- hot cache

-------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name                   | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |                        |    21 |  1155 |    20   (5)| 00:00:01 |       |       |
|   1 |  HASH UNIQUE                         |                        |    21 |  1155 |    20   (5)| 00:00:01 |       |       |
|   2 |   INLIST ITERATOR                    |                        |       |       |         |             |       |       |
|*  3 |    TABLE ACCESS BY GLOBAL INDEX ROWID| SOME_PARTITIONED_TABLE |    59 |  3245 |    19   (0)| 00:00:01 | ROWID | ROWID |
|*  4 |     INDEX RANGE SCAN                 | PATH_INDX              |    59 |       |     1   (0)| 00:00:01 |       |       |
-------------------------------------------------------------------------------------------------------------------------------

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

   3 - filter("PATH__LVL"<10)
   4 - access("PATH"='  R       ' OR "PATH"='   R       v11     ')

Быстро сконструировать простой тест-кейс у меня не получилось, поэтому надеюсь, что эта особенность характерна только для старых версий Oracle < 11.2

«OPTIMIZER_INDEX_COST_ADJ» Reference Note [ID 62285.1]

Добавить комментарий »

Комментариев нет.

RSS feed for comments on this post. TrackBack URI

Добавить комментарий

Заполните поля или щелкните по значку, чтобы оставить свой комментарий:

Логотип WordPress.com

Для комментария используется ваша учётная запись WordPress.com. Выход / Изменить )

Фотография Twitter

Для комментария используется ваша учётная запись Twitter. Выход / Изменить )

Фотография Facebook

Для комментария используется ваша учётная запись Facebook. Выход / Изменить )

Google+ photo

Для комментария используется ваша учётная запись Google+. Выход / Изменить )

Connecting to %s

Создайте бесплатный сайт или блог на WordPress.com.

%d такие блоггеры, как: