Древовидные справочники в качестве таблиц-размерностей или анализ “что-если” применительно к “TUNING BY CARDINALITY FEEDBACK METHOD”

Недавно консультировал коллегу по настройке запроса. Суть решаемой запросом задачи состояла в следующем: таблица фактов (далее facts) связана с таблицей размерности (далее dic$territory), а та в свою очередь, являлась древовидным справочником административно-территориального устройства. Интересующий моего коллегу запрос должен был выдавать некоторые суммарные значения по таблице facts, отфильтрованной с помощью таблицы dic$territory. Из таблицы facts отбирались строки, ссылающиеся на некоторую часть дерева справочника dic$territory, корень необходимой для фильтрации части дерева указывался параметром :code.
Запрос имеел вид аналогичный приведенному ниже:

select sum(human_cnt) from facts
 where territory_id in (select territory_id
                          from dic$territory
                         start with territory_code = :code
                       connect by prior territory_id = territory_parent);

Первый подход к анализу запроса (explain plan) показал - прогнозируемая кардинальность фильтрующего подзапроса (шаг 4) определяется крайне неточно. Следует отметить, что реальная кардинальность подзапроса зависит от значения параметра, указывающего на корневой узел начала перебора дерева, и может изменяться начиная от нескольких единиц для узлов дерева близких к листовым, до нескольких тысяч для узлов дерева близких к корню.

------------------------------------------------------------------------------------------------------
| Id  | Operation                          | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                   |                 |     1 |    22 |    39   (3)| 00:00:01 |
|   1 |  SORT AGGREGATE                    |                 |     1 |    22 |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID      | FACTS           |     9 |    81 |    12   (0)| 00:00:01 |
|   3 |    NESTED LOOPS                    |                 |    27 |   594 |    39   (3)| 00:00:01 |
|   4 |     VIEW                           | VW_NSO_1        |     3 |    39 |     2   (0)| 00:00:01 |
|   5 |      HASH UNIQUE                   |                 |     3 |    30 |            |          |
|*  6 |       CONNECT BY WITH FILTERING    |                 |       |       |            |          |
|   7 |        TABLE ACCESS BY INDEX ROWID | DIC$TERRITORY   |     1 |    26 |     2   (0)| 00:00:01 |
|*  8 |         INDEX RANGE SCAN           | IDX_TERR_CODE   |     1 |       |     1   (0)| 00:00:01 |
|   9 |        NESTED LOOPS                |                 |       |       |            |          |
|  10 |         CONNECT BY PUMP            |                 |       |       |            |          |
|  11 |         TABLE ACCESS BY INDEX ROWID| DIC$TERRITORY   |     3 |    30 |     2   (0)| 00:00:01 |
|* 12 |          INDEX RANGE SCAN          | IDX_TERR_PARENT |     3 |       |     1   (0)| 00:00:01 |
|* 13 |     INDEX RANGE SCAN               | IDX_FACTS_TERR  |     9 |       |     2   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------

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

   6 - access("TERRITORY_PARENT"=PRIOR "TERRITORY_ID")
   8 - access("TERRITORY_CODE"=:CODE)
  12 - access("TERRITORY_PARENT"=PRIOR "TERRITORY_ID")
  13 - access("TERRITORY_ID"="$nso_col_1")

