Параметры

optimizer_mode

rule

optimizer ранжирует методы доступа к данным, без учёта статистики:

Rank Access Path
1 Single row by ROWID
2 Single row by cluster join
3 Single row by hash cluster key with unique or primary key
4 Single row by unique or primary key
5 Cluster join
6 Hash cluster key
7 Indexed cluster key
8 Composite key
9 Single–column indexes
10 Bounded range search on indexed columns
11 Unbounded range search on indexed columns
12 Sort–merge join
13 MAX or MIN of indexed column
14 ORDER BY on indexed columns
15 Full table scan

Не аннонсируется в документации и не поддерживается, начиная с Oracle 10, но используется в коде до 11gR1 включительно (dba_source, v$fixed_view_definition).

first_rows

Наряду со стоимостным подходом, использует набор правил (heuristics):
“If an index scan is available, then the optimizer may choose it over a full table
scan.
If an index scan is available, then the optimizer may choose a nested loops join
over a sort-merge join whenever the associated table is the potential inner table
of the nested loops.
If an index scan is made available by an ORDER BY clause, then the optimizer
may choose it to avoid a sort operation”.
Предпочтение nested loop перед hash и merge join.
После генерации оптимального плана для запроса с сортировкой, в случае, если имеется индекс по столбцам сортировки, план пересматривается с учётом внутреннего хинта для использования этого индекса (с возможным значительным увеличением стоимости), т.е. с целью исключить сортировку – см. Using the FIRST_ROWS hint within a query gives poor performance – тот же эффект можно наблюдать и в Oracle 10.2.
Описание правил – Fast response optimization (FIRST_ROWS variants).
first_rows может успешно использоваться для OLTP систем с ограниченными ресурсами CPU и системы ввода/вывода, поскольку при использовании first_rows_n CBO может предпочитать быстрые, но «ресурсоёмкие» hash и merge join’ы.
Связанные параметры: _sort_elimination_cost_ratio

first_rows_n

CBO, цель оптимизации – лучшее время отклика для получения первых n строк, соответственно план рассчитывается с учётом значения n, как целевой cardinality.

all_rows

CBO, цель оптимизации – лучшее время выполнения всего запроса. Используется по умолчанию для optimizer_mode, начиная с Oracle 10.

choose

выбор между CBO или rule, в зависимости от наличия статистики. Значение по умолчанию до версии 9.2 включительно. Не аннонсируется в документации (начиная с Oracle 10g) – очевидно, в связи с автоматизацией процесса сбора статистики.

cursor_sharing

exact

force

similar

Особенности функционирования: зависимость возможности «cursor sharing» от наличия гистограмм на столбцах таблиц, зависимость возможности «cursor sharing» от предиката даже при отсутствии гистограмм и принудительном отключении возможности bind peeking (_optim_peek_user_binds = false): например, невозможность «cursor sharing» при использовании условий > и < для столбцов с большим количеством (близким количеству строк) неповторяющихся значений при идентичных планах выполнения (10gR2).
463860.1 Higher Library Cache Latch contention in 10g than 9i, High Loaded_Versions Count for SQL In V$SQLAREA despite using bind variables, High Version Count with CURSOR_SHARING = SIMILAR or FORCE

db_file_multiblock_read_count

максимально допустимое количество блоков Oracle, читаемых из БД при последовательном сканировании объектов (FULL TABLE SCAN, INDEX FULL SCAN).

optimizer_dynamic_sampling

Параметр регулирует использование оптимизатором (CBO) механизма dynamic sampling.

«Идея механизма dynamic sampling – улучшить производительность сервера СУБД [в части времени выполнения запросов] с помощью более точной оценки избирательности условий запроса (predicate selectivity) и статистики таблиц и индексов [во времявыполнения запросов]. Под статистикой таблиц и индексов подразумевается количество блоков в таблицах и применяемых индексах, количество строк таблицы (table cardinalities) и статистика столбцов, используемых в операциях соединения…

dynamic sampling можно использовать для:

• оценки избирательности условий запроса одиночной таблицы в случаях, когда собранная статистика не может быть использована или, возможно, приводит к значительным ошибкам в оценке избирательности.
• оценки статистики для таблиц и соответствующих индексов в случае отсутствия… [или]недостоверности»

«Уровни dynamic sampling [значения параметра OPTIMIZER_DYNAMIC_SAMPLING]:

• Уровень 0: Не использовать use dynamic sampling.
• Уровень 1: Оценивать таблицы без статистики (неанализированная) при выполнении условий: (1) в запросе есть по крайней мере одна таблица без статистики; (2) эта таблица в запросе соединяется с другой таблице или включена в подзапрос или non-mergeable view; (3) эта таблица не имеет индексов; (4) в этой таблице блоков больше, чем количество блоков, которое будет использоваться механизмом dynamic sampling для динамического анализа этой таблицы. Количество таких оценочных блоков по умолчанию (dynamic sampling blocks) 32.
• Уровень 2: Применить механизм dynamic sampling ко всем неанализированным таблицам. Количество блоков для динамического анализа вдвое больше значения по умолчанию.
• Уровень 3: Механизм dynamic sampling примяется ко всем таблицам, удовлетворяющим критериям уровня 2 плюс ко всем таблицам, для которых стандартная оценка избирательности по условиям запроса (selectivity estimation) использует предположения, т.е. есть условия, для которых подходит механизм dynamic sampling (potential dynamic sampling predicate). Используется количество оценочных (sampled) блоков по умолчанию. Для неанализированных таблиц оценивается вдвое больше блоков.
• Уровень 4: Механизм dynamic sampling примяется ко всем таблицам, удовлетворяющим критериям уровня 3 плюс ко всем таблицам, для которых в запросе присутствуют условия, относящиеся только к одной таблице (single-table predicates) и включающие условия для 2-х и более столбцов этой таблицы. Используется количество оценочных (sampled) блоков по умолчанию. Для неанализированных таблиц оценивается вдвое больше блоков.
• Уровни 5, 6, 7, 8, и 9: Механизм dynamic sampling примяется ко всем таблицам, удовлетворяющим критериям предыдущих уровней с использованием 2, 4, 8, 32, или 128 кратного количества оценочных (sampled) блоков по умолчанию соответственно.
• Уровень 10: Механизм dynamic sampling примяется ко всем таблицам, удовлетворяющим критериям уровня 9 с использованием всех блоков таблицы»

Значение по умолчанию  зависит от параметра OPTIMIZER_FEATURES_ENABLE. Например, для Oracle 11g при OPTIMIZER_FEATURES_ENABLE>=»11.1.0.6″ значение OPTIMIZER_DYNAMIC_SAMPLING=2 по умолчанию.

См. также описании подсказки Oracle CBO DYNAMIC_SAMPLING

optimizer_index_caching

% индексных блоков, находящихся в buffer cache, используемый CBO для оценки. Диапазон значений – от 0 до 100, по умолчанию OPTIMIZER_INDEX_CACHING = 0, т.е. CBO рассчитывает, что для доступа к каждому блоку индекса потребуется дисковая операция чтения. Увеличение значения параметра делают операции индексного доступа к данным (nested loops joins, IN-list iterators) более предпочтительными для CBO.

optimizer_index_cost_adj

Диапазон 1-10000, значение по умолчанию 100. Мультипликатор стоимости индексного доступа к данным: при модификации параметра, стоимость индексного доступа умножается на optimizer_index_cost_adj / 100.

[_]OPTIMIZER_MAX_PERMUTATIONS

«…контролирует максимальное количество вариантов перестановки таблиц, рассматриваемое  Oracle CBO при подготовке плана выполнения запроса с соединениями (joins)»

Значения от 4 до 80000, по умолчанию 2000, начиная с OPTIMIZER_FEATURES_ENABLE = 9.0.0., для OPTIMIZER_FEATURES_ENABLE = 8.1.7 и ниже – 80000. Т.е., начиная с Oracle 9, подбор оптимального плана выполнения методом простой перестановки таблиц является менее актуальным, используются другие более эффективные методы.

Начиная с Oracle 10g, является скрытым параметром _optimizer_max_permutations.

OPEN_CURSORS

SESSION_CACHED_CURSORS

Определяет размер session cursor cache (количество курсоров), значение по умолчанию 0 до Oracle 10gR2 включительно, 50 – с версии 11g . Рекомендуется установить ненулевое значение для уменьшения нагрузки на library cache. Подробнее можно прочитать на сайте Steve Adams, там же запрос для сравнения установленных параметрами session_cached_cursors, open_cursors лимитов и достигнутых максимальных значений.

_OPTIM_PEEK_USER_BINDS

Контролирует механизм bind peeking, default TRUE = включен, для отключения:
alter session set «_OPTIM_PEEK_USER_BINDS»=FALSE;

_ASM_ALLOW_ONLY_RAW_DISKS

BOOLEAN, по умолчанию TRYE. Установка в FALSE позволяет использовать NFS для хранения ASM файлов.

_DISABLE_ODM

Oracle Disk Manager (ODM)