The Search For Intelligent Life in the Cost-Based Optimizer
by Tim Gorman

Ближе к делу...

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

Начиная с Oracle8 версии 8.0, были введены два новых недокументированных параметра инициализации. Со временем они были описаны в руководстве Oracle8i Server Reference релиза Oracle8i:

• OPTIMIZER_INDEX_CACHING
  Этот параметр представляет собой процентное отношение в диапазоне от 0 до 99. Значение по умолчанию 0 указывает CBO, что в буферном кэше SGA ожидается найти 0% блоков, полученных через индексный доступ. Это значение (также известное как нулевой коэффицент попадания в Буферный Кэш) предполагает, что любое обращение к индексу потребует операции физического чтения (physical read) с подсистемы ввода/вывода для каждой операции логического чтения (logical read) из Буферного Кэша. Этот параметр относится только к вычислению оптимизатором стоимости читаемых индексных блоков, но не блоков таблицы, связанной с этим индексом.
• OPTIMIZER_INDEX_COST_ADJ
  Этот параметр также задается процентной величиной, но в диапазоне между 1 и 10000, указывая степень сопоставления между относительной стоимостью физических запросов ввода/вывода при индексном доступе и операций полного табличного сканирования (full table-scans⁵). Значение по умолчание 100 указывает стоимостному оптимизатору, что индексный доступ также (100%) дорог (т.е. "стоит" столько же) как и операции полного табличного сканирования.

Оказывается, значения по умолчанию для этих параметров являются слишком неподходящими и нереалистичными. Я докажу это утверждение позже в этой статье, но пока достаточно будет сказать, что параметр OPTIMIZER_INDEX_CACHING должен быть установлен в значение 90. OPTIMIZER_INDEX_COST_ADJ должен быть выставлен в значение, которое обычно лежит в диапазоне между 10 и 50 для большинства систем оперативной обработки транзакций (OLTP⁶); для хранилищ данных или других систем поддержки принятия решений (DSS⁷) было бы разумно просто установить этот параметр равным 50. Хороший способ вычисления этого параметра с некоторой степенью точности приведен в конце статьи...

Вот и все!

Проверьте этот совет. Возьмите прежде "безнадежный" SQL оператор, удалите все хинты, которыми он был старательно залатан, установите эти два параметра, используя ALTER SESSION SET, и выполните команду EXPLAIN PLAN.

...окончание истории...

Все еще здесь?

Ненавидете, когда кто-то делает дикое утверждение абсолютно без доказательств? Похоже на большинство подобных рекомендаций, не так ли? "Советы и методики" - издержки профессионального образования, поэтому важно поразмыслить над более убедительными аргументами. Хорошо, приготовьтесь размышлять...

Прежде всего, я хотел бы рассказать историю с самого начала, начиная с RBO и его принципов работы, затем перейти к обсуждению CBO и объяснить (и сопоставить) его принципы работы. Я хочу показать, какой сильный эффект производят упомянутые выше параметры при работе с CBO. Добавленные в Oracle8 версии 8.0 в качестве недокументированных, эти параметры оказывают влияние на вычисление стоимости, производимой CBO, примерно так же, как общий доход влияет на чистую прибыль за вычетом налогов.