Всякий раз, когда обрабатывается допустимый оператор SQL, Oracle должен решить, как получить необходимые данные. Такое решение может быть принято одним из двух способов:
Оптимизатор на основе правил (RBO). Этот метод используется, если на сервере нет внутренней статистики, относящейся к объектам, на которые ссылается оператор. Oracle больше не поддерживает этот метод, и его поддержка будет прекращена в будущих выпусках.
Оптимизатор на основе затрат (CBO). Этот метод используется при наличии внутренней статистики. CBO проверяет несколько возможных планов выполнения и выбирает тот, который имеет наименьшую стоимость, где стоимость связана с системными ресурсами.
CBO оракула будет иметь эффект, если для параметра инициализации оракула «optimizer_index_cost_adj» установлено неправильное значение. Я столкнулся с этой проблемой при работе с медиа-клиентом, использующим приложения SAP CRM/BW поверх уровня базы данных Oracle. Общий размер базы данных превышал 4 терабайта.
Здесь я выбрал для анализа худший SQL-код. Представление «VBAP_VAPMA» основано на таблицах VBAP и VAPMA, VBAP последовательно отображается в верхних сегментах ожидания. Я мог видеть, чтоOptimer_index_cost_adj отдает предпочтение сканированию индекса, даже если оно является худшим по производительности по сравнению с ПОЛНЫМ сканированием таблицы. Я провел некоторые расчеты, чтобы доказать это. .
ВЫБЕРИТЕ «АЕДАТ», «АУАРТ», «ЭРДАТ», «ЕРНАМ», «КОНДМ», «КУННР», «МАТКЛ», «МАТНР», «NETWR», «ПОСНР», «ВБЕЛН», «ВКОРГ», « ВАЕРК", "ZZAD_LINE_STATUS", "ZZCDO", "ZZCDO_P", "ZZKONDM_P"
ИЗ САПР3."VBAP_VAPMA"
ГДЕ "МАНДТ" = :a0
И "АЕДАТ" > :a1
И "АУАРТ" = :a2
И "КОНДМ" = :a3
И "ВКОРГ" = :a4
И "ZZCDO" >= :a5
Текущее значение Optimizer_index_cost_adj установлено равным 10. Установка «Optimizer_index_cost_adj=100» меняет план выполнения с индекса «VBAP~Z3» на полное сканирование таблицы.
Optimizer_index_cost_adj=10
SELECT STATEMENT Optimizer Mode=CHOOSE 2 313894 ДОСТУП К ТАБЛИЦЕ ПО ИНДЕКСНОМУ ROWID SAPR3.VAPMA 1 49 .4 Вложенные циклы 2 206 313893.8 ДОСТУП К ТАБЛИЦЕ ПО ИНДЕКСНОМУ ROWID SAPR3.VBAP 3 K 174 K 312568.2 СКАНИРОВАНИЕ ИНДЕКСНОГО ДИАПАЗОНА SAPR3.VBAP ~Z3 15 М 100758 ИНДЕКС СКАНИРОВАНИЕ ДИАПАЗОНА SAPR3.VAPMA~Z01 1 3
Optimizer_index_cost_adj=100 (рекомендованное Oracle значение по умолчанию)
SELECT STATEMENT Режим оптимизатора = ВЫБЕРИТЕ 2 577409
ДОСТУП К ТАБЛИЦЕ ПО ИНДЕКСНОМУ ROWID SAPR3.VAPMA 1 49 4
Вложенные циклы 2 206 577409 ДОСТУП К ТАБЛИЦЕ ПОЛНЫЙ SAPR3.VBAP 3 K 174 K 564153 СКАНИРОВАНИЕ ИНДЕКСНОГО ДИАПАЗОНА SAPR3.VAPMA~Z01 1 3
Здесь я проведу простые расчеты того, как Oracle оценивает затраты на выполнение. Обратите внимание, что это не точные формулы.
Приблизительная стоимость полного сканирования таблицы: 484 193 без изменений.
Стоимость здесь рассчитывается как «IO + CPU/1000 + NetIO*1,5», но простая формула будет выглядеть следующим образом (Количество блоков/DB_FILE_MULTIBLOCK_READCOUNT).
Количество блоков/DB_FILE_MULTIBLOCK_READCOUNT) = 3 873 549 блоков/8 = 484 193
Как снизить стоимость выполнения: увеличьте DB_FILE_MULTIBLOCK_READCOUNT до 32 + реорганизация таблицы, стоимость «ПОЛНОГО сканирования» упадет до 82 000, что приведет к увеличению количества операций ввода-вывода в 5 раз.
Стоимость сканирования индекса: 149 483 — скорректированное значение.
Он использует неуникальный индекс «SAPR3.VBAP~Z3», определенный для столбцов MANDT, ZZBU_DIR, ZZBU_EDITION.
В этом индексе всего 160 различных значений из 15,9 миллионов строк — «выберите MANDT, ZZBU_DIR, ZZBU_EDITION из SAPR3.vbap»
Стоимость сканирования диапазона индекса = blevel + (средний блок конечных элементов на ключ * (num_rows * избирательность)) = 1 188 451 (фактическое значение) > чем FTS
Мы установили Optimizer_index_cost_adj=10, поэтому реальная стоимость, которую мы установили, равна = 1 188 451 * 10/100 = 118845,1, что составляет 10% фактических накладных расходов.
Окончательное значение стоимости индекса должно включать усилия по доступу к блокам данных =
Предыдущая стоимость + (Avg_data_blks_per_key * (Clustering_fact / Total Table bloks)) = 149 483
Заключение:
Нам нужно позволить оптимизатору Oracle выбрать лучший путь для выполнения, а не заставлять его постоянно выбирать индексы. Установка значения по умолчанию для «optimizer_index_cost_adj» должна сопровождаться актуальной статистикой, поскольку оптимизатор на основе затрат сильно зависит от правильной статистики.
http://OracleDbaSupport.co.uk — это блог Сагара Патила, независимого консультанта Oracle, прекрасно понимающего, как ядро базы данных Oracle и приложения Oracle работают вместе.
Название: Оптимизатор Oracle на основе затрат и влияние параметраоптимизатор_index_cost_adj
Введение
При обработке допустимого оператора SQL Oracle должен определить, как получить необходимые данные. Это решение основано на двух методах: оптимизаторе на основе правил (RBO) и оптимизаторе на основе затрат (CBO). RBO используется, когда на сервере отсутствует внутренняя статистика, связанная с объектами, на которые ссылается оператор. Однако CBO является предпочтительным и используется при наличии внутренней статистики. CBO оценивает несколько планов выполнения и выбирает тот, который имеет наименьшую стоимость с точки зрения системных ресурсов.
Влияние параметраоптимизатор_index_cost_adj
Параметроптимизатор_index_cost_adj в Oracle играет важную роль в поведении оптимизатора на основе затрат. Этот параметр позволяет корректировать расчеты затрат, связанные со сканированием индекса, по сравнению с полным сканированием таблицы. Он определяет относительную стоимость использования индекса по сравнению с другими путями доступа. Если установлено неправильно, это может повлиять на процесс принятия решений оптимизатором и привести к неоптимальным планам выполнения.
Реальный сценарий
Чтобы проиллюстрировать влияние параметраоптимизатор_index_cost_adj, давайте рассмотрим реальный сценарий, возникающий при работе с медиаклиентом с использованием приложений SAP CRM/BW поверх базы данных Oracle. Размер базы данных превышал 4 терабайта, а конкретный SQL-запрос, включающий представление VBAP_VAPMA, выполнялся плохо. Параметроптимизатор_index_cost_adj отдавал предпочтение сканированию индекса, даже если было известно, что оно менее эффективно, чем полное сканирование таблицы.
Пример анализа
Вот пример SQL-запроса, демонстрирующий проблему:
SQL-копия
SELECT "AEDAT", "AUART", "ERDAT", "ERNAM", "KONDM", "KUNNR", "MATKL", "MATNR", "NETWR", "POSNR", "VBELN", "VKORG", "WAERK", "ZZAD_LINE_STATUS", "ZZCDO", "ZZCDO_P", "ZZKONDM_P" FROM SAPR3."VBAP_VAPMA" WHERE "MANDT" = :a0 AND "AEDAT" > :a1 AND "AUART" = :a2 AND "KONDM" = :a3 AND "VKORG" = :a4 AND "ZZCDO" >= :a5
Изменив параметроптимизатор_index_cost_adj, мы заметили существенное изменение плана выполнения. Если установлено значение 10, индекс «VBAP~Z3» имеет преимущество над полным сканированием таблицы. Однако при установке значения по умолчанию 100 (рекомендуется Oracle) план выполнения переключился на полное сканирование таблицы.
Расчет затрат на выполнение
Мы провели приблизительные расчеты, чтобы понять, как Oracle оценивает затраты на выполнение. Обратите внимание, что это не точные формулы, а упрощенные представления.
-
Стоимость полного сканирования таблицы:
Стоимость полного сканирования таблицы рассчитывается как «IO + CPU/1000 + NetIO*1,5». В нашем сценарии примерная стоимость определялась по формуле (Количество блоков/DB_FILE_MULTIBLOCK_READCOUNT). Увеличив значение DB_FILE_MULTIBLOCK_READCOUNT до 32 и реорганизовав таблицу, стоимость полного сканирования значительно снизилась. -
Стоимость сканирования индекса:
В нашем случае индекс «SAPR3.VBAP~Z3» был неуникальным индексом. Стоимость сканирования диапазона индекса рассчитывается как blevel + (средний листовый блок на ключ * (num_rows * избирательность)). Регулируя параметрOptimer_index_cost_adj, мы устанавливаем реальную стоимость в процентах от фактических накладных расходов.
Заключение
Правильная настройка параметраоптимизатор_index_cost_adj имеет решающее значение для эффективного функционирования оптимизатора на основе затрат в Oracle. Рекомендуется позволить оптимизатору выбрать лучший путь выполнения, а не заставлять его постоянно использовать индексы. Кроме того, установку значения по умолчанию дляOptimer_index_cost_adj следует сочетать с обновлением статистики, поскольку оптимизатор на основе затрат в значительной степени полагается на точную статистику для принятия оптимальных решений.
об авторе
Сагар Патил, независимый консультант Oracle и автор блога http://OracleDbaSupport.co.uk, обладает обширными знаниями и пониманием механизма базы данных Oracle и его интеграции с приложениями Oracle. Его опыт и знания делают его ценным ресурсом в сообществе Oracle.
-
Сборка Компьютера. Введение
19 Oct, 24 -
Массовые Ролики С Летающими Самолетами
19 Oct, 24 -
Изучение Основ Photoshop Бесплатно
19 Oct, 24 -
Нужен Ли Вашему Компьютеру Брандмауэр
19 Oct, 24 -
Романтические Открытки – Как Выбрать Лучшее
19 Oct, 24
