Как увеличить оценку стоимости Oracle CBO для хеш-соединений, группировать по и упорядочивать по без подсказок

Question

Похоже, что на некоторых наших серверах стоимость хеш-соединений, группирования и упорядочения слишком низкая по сравнению с реальной стоимостью. То есть часто планы выполнения с просмотром диапазона индекса превосходят прежние, но в плане объяснения стоимость оказывается выше.

Некоторые дальнейшие заметки:

1. Я уже установил optimizer_index_cost_adj на 20, и это все еще недостаточно хорошо. Я НЕ хочу увеличивать стоимость чистых полных сканирований таблиц, на самом деле я не возражаю против того, чтобы оптимизатор уменьшил стоимость.
2. Я заметил, что pga_aggregate_target влияет на оценку стоимости CBO, но я определенно НЕ хочу понижать этот параметр, так как у нас достаточно оперативной памяти.
3. В отличие от использования подсказок оптимизатора в отдельных запросах, я хочу, чтобы настройки были глобальными.

---

Редактировать 1: Я думаю об экспериментировании с динамической выборкой, но у меня недостаточно глубоких знаний, чтобы предсказать, как это может повлиять на общую производительность, то есть как часто планы выполнения могут меняться. Я бы определенно предпочел что-то очень стабильное, на самом деле для некоторых из наших крупнейших клиентов у нас есть политика блокировки всей статистики (которая изменится в Oracle 11g SQL Plan Management).

Answer 1

Довольно часто, когда планы выполнения с сканированиями диапазона индексов превосходят планы с полным сканированием + сортировки или хэш-соединения, но CBO выбирает полные сканирования, это потому, что оптимизатор полагает, что он найдет больше совпадающих результатов, чем на самом деле в реальной жизни.

Другими словами, если оптимизатор думает, что он собирается получить 1M строк из таблицы A и 1000 строк из таблицы B, он вполне может выбрать полное сканирование + сортировка слиянием или объединение хэшей; если, однако, когда он фактически выполняет запрос, он получает только 1 строку из таблицы A, сканирование диапазона индекса вполне может быть лучше.

Сначала я посмотрю на некоторые плохо выполняющиеся запросы и проанализирую избирательность предикатов, чтобы определить, делает ли оптимизатор разумные оценки количества строк для каждой таблицы.

РЕДАКТИРОВАТЬ: Вы упомянули, что оценки мощности неверны. Это коренная причина ваших проблем; стоимость хеш-соединений и сортировок, вероятно, вполне приемлема. В некоторых случаях оптимизатор может использовать неправильные оценки, потому что он не знает, насколько коррелированы данные. Гистограммы в некоторых столбцах могут помочь (если вы их еще не получили), а в некоторых случаях вы можете создавать функциональные индексы и собирать статистику по скрытым столбцам, чтобы предоставить оптимизатору еще лучшие данные.

В конце концов, ваш трюк по определению количества элементов в различных таблицах в запросах вполне может потребоваться для достижения удовлетворительной производительности.