Традиционно начинаю с неэффективно работающего запроса:
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
Oracle mechanics 