Интересно было бы понять, каким образом вычисляется кардинальность фильтрующего подзапроса. В файле трассировки 10053 имеется следующий фрагмент:

  -----------------------------------------
  BEGIN Single Table Cardinality Estimation
  -----------------------------------------
  Column (#2): TERRITORY_PARENT(NUMBER)
    AvgLen: 5.00 NDV: 12110 Nulls: 23 Density: 8.2576e-005 Min: 0 Max: 42164
  Table: DIC$TERRITORY  Alias: DIC$TERRITORY     
    Card: Original: 42188  Rounded: 3  Computed: 3.48  Non Adjusted: 3.48
  -----------------------------------------
  END   Single Table Cardinality Estimation
  -----------------------------------------

на основе которого можно сделать вывод, что кардинальность подзапроса есть не что иное как кардинальность выборки из таблицы dic$territory, отфильтрованой предикатом territory_parent=:id.
Статистика времени выполнения этого запроса при значении параметра :code, указывающим на «близкий» к корню дерева узел, подтверждает, некорректность прогноза кардинальности подзапроса (шаг 4):

----------------------------------------------------------------------------------------------------------------
| Id  | Operation                          | Name            | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
----------------------------------------------------------------------------------------------------------------
|   1 |  SORT AGGREGATE                    |                 |      1 |      1 |      1 |00:00:00.55 |   28858 |
|   2 |   TABLE ACCESS BY INDEX ROWID      | FACTS           |      1 |      9 |  21051 |00:00:00.43 |   28858 |
|   3 |    NESTED LOOPS                    |                 |      1 |     27 |  21051 |00:00:00.32 |    7807 |
|   4 |     VIEW                           | VW_NSO_1        |      1 |      3 |   2339 |00:00:00.15 |    3081 |
|   5 |      HASH UNIQUE                   |                 |      1 |      3 |   2339 |00:00:00.14 |    3081 |
|*  6 |       CONNECT BY WITH FILTERING    |                 |      1 |        |   2339 |00:00:00.13 |    3081 |
|   7 |        TABLE ACCESS BY INDEX ROWID | DIC$TERRITORY   |      1 |      1 |      1 |00:00:00.01 |       3 |
|*  8 |         INDEX RANGE SCAN           | IDX_TERR_CODE   |      1 |      1 |      1 |00:00:00.01 |       2 |
|   9 |        NESTED LOOPS                |                 |      4 |        |   2338 |00:00:00.11 |    3078 |
|  10 |         CONNECT BY PUMP            |                 |      4 |        |   2339 |00:00:00.01 |       0 |
|  11 |         TABLE ACCESS BY INDEX ROWID| DIC$TERRITORY   |   2339 |      3 |   2338 |00:00:00.07 |    3078 |
|* 12 |          INDEX RANGE SCAN          | IDX_TERR_PARENT |   2339 |      3 |   2338 |00:00:00.03 |    2353 |
|* 13 |     INDEX RANGE SCAN               | IDX_FACTS_TERR  |   2339 |      9 |  21051 |00:00:00.06 |    4726 |
----------------------------------------------------------------------------------------------------------------

Оценив на глазок, можно сказать, что способ соединения (шаг 3) не очень подходит для такой кардинальности, так же как и способ доступа к таблице facts (шаги 13, 2).
Учитывая тот факт, что кардинальность подзапроса оценивается оптимизатором настолько грубо, не остается ничего другого, как воспользоваться подсказкой cardinality со значением параметра равным значению колонки A-Rows для шага 4.

select /*+gather_plan_statistics cardinality(@qb1 2339)*/
       sum(human_cnt) from facts
 where territory_id in (select /*+QB_NAME(qb1)*/ territory_id
                          from dic$territory
                         start with territory_code = :code
                       connect by prior territory_id = territory_parent);

Статистика времени выполнения с уточненной кардинальностью (для того же параметра :code):

--------------------------------------------------------------------------------------------------------------
| Id  | Operation                        | Name            | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------------------------
|   1 |  SORT AGGREGATE                  |                 |      1 |      1 |      1 |00:00:03.42 |    4064 |
|*  2 |   HASH JOIN RIGHT SEMI           |                 |      1 |  21212 |  21051 |00:00:03.38 |    4064 |
|   3 |    VIEW                          | VW_NSO_1        |      1 |   2339 |   2339 |00:00:00.14 |    3081 |
|*  4 |     CONNECT BY WITH FILTERING    |                 |      1 |        |   2339 |00:00:00.13 |    3081 |
|   5 |      TABLE ACCESS BY INDEX ROWID | DIC$TERRITORY   |      1 |      1 |      1 |00:00:00.01 |       3 |
|*  6 |       INDEX RANGE SCAN           | IDX_TERR_CODE   |      1 |      1 |      1 |00:00:00.01 |       2 |
|   7 |      NESTED LOOPS                |                 |      4 |        |   2338 |00:00:00.11 |    3078 |
|   8 |       CONNECT BY PUMP            |                 |      4 |        |   2339 |00:00:00.01 |       0 |
|   9 |       TABLE ACCESS BY INDEX ROWID| DIC$TERRITORY   |   2339 |      3 |   2338 |00:00:00.07 |    3078 |
|* 10 |        INDEX RANGE SCAN          | IDX_TERR_PARENT |   2339 |      3 |   2338 |00:00:00.03 |    2353 |
|  11 |    TABLE ACCESS FULL             | FACTS           |      1 |    381K|    379K|00:00:00.76 |     983 |
--------------------------------------------------------------------------------------------------------------

В результате LIO уменьшился в 7 раз. Для настройки реального запроса такой метод корректирования кардинальности не претендует на универсальность, однако позволяет легко воздействовать на форму плана и оценивать его эффективность.
В моем случае знание формы более эффективного плана позволило найти решение реальной проблемы, а именно, была создана дополнительная таблица, в которой хранилась «развертка» дерева, эта новая таблица была использована в фильтрующем подзапросе вместо первоначальной древовидной. Её использование позволило оптимизатору получать корректные значения прогнозов кардинальности для большинства используемых значений параметра :code.
Кроме того, на основе проведенной работы выкристализовалась идея использовать подсказку cardinality для осуществления «что-если» анализа в рамках метода «...by cardinality feedback...». Алгоритм может быть примерно следующим:
1) выявить шаг плана, с ошибочным прогнозом кардинальности, используя для этого статистику времени исполнения;
2) подсказкой cardinality скорректировать кардинальность;
3) перезапустить запрос, если еще остаются шаги плана с некорректным прогнозом кардинальности, перейти к шагу 1), иначе перейти к следующему шагу;
4) оценить эффективность плана, при положительном ответе — найти способо помочь оптимизатору находить такой же план, но без использования подсказки(ок) cardinality.
Фактически, такая последовательность действий дает возможность понять — какой план можно получить в ситуации, когда оптимизатор имеет точную информацию о кардинальности, станет ли такой план достаточно эффективным. Ведь в результате анализа может также выяснится, что сама задача, которую решает настраиваемый запрос, требует переформулирования. Кстати, окончательным решением моего коллеги по настройке запроса стало именно переформулирование задачи.