Запрос имеел вид аналогичный приведенному ниже:
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.
Фактически, такая последовательность действий дает возможность понять — какой план можно получить в ситуации, когда оптимизатор имеет точную информацию о кардинальности, станет ли такой план достаточно эффективным. Ведь в результате анализа может также выяснится, что сама задача, которую решает настраиваемый запрос, требует переформулирования. Кстати, окончательным решением моего коллеги по настройке запроса стало именно переформулирование задачи.
