Oracle объясняет, что план оценивает неправильную кардинальность для индексного сканирования диапазона
У меня есть база данных Oracle 10.2.0.3 и запрос как это:
select count(a.id)
from LARGE_PARTITIONED_TABLE a
join SMALL_NONPARTITIONED_TABLE b on a.key1 = b.key1 and a.key2 = b.key2
where b.id = 1000
Таблица LARGE_PARTITIONED_TABLE (a) имеет приблизительно 5 миллионов строк и делится столбцом, не существующим в запросе. Таблица SMALL_NONPARTITIONED_TABLE (b) не делится и содержит приблизительно 10 000 строк.
Статистические данные актуальны, и существуют сбалансированные гистограммы высоты в столбцах key1 и key2 таблицы a.
Таблица a имеет первичный ключ и глобальный, неразделенный уникальный индекс на столбцах key1, key2, key3, key4 и key5.
Объясните, что план относительно запроса отображает следующие результаты:
---------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 31 | 4 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 31 | | |
| 2 | NESTED LOOPS | | 406 | 12586 | 4 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN| INDEX_ON_TABLE_B | 1 | 19 | 2 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN| PRIMARY_KEY_INDEX_OF_TABLE_A | 406 | 4872 | 2 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("b"."id"=1000)
4 - access("a"."key1"="b"."key1" and
"a"."key2"="b"."key2")
Таким образом строки (кардинальность), оцененная для шага 4, 406.
Теперь, трассировка tkprof показывает следующее:
Rows Row Source Operation
------- ---------------------------------------------------
1 SORT AGGREGATE (cr=51 pr=9 pw=0 time=74674 us)
7366 NESTED LOOPS (cr=51 pr=9 pw=0 time=824941 us)
1 INDEX RANGE SCAN INDEX_ON_TABLE_B (cr=2 pr=0 pw=0 time=36 us)(object id 111111)
7366 INDEX RANGE SCAN PRIMARY_KEY_INDEX_OF_TABLE_A (cr=49 pr=9 pw=0 time=810173 us)(object id 222222)
Таким образом, кардинальность в действительности была 7366, не 406!
Мой вопрос - это: От того, где Oracle получает предполагаемую кардинальность 406 в этом случае, и как я могу улучшить ее точность, так, чтобы оценка была в гармонии больше того, что действительно происходит во время выполнения запросов?
Обновление: Вот отрывок 10 053 трассировок, я работал на запросе.
NL Join
Outer table: Card: 1.00 Cost: 2.00 Resp: 2.00 Degree: 1 Bytes: 19
Inner table: LARGE_PARTITIONED_TABLE Alias: a
...
Access Path: index (IndexOnly)
Index: PRIMARY_KEY_INDEX_OF_TABLE_A
resc_io: 2.00 resc_cpu: 27093
ix_sel: 1.3263e-005 ix_sel_with_filters: 1.3263e-005
NL Join (ordered): Cost: 4.00 Resp: 4.00 Degree: 1
Cost_io: 4.00 Cost_cpu: 41536
Resp_io: 4.00 Resp_cpu: 41536
****** trying bitmap/domain indexes ******
Best NL cost: 4.00
resc: 4.00 resc_io: 4.00 resc_cpu: 41536
resp: 4.00 resp_io: 4.00 resp_cpu: 41536
Using concatenated index cardinality for table SMALL_NONPARTITIONED_TABLE
Revised join sel: 8.2891-e005 = 8.4475e-005 * (1/12064.00) * (1/8.4475e-005)
Join Card: 405.95 = outer (1.00) * inner (4897354.00) * sel (8.2891-e005)
Join Card - Rounded: 406 Computed: 405.95
Таким образом, это - то, куда значение 406 прибывает из. Как Adam, которому отвечают, кардинальность соединения join selectivity * filter cardinality (a) * filter cardinality (b), как видно на предпоследней строке вышеупомянутой кавычки трассировки.
То, что я не понимаю, Revised join sel строка. 1/12064 является селективностью индекса, используемого для нахождения строки от таблицы b (12 064 строки на таблице и выбор на основе уникального идентификатора). Но и что?
Кардинальность, кажется, вычисляется путем умножения кардинальности фильтра таблицы b (4897354) с селективностью таблицы a (1/12064). Почему? Что делает селективность на таблице a, должны сделать с тем, каким количеством строк, как ожидают, будет найден от таблицы b, когда-> b соединение не основан на a.id?
Куда номер 8.4475e-005 прибывает из (это не появляется больше нигде в целой трассировке)? Не то, чтобы это влияет на вывод, но я все еще хотел бы знать.
Я понимаю, что оптимизатор, вероятно, выбрал корректный путь здесь. Но тем не менее кардинальность неверно рассчитана - и это может иметь главный эффект на путь выполнения, который выбран из той точки вперед (как в случае, у меня есть IRL - этим примером является упрощение этого).
3 ответа
Генерация файла трассировки 10053 поможет показать, какой именно выбор делает оптимизатор в отношении оценки кардинальности и селективности. Книга Джонатана Льюиса Cost Based Oracle Fundamentals - отличный ресурс для понимания работы оптимизатора, а имеющаяся у меня печать охватывает версии от 8i до 10.1.
Из этой работы:
Join Selectivity = ((num_rows(t1) - num_nulls(t1.c1)) / num_rows(t1))
* ((num_rows(t2) - num_nulls(t2.c2)) / num_rows(t2))
/ greater (num_distinct(t1.c1), num_distinct(t2.c2))
Join Cardinality = Join Selectivity
* filtered_cardinality (t1)
* filtered_cardinality (t2)
Однако, поскольку у нас многостолбцовое соединение, Join Selectivity не на уровне таблицы, а произведение (пересечение) селективностей соединения по каждому столбцу. Предположим, что в игре нет нулей:
Join Selectivity = Join Selectivity (key1) * Join Selectivity (key2)
Join Selectivity (key1) = ((5,000,000 - 0) / 5,000,000)
* ((10,000 - 0)) / 10,000)
/ max (116, ?) -- distinct key1 values in B
= 1 / max(116, distinct_key1_values_in_B)
Join Selectivity (key2) = ((5,000,000 - 0) / 5,000,000)
* ((10,000 - 0)) / 10,000)
/ max (650, ?) -- distinct key2 values in B
= 1 / max(650, distinct_key2_values in B)
Join Cardinality = JS(key1) * JS(key2)
* Filter_cardinality(a) * Filter_cardinality(b)
Мы знаем, что на A нет фильтров, поэтому кардинальность фильтра таблицы - это количество строк. Мы выбираем ключевое значение из B, поэтому кардинальность фильтра этой таблицы равна 1.
Таким образом, наилучший вариант для расчетной кардинальности соединения сейчас
Join Cardinality = 1/116 * 1/650 * 5,000,000 * 1
=~ 67
Возможно, проще работать в обратном направлении. Ваша предполагаемая кардинальность 406, учитывая то, что мы знаем, приводит к селективности объединения 406/5,000,000, или приблизительно 1/12315. Это очень, очень близко к 1 / (116^2), что является проверкой здравомыслия в оптимизаторе, чтобы он не нашел слишком агрессивную кардинальность для многоколоночных соединений.
Для любителей TL;DR:
- Получите книгу Джонатана Льюиса Основы Oracle на основе затрат.
- Получите 10053 трассировку запроса, поведение которого вы не можете понять.
Оценка кардинальности будет основана на произведении селективности a.key1 и a.key2, которые (по крайней мере, в 10g) будут основаны на количестве различных значений для этих двух столбцов, записанных в статистике столбцов.
Для таблицы из 5 М строк оценка кардинальности 406 не сильно отличается от 7366. Вопрос, который вы должны задать себе, заключается в том, вызывает ли "неточная" оценка здесь проблему?
Вы можете проверить, какой план выбрал бы Oracle, если бы он был в состоянии генерировать абсолютно точную оценку, получив план объяснения для этого:
select /*+CARDINALITY(a 7366)*/ count(a.id)
from LARGE_PARTITIONED_TABLE a
join SMALL_NONPARTITIONED_TABLE b on a.key1 = b.key1 and a.key2 = b.key2
where b.id = 1000;
Если в результате получится тот же план, то оценка, которую Oracle вычисляет, уже адекватна.
Возможно, вам будет интересно прочитать эту отличную статью Вольфганга Брайтлинга, в которой есть много информации о расчетах CBO: http://www.centrexcc.com/A%20Look%20under%20the%20Hood%20of% 20CBO% 20-% 20the% 2010053% 20Event.pdf .
Как объясняется там, поскольку у вас есть гистограммы, при вычислении коэффициента фильтрации для этих столбцов используется не количество различных значений (NDV), а плотность, которая каким-то образом выводится из гистограммы.
Каковы значения DENSITY в USER_TAB_COLUMNS для a.key1 и a.key2?
Как правило, проблема в подобных случаях заключается в том, что Oracle не собирает статистику по парам столбцов и предполагает, что их комбинированный коэффициент фильтрации будет быть продуктом их индивидуальных факторов. Это даст низкие оценки, если есть какая-либо корреляция между значениями двух столбцов.
Если это вызывает серьезные проблемы с производительностью, я полагаю, вы могли бы создать функциональный индекс для функции этих столбцов и использовать его для поиска. Затем Oracle соберет статистику по этому индексу и, вероятно, даст более точные оценки.