Описание некоторых инициализационных параметров Oracle, влияющих на механизмы оптимизации запросов и производительность бд
optimizer_features_enable
определяет фичи оптимизатора, действующие на уровне сессии / системы, в соответствии с указанным релизом Oracle. Изменение параметра в сторону более низкого релиза отключает новые фичи, исключая новые сопутствующие баги оптимизатора.
PARAMETER VALUE DEFAULT
-------------------------- ---------- -------
optimizer_features_enable 8.0.0
8.0.3
8.0.4
8.0.5
8.0.6
8.0.7
8.1.0
8.1.3
8.1.4
8.1.5
8.1.6
8.1.7
9.0.0
9.0.1
9.2.0
9.2.0.8
10.1.0
10.1.0.3
10.1.0.4
10.1.0.5
10.2.0.1
10.2.0.2
10.2.0.3
10.2.0.4
10.2.0.5
11.1.0.6
11.1.0.7
11.2.0.1
11.2.0.2
11.2.0.3 DEFAULT
11.2.0.3.1
PARAMETERS TO CHANGE 11.2.0.1 TO 10.2.0.4 [ID 1274553.1]
PARAMETERS TO CHANGE 11.2.0.1 TO 11.1.0.7 [ID 1096377.1] – списки параметров оптимизатора, используемых при разных значениях параметра optimizer_features_enable – своеобразный diff между версиями :)
В Oracle 11g список добавленных/изменившихся фич оптимизатора (_fix_control), действующих на уровне инстанса / отдельных сессий, отражён в обзорах v$system_fix_control / v$session_fix_controlсоответственно:
SQL> select optimizer_feature_enable, count(*) 2 from v$system_fix_control where optimizer_feature_enable like '11%' 3 group by optimizer_feature_enable 4 / OPTIMIZER_FEATURE_ENABLE COUNT(*) ------------------------- ---------- 11.1.0.7 19 11.1.0.6 40 11.2.0.1 67 11.2.0.2 106 11.2.0.3 76
optimizer_mode
rule
Oracle ранжирует методы доступа к данным, без учёта статистики в следующем порядке:
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, но используется в коде до 11g включительно (примеры использования можно найти в системных обзорах dba_source, v$fixed_view_definition). Может быть полезен в запросах к системным обзорам и таблицам
first_rows
Наряду со стоимостным подходом, использует набор правил (heuristics):
“При возможности, оптимизатор может выбрать индексный доступ (index scan) вместо сканирования таблицы (full table scan).
При возможности, оптимизатор может выбрать nested loops join вместо sort-merge join всякий раз, когда связанная таблица [с возможным индексным доступом] потенциально является подчинённой таблицей в операции nested loop (inner table of the nested loops).
При возможности индексного доступа к столбцам сортировки запроса (указанным в списке ORDER BY), оптимизатор может выбрать индексный доступ (index scan) во избежание дополнительной сортировки [результатов запроса]”
Предпочтение 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
Cost Based Optimization, цель оптимизации – лучшее время отклика для получения первых n строк, план рассчитывается с учётом значения n, как целевого количества выбранных запросом строк (query cardinality)
Значения n=1, 10, 100, 1000, соттветственно, “план выполнения покажет ожидаемое количество строк 1, 10, 100, 1000 в итоговом значении столбца CARDINALITY таблицы PLAN_TABLE”. Количество строк всех подзапросов при расчёте плана выполнения будет пропорционально уменьшено. При небольших значениях n оптимизатор вероятнее использует правила FIRST_ROWS, при больших – сгенерированный план будет близок к ALL_ROWS.
all_rows
Cost Based Optimization, цель оптимизации – лучшее время выполнения всего запроса. Используется по умолчанию для optimizer_mode, начиная с Oracle 10.
choose
выбор между CBO или rule, в зависимости от наличия статистики. Значение по умолчанию до версии 9.2 включительно. Не аннонсируется в документации (начиная с Oracle 10g) – очевидно, в связи с автоматизацией процесса сбора статистики.
cursor_sharing
Значение параметра “… определяет типы SQL запросов, которые могут использовать разделяемые курсоры (to share the same cursors)”. Использование разделяемых курсоров исключает “тяжёлую” вычислительную фазу hard parse из выполнения SQL запроса и, таким образом, экономит процессорное время при на этапе подготовки запроса, а также системные ресурсы для управления shared pool
exact
Только абсолютно идентичные (по тексту) запросы могут использовать тот же курсор. Значение параметра по умолчанию
force
< Oracle 11.2: “Позволяет SQL запросам, отличающимся только несколькими текстовыми константами (some literals, например, значениями в условиях запроса), использовать один курсор до тех пор, пока эти отличия не влияют на смысл запроса”
>= Oracle 11.2: “Разрешает создавать новый курсор для SQL запроса [только] если использование существующего или план существующего курсора не оптимальны”
similar
“Позволяет SQL запросам, отличающимся только несколькими текстовыми константами (some literals, например, значениями в условиях запроса), использовать один курсор до тех пор, пока эти отличия не влияют на смысл запроса или уровень оптимизации плана выполнения [не ухудшают план]“
Т.е. в отличие от значения FORCE при использовании SIMILAR при решении о повторном использовании курсора (cursor sharing) оптимизатор учитываются не только совпадение текстов запросов, но и оптимальность повторного использования плана для различных значений текстовых констант (условий) в запросе.
Особенности функционирования: зависимость возможности повторного использования курсора (cursor sharing) от наличия гистограмм на столбцах таблиц, зависимость возможности «cursor sharing» от условий запроса (query predicates) даже при отсутствии гистограмм и принудительном отключении возможности 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
*) Использование механизма cursor_sharing (независимо от значения параметра force или similar) интересно влияет на использование оптимизатором функциональных индексов (function based index), созданных с использованием функций с литералами (например, substr(vc1,4,2)). CBO не использует такие индексы, поскольку преобразует условия запроса для использования связанных переменных к виду
SUBSTR("VC1",:SYS_B_0,:SYS_B_1)=:SYS_B_2
Кроме очевидного метода решения этой проблемы (отключение механизма cursor_sharing с помощью параметра cursor_sharing=exact или подсказки CURSOR_SHARING_EXACT), по вышеприведённой ссылке Jonathan Lewis описывает рабочий метод использования function based индексов через “сокрытие” function-based выражений в обзор
create view v1 as select ... substr(vc1,4,2) vc_short, ...
Использование CURSOR_SHARING = SIMILAR настоятельно не рекомендуется производителем, начиная с 11g будет исключено из доступных значений параметра в Oracle 12 – ANNOUNCEMENT: Deprecating the cursor_sharing = ‘SIMILAR’ setting [ID 1169017.1]
db_file_multiblock_read_count
максимально допустимое количество блоков Oracle, читаемых из БД при последовательном сканировании объектов (FULL TABLE SCAN, INDEX FAST 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 [определяется значением параметра _optimizer_dyn_smp_blks]
- Уровень 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
2 если OPTIMIZER_FEATURES_ENABLE >= 10.0.0 1 если OPTIMIZER_FEATURES_ENABLE = 9.2.0 0 если OPTIMIZER_FEATURES_ENABLE <= 9.0.1
Unsafe Literals or Peeked Bind Variables [ID 377847.1]
… после того, значение по умолчанию dynamic sampling было изменено с 1 до 2. Если optimizer_dynamic_sampling больше чем 1, технология Dynamic sampling эмулирует статистику + гистограммы. В свою очередь, при появлении гистограм связанные переменные (binds) могут рассматриваться оптимизатором как небезопасные (unsafe) в результате чего для разных значений будут генерироваться разные планы выполнения. При значении optimizer_dynamic_sampling > 1 условие запроса (predicate) может генерировать новую версию запроса (v$sql_shared_cursor) для каждого нового значения связанной переменной, даже в случае отсутствия гистограм (или даже собранной статистики) на таблице (поскольку dynamic sampling может генерировать и то, и другое на лету (in the background))
См. также описании подсказки Oracle CBO DYNAMIC_SAMPLING
Upgrading from Oracle Database 10g to 11g: What to expect from the Optimizer:
Начиная с Oracle 11g R2 при параллельном выполнении запросов оптимизатор определяет использование dynamic sampling и dynamic sampling level даже при наличии адекватной статистики для всех объектов, используемых в запросе. Это решение основывается на размере таблиц и комплексности условий (the complexity of the predicates).
Однако, если параметр OPTIMIZER_DYNAMIC_SAMPLING установлен в значение, отличное от знасения по умолчанию, предпочнение в выборе dynamic sampling level будет отдано установленному значению (specified value will be honored). Факт использование dynamic sampling отражается в секции note плана выполнения запроса:
Note ----- - dynamic sampling used for this statement (level=5)
Во время выполнения параллельного запроса оптимизатор меняет значение dynamic sampling level с помощью подсказки OPT_PARAM, что отражается в секции OUTLINE реального плана выполнения:
SQL> SELECT * FROM TABLE(dbms_xplan.display_cursor( 'gdxqxfj5d3zjb',0,'basic +outline last'));
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------------------
...
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
...
OPT_PARAM('optimizer_dynamic_sampling' 5)
...
END_OUTLINE_DATA
*/
optimizer_index_caching
% индексных блоков, находящихся в buffer cache, используется CBO для оценки стоимости индексного доступа. Диапазон значений – от 0 до 100, по умолчанию OPTIMIZER_INDEX_CACHING = 0, т.е. CBO рассчитывает, что для доступа к каждому блоку индекса потребуется дисковая операция чтения. Увеличение значения параметра делают некоторые операции индексного доступа к данным (согласно документации и простым тестам только nested loops joins, IN-list iterators) более предпочтительными/недорогими при вычислениях оптимизатора. См. тесты SORT UNIQUE NOSORT и кэширование индексных блоков
optimizer_index_cost_adj
Диапазон 1-10000, значение по умолчанию 100. Мультипликатор стоимости одноблочного индексного доступа к данным: index range scan, index full scan. При модификации параметра, стоимость индексного доступа умножается на (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
“… наибольшее количество одновременно открытых сессией курсоров,.. при установлении этого параметра выше количества используемых сессией курсоров дополнительные ресурсы не используются”
Значение по умолчанию 50
SESSION_CACHED_CURSORS
Определяет размер session cursor cache (количество курсоров), значение по умолчанию 0 до Oracle 10gR2 включительно, 50 – начиная с версии 11g . Рекомендуется установить ненулевое значение для уменьшения нагрузки на library cache. Подробнее можно прочитать на сайте Steve Adams, там же запрос для сравнения установленных параметрами session_cached_cursors, open_cursors лимитов и достигнутых максимальных значений
CURSOR_SPACE_FOR_TIME
“… позволяет использовать большее пространство [памяти] для курсоров с целью экономии времени [на освобождение места в SQL областях]. Влияет и на разделяемую (shared SQL area в shared pool), и на частную (private SQL area) области SQL”
- false (значение по умолчанию) – “… курсор может быть удалён (deallocated) из кэша (library cache) несмотря на то, что в приложении остаются открытыми курсоры, связанные с SQL предложениями [до этого находившимися в кэше]. В этом случае Oracle должен [всякий раз] проверять, что курсор для конкретного SQL предложения [всё ещё] присутствует в library cache“
- true – “… курсор может быть удалён из кэша только тогда, когда все курсоры приложения, связанные с SQL предложениями [находящимися в кэше] закрыты. В этом случае Oracle не должен [всякий раз] проверять, что курсор для конкретного SQL предложения [всё ещё] присутствует в library cache, т.к. курсор не может быть удалён из кэша, пока в приложении есть открытые курсоры, связанные с кэшированными SQL”
Oracle рекомендует устанавливать параметр в значение true “только в случаях, когда shared pool достаточно велик, чтобы вместить все открытые курсоры одновременно” воизбежание конкуренции за library cache / shared pool
_OPTIM_PEEK_USER_BINDS
Контролирует механизм bind peeking, default TRUE = включен, для отключения:
SQL> alter session set "_OPTIM_PEEK_USER_BINDS"=FALSE;
_ASM_ALLOW_ONLY_RAW_DISKS
BOOLEAN, по умолчанию TRYE. Установка в FALSE позволяет использовать NFS для хранения ASM файлов (не только raw devices)
_DISABLE_ODM
Параметр для управления ODM – Oracle Disk Manager
max_commit_propagation_delay
- определяет максимальное время (в миллисекундах), в течение которого результат транзакции (COMMIT) на одном из нодов будет доступен на всех нодах кластера (Oracle OPS/RAC), точнее, на всех нодах произойдёт обновление SCN
- определяет используемый механизм распостранения System Change Number (SCN propagation) между инстансами кластера
до Oracle 10.2 значение по умолчанию 700 мс, используется Lamport Scheme распостранения SCN (с использованием GCS/GES messages), сообщение в alert.log при старте инстанса
Picked Lamport scheme to generate SCNs
с версии Oracle 10.2 значение по умолчанию 0, используется Broadcast on commit Scheme
“Каждый раз, когда LGWR пишет в redo log (т.е. при каждом commit):
- LGWR посылает сообщение на обновление global SCN.
- LGWR посылает сообщение каждому активному инстансу на обновление local SCN“
с соответствующим сообщением в alert.log
Picked broadcast on commit scheme to generate SCNs
Начиная с версии Oracle 10.2 параметр max_commit_propagation_delay является “нерекомендуемым к использованию” (deprecated), в 11.2 – вовсе отсутствует. Выбор механизма SCN propagation определяется параметром _immediate_commit_propagation ( = true по умолчанию, т.е. используется Broadcast on commit (BOC))
MAX_COMMIT_PROPAGATION_DELAY – in Oracle 9.2 doc
MAX_COMMIT_PROPAGATION_DELAY In A Real Application Clusters Environment [ID 259454.1]
Julian Dyke “RAC Internals – Forgotten Features”
http://yong321.freeshell.org/oranotes/max_commit_propagation_delay.txt
COMMIT_WRITE
COMMIT_WRITE = ‘{IMMEDIATE | BATCH},{WAIT |NOWAIT}’
SQL> COMMIT WRITE {IMMEDIATE | BATCH} {WAIT |NOWAIT};
“… параметр, предназначенный для контроля процесса записи redo информации при вызове команды commit в redo log файлы. Опции IMMEDIATE и BATCH контролируют как redo информация буферизуется (batched) при записи процессом Log Writer (LGWR). Опции WAIT и NOWAIT контролируют, когда redo информация по команде commit будет записана в redo logs файлы”
Или по определению Тима Холла (там же есть простые тесты производительности опций):
- IMMEDIATE – команда commit, посылая сообщение, “дёргает” процесс LGWR для немедленной записи redo данных в redo log файлы
- BATCH – запись в redo log файлы буферизуется
- WAIT – команда commit выполняется синхронно. Результат не возвращается, пока redo информация не будет записана в redo log файл
- NOWAIT – команда commit выполняется асинхронно. Результат возвращается до того, как redo информация будет записана в redo log файл
Значение по умолчанию COMMIT_WRITE = ‘IMMEDIATE, WAIT’ (классическое, надёжное поведение при COMMIT), самые “быстрый”, но ненадёжный набор опций ‘BATCH, NOWAIT’
Появился в версии Oracle 10g R2, начиная с Oracle 11.1 заменён параметрами COMMIT_LOGGING, COMMIT_WAIT
Элегантный тест опций commit от французского товарища Franck Pachot на форуме OTN:
‘immediate nowait‘ сигнализирует log writer записать на диск redo buffer, но не ждёт сигнала о завершении записи (write completion)
‘batch‘ даже не сигнализирует log writer записать redo buffer. Опция имеет значение [только] совместно с ‘nowait‘.
поведение ‘ batch wait‘ похоже на ‘immediate wait‘: пользовательский процесс ждёт завершения записи redo на диск…
08:02:15 SQL> host ps -edo args,pid | egrep "lgwr|PID" COMMAND PID ora_lgwr_orcl 2933 08:02:15 SQL> 08:02:15 SQL> update t set n=n+1; 1 row updated. 08:02:15 SQL> host ( kill -STOP 2933 ; sleep 10 ; kill -cont 2933 )& 08:02:15 SQL> commit write immediate wait; Commit complete. 08:02:25 SQL> host sleep 10 08:02:35 SQL> 08:02:35 SQL> update t set n=n+1; 1 row updated. 08:02:35 SQL> host ( kill -STOP 2933 ; sleep 10 ; kill -cont 2933 )& 08:02:35 SQL> commit write immediate nowait; Commit complete. 08:02:35 SQL> host sleep 10 08:02:45 SQL> 08:02:45 SQL> update t set n=n+1; 1 row updated. 08:02:45 SQL> host ( kill -STOP 2933 ; sleep 10 ; kill -cont 2933 )& 08:02:45 SQL> commit write batch wait; Commit complete. 08:02:56 SQL> host sleep 10 08:03:06 SQL> 08:03:06 SQL> update t set n=n+1; 1 row updated. 08:03:06 SQL> host ( kill -STOP 2933 ; sleep 10 ; kill -cont 2933 )& 08:03:06 SQL> commit write batch nowait; Commit complete. 08:03:06 SQL> host sleep 10 08:03:16 SQL> 08:03:16 SQL> spool off 08:03:16 SQL> exit Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1
COMMIT_LOGGING
{IMMEDIATE | BATCH}
параметр буферизации операций записи redo процессом LGWR (how redo is batched, IMMEDIATE – без буферизации, BATCH – буферизует/ группирует отдельные операции)
COMMIT_WAIT
{ NOWAIT | WAIT | FORCE_WAIT }
“параметр контролирует когда redo информация по команде commit будет записана в redo logs файлы.
При использовании нужно понимать, что значение NOWAIT может привести к потере данных, которая может случиться после того, как бд получит подтверждение успешного завершения операции commit, но до того, как redo log данные будут записаны на диск…”
_enqueue_deadlock_time_sec
requests with timeout <= this will not have deadlock detection, по умолчанию, 5 секунд
Например, SQL запрос
SELECT ... FOR UPDATE WAIT 5
процедурой Enqueue Deadlock detection (выполняемой системным процессом Global Enqueue Service Daemon (LMD) каждые 3 секунды) не рассматривается как потенциальный источник deadlock’а и соответственно не может закончится с ошибкой
ORA-00060: deadlock detected while waiting for resource
, а запрос
SELECT ... FOR UPDATE WAIT 6
с таймаутом > _enqueue_deadlock_time_sec уже рассматривается, и при совпадении прочих условий может быть терминирован с ошибкой ORA-00060 через 3 секунды после начала, см. там же объяснение Tanel Poder
_memory_broker_stat_interval
параметр, определяющий частоту операций (в секундах) перераспределения (resize) памяти между разными разделами SGA (чаще всего, между buffer cache и shared pool) при использовании Automatic Shared Memory Management (ASMM)
SQL> @param _memory_broker_stat_interval NAME VALUE IS_DEF IS_MOD IS_ADJ DSC ----------------------------- ------ -------- ---------- -------- -------------------------------------------------------- _memory_broker_stat_interval 30 TRUE SYSTEM_MOD FALSE memory broker statistics gathering interval for auto sga
FREQUENT RESIZE OF SGA [ID 742599.1]
SQL> alter system set "_memory_broker_stat_interval"=999; --- 999 секунд между операциями (<em>resize</em>)
Изменение параметра увеличивает время между операциями до 999 секунд, как минимум
Таким образом уменьшается количество операций (resizes)
_memory_broker_stat_interval определяется в секундах, по умолчанию 30 сек.
Можно установить _memory_broker_stat_interval в большее значение
Изменение параметра должно быть выполнено на всех нодах RAC
Bug 7189722 – Frequent grow/shrink SGA resize operations [ID 7189722.8] – в версиях Oracle < 10.2.0.5 при частых операциях resize возможно увеличение параметра, см. также обсуждение в Jonathan Lewis blog: SGA Resizing
Увеличение значения также может быть полезно, как один из методов уменьшения ожиданий, связанных с конкуренцией за место в shared pool и вызываемых частыми операциями автоматического изменения размеров компонентов SGA – см. High ‘cursor: pin S wait on X’ and/or ‘library cache lock’ Waits Generated by Frequent Shared Pool/Buffer Cache Resize Activity [ID 742599.1]
_fix_control
Init.ora Parameter “_FIX_CONTROL” [Hidden] Reference Note [ID 567171.1] _FIX_CONTROL – специальный скрытый параметр, добавленный в 10.2.0.2, позволяющий включать/выключать определённые bug fixes[, влияющие на работу оптимизатора и перечисляемее в трейсе уровня 10053]… Состояние конкретного bug fix по умолчанию (ON или OFF) часто определяется значением параметра OPTIMIZER_FEATURES_ENABLE… …Полный список номеров багов, управляемых механизмом fix control можно получить из V$ обзоров: V$SYSTEM_FIX_CONTROL V$SESSION_FIX_CONTROL например: alter session set “_fix_control”=’4969880:ON’; alter session set “_fix_control”=’4969880:ON’,’5483301:off’; или в the init.ora _fix_control=’4969880:ON’ примеры применения в блоге El-caro
_OPTIMIZER_IGNORE_HINTS
инструктирует оптимизатор игнорировать подсказки в запросах, по умолчанию функция отключена (FALSE) доступен с Oracle 10, рекомендуется использовать для тестирования приложений при обновлении бд на новые, улучшенные версии оптимизатора – см., например Upgrading from Oracle Database 10g to 11g: What to expect from the Optimizer
_gby_hash_aggregation_enabled
разрешает (значение TRUE по умолчанию)/запрещает (FALSE) использование операции HASH UNIQUE вместо SORT UNIQUE установка “_gby_hash_aggregation_enabled”=false помогает исключить возникновение ora-600 [qeshQBNextLoad.1] на версиях вплоть до 11.1.0.7 включительно на запросах (SELECT) с группировкой, идея взята из Bug 8210423: SPORADIC ORA-00600 [qeshQBNextLoad.1] FAILURES ON INSERT
NLS_SORT
“При установке параметра NLS_SORT в любое значение, кроме BINARY сортировки [порождаемые, в частности операторами ORDER BY в запросах] будут использовать full table scan, независимо от выбираемых Oracle оптимальных методов доступа к данным. Значение NLS_SORT=BINARY является исключением, поскольку индексы строятся в соответствии с бинарным порядком ключей… Если параметр NLS_SORT [при выполнении запроса] установлен в любое другое (языковое) значение, оптимизатор будет вынужден включить full table scan с полной сортировкой в план выполнения запроса” Например, при установке переменной окружения NLS_LANG=RUSSIAN_CIS.CL8MSWIN1251 или
SQL> alter session set NLS_SORT='RUSSIAN';
или
SQL> alter session set NLS_LANGUAGE='RUSSIAN';
получаем значение NLS_SORT:
SQL> select * from nls_session_parameters where parameter = 'NLS_SORT'; PARAMETER VALUE ----------------- ------- NLS_SORT RUSSIAN
и “дорогой” план выполнения с использованием TABLE FULL SCAN и операцией сортировки (в этом случае SORT ORDER BY STOPKEY) с использованием временного табличного пространства (размер временных сегментов указан в столбце TempSpc)
--------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | --------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 41 | 47150 | | 122K (1)| 00:24:26 | |* 1 | COUNT STOPKEY | | | | | | | | 2 | VIEW | | 507K| 556M| | 122K (1)| 00:24:26 | |* 3 | SORT ORDER BY STOPKEY| | 507K| 221M| 233M| 122K (1)| 00:24:26 | |* 4 | HASH JOIN RIGHT SEMI| | 507K| 221M| 45M| 72974 (1)| 00:14:36 | |* 5 | TABLE ACCESS FULL | SysContactRight | 507K| 39M| | 5515 (1)| 00:01:07 | | 6 | TABLE ACCESS FULL | Contact | 1941K| 696M| | 29595 (1)| 00:05:56 | ---------------------------------------------------------------------------------------------------
Использование отсортированного индекса по полю CHAR_COLUMN для исключения сортировки невозможно, т.к. при трансформации запроса оптимизатор меняет ORDER BY CHAR_COLUMN на ORDER BY NLSSORT(CHAR_COLUMN, ‘NLS_SORT = RUSSIAN’), а для функции NLSSORT(CHAR_COLUMN, ‘NLS_SORT = RUSSIAN’) индекса нет :(
Если установить NLS_LANG=english.CL8MSWIN1251 или
SQL> alter session set nls_language='AMERICAN';
или
SQL> alter session set nls_sort='BINARY';
получим:
SQL> select * from nls_session_parameters where parameter = 'NLS_SORT';
PARAMETER VALUE --------------- ------ NLS_SORT BINARY
и гораздо более красивый/дешёвый/быстый план для того же запроса – без необходимости временных сегментов, без операций сортировки, с индексным доступом к данным и единственной медленной операцией INDEX FULL SCAN по полю сортировки:
------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 41 | 47150 | 530 (0)| 00:00:07 | |* 1 | COUNT STOPKEY | | | | | | | 2 | VIEW | | 41 | 47150 | 530 (0)| 00:00:07 | | 3 | NESTED LOOPS SEMI | | 41 | 18737 | 530 (0)| 00:00:07 | | 4 | TABLE ACCESS BY INDEX ROWID| Contact | 1896K| 680M| 158 (0)| 00:00:02 | | 5 | INDEX FULL SCAN | Idx_Contact_CHAR_COLUMN | 157 | | 3 (0)| 00:00:01 | |* 6 | TABLE ACCESS BY INDEX ROWID| SysContactRight | 132K| 10M| 3 (0)| 00:00:01 | |* 7 | INDEX RANGE SCAN | Idx_SysContactRight | 1 | | 2 (0)| 00:00:01 | -------------------------------------------------------------------------------------------------------------
В последнем случае в трейсе 10053 появляется секция:
****** Recost for ORDER BY (using index) ************
т.е. операция сортировки производится в процессе чтения отсортированных ключей индекса и не требуется отдельно
Соответственно, при необходимости национальной сортировки можно использовать function-based index на NLSSORT(COLUMN_NAME) с соответствующим изменением текста запросов на ORDER BY NLSSORT(COLUMN_NAME)
_pga_max_size
максимальный размер PGA, выделяемой для одного выделенного (dedicated) процесса, начиная с 10.2 по умолчанию автоматически устанавливается в значение ~ 20% PGA при автоматическом управлении:
NAME VALUE IS_DEF IS_MOD IS_ADJ DSC --------------------- ----------- ------ ---------- -------- ------------------------------------------------------------------ workarea_size_policy auto FALSE FALSE FALSE policy used to size SQL working areas (MANUAL/AUTO) pga_aggregate_target 7516192768 FALSE FALSE FALSE Target size for the aggregate PGA memory consumed by the instance _pga_max_size 1503232000 TRUE FALSE FALSE Maximum size of the PGA memory for one process
Изменяется динамически:
SQL> alter system set "_pga_max_size"=4G;
System altered.
Может быть использован для [временного] исключения ошибок типа ORA-04030: out of process memory when trying to allocate 32680 bytes (session heap,ktspsrchche: L2s)
_serial_direct_read
Судя по описанию должен регулировать использование режима direct read – чтения блоков бд с использованием PGA / в обход SGA при непараллельном (serial) выполнении (в действительности явно не влияет – Oracle 11g: автоматическое управление памятью (ASMM))
До Oracle 11.2.0.1 вкючительно значение по умолчанию FALSE:
NAME VALUE IS_DEF IS_MOD IS_ADJ DSC -------------------- ------ -------- ---------- -------- ---------------------------- _serial_direct_read FALSE TRUE FALSE FALSE enable direct read in serial
Начиная с 11.2.0.2 значение по умолчанию AUTO, что может значительно менять распределение блоков бд в SGA при операциях FULL TABLE SCAN / INDEX FAST FULL SCAN:
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production With the Partitioning, Automatic Storage Management, OLAP, Data Mining and Real Application Testing options SQL> @param _serial_direct_read NAME VALUE IS_DEF IS_MOD IS_ADJ DSC -------------------- ------ -------- ---------- -------- ---------------------------- _serial_direct_read auto TRUE FALSE FALSE enable direct read in serial
_db_block_buffers
статический параметр, отражает размер buffer cache на момент старта инстанса при использовании ASMM, начиная с Oracle 11g, напрямую влияет на параметр
_small_table_threshold
в числе прочих параметров, непосредственно влияет на выбор доступа к блокам объектов бд при операциях полного многоблочного сканировании (FULL TABLE SCAN, INDEX FAST FULL SCAN) между чтением через буферный пул SGA (блоки кэшируются) и direct path read (блоки таблицы не кэшируются и перечитываются в PGA при каждом последующем выполнении запроса)
Для Oracle 11.1 вычисляется статически как ~2% от _db_block_buffers на момент старта инстанса
Для непараллельного сканирования (serial direct path read) при превышении размера таблицы (в блоках бд) величины ~ _small_table_threshold*5 таблица считается большой и кэш SGA не используется – см. Oracle 11g: автоматическое управление памятью (ASMM)
При параллельном сканировании (parallel direct path read или просто direct path read) переключение может происходить, если размер таблицы (в блоках) превышает _small_table_threshold <- How does Oracle load data into the buffer cache for table scans ? [ID 787373.1]:
If the number of blocks to be read is lower than or equal to the setting of the parameter [_small_table_threshold] Oracle will load the object via the buffer cache as this is more efficient than doing a direct read operation
http://jonathanlewis.wordpress.com/2011/03/24/small-tables/
_OPTIMIZER_COST_BASED_TRANSFORMATION
с версии 10.1
контролирует использование Cost-Based Query Transformation при компиляции/разборе запроса (hard parsing)
допустимые значения:
EXHAUSTIVE
ITERATIVE
LINEAR (по умолчанию)
ON
OFF
TWO_PASS (начиная с 11.2)
Init.ora Parameter “_OPTIMIZER_COST_BASED_TRANSFORMATION” [Hidden] Reference Note [ID 567354.1]
_kghdsidx_count
SQL> @param_ _kghdsidx_count NAME VALUE IS_DEF IS_MOD IS_ADJ DSC ---------------- ----- ------ ------ ------ ------------------ _kghdsidx_count 4 TRUE FALSE FALSE max kghdsidx count
определяет количество создаваемых subpool‘ов для shared pool / large pool на момент (ре)старта инстанса / создания SGA
Метод автоматического расчёта – How To Determine The Default Number Of Subpools Allocated During Startup [ID 455179.1]
ORA-4031 Troubleshooting Tool – в этом инструменте MOS рекомендуется ручная модификация в случае проблем типа Imbalanced Subpool Allocations
_connect_by_use_union_all
между 10.2.0.4/11.1.0.7 и 11.2.0.1 значение параметра по умолчанию меняется с old_plan_mode на true, изменяя планы запросов, использующих CONNECT BY:
11.2.SQL> @param_ _connect_by_use_union_all NAME VALUE IS_DEF IS_MOD IS_ADJ DSC -------------------------- ------ ------- ------- ------- ---------------------------- _connect_by_use_union_all TRUE TRUE FALSE FALSE use union all for connect by 11.2.SQL> @pvalid_ _connect_by_use_union_all PARAMETER VALUE DEFAULT -------------------------- -------------- ------- _connect_by_use_union_all FALSE TRUE DEFAULT OLD_PLAN_MODE
про особенности и связанные баги – в блоге Тимура Ахмадеева
Параметры технологий Bind Aware Cursor Sharing / Cardinality Feedback
_optimizer_adaptive_cursor_sharing
Контролирует применение Adaptive Cursor Sharing (ACS), допустимые значения:
- TRUE – по умолчанию, применение ACS разрешено
- FALSE – отключает ACS, при этом все курсоры считаются BIND AWARE, с последующим применением Extended Cursor Sharing (ECS)
_optimizer_extended_cursor_sharing_rel
Контролирует применение ECS и Cardinality Feedback (CF) “для реляционных предикатов”, допустимые значения:
- SIMPLE – по умолчанию, применение ECS и CF разрешено “для реляционных предикатов” в широком смысле, включая конструкции: LIKE, INSTR, SUBSTR,…
- NONE – ECS (+ACS) и CF не применяется для тех же случаев
Примеры: Элементы технологий Bind Aware Cursor Sharing / Cardinality Feedback
Параметры кэширования результатов запросов
RESULT_CACHE_MODE
11.2.0.3.@SQL> @param RESULT_CACHE_MODE
NAME VALUE IS_DEF IS_MOD DSC
------------------ ------- -------- ------- --------------------------------
result_cache_mode MANUAL TRUE FALSE result cache operator usage mode
11.2.0.3.@SQL> @pvalid RESULT_CACHE_MODE
PARAMETER ORD VALUE DEFAULT
------------------ ---------- ------------------------------ -------
result_cache_mode 1 AUTO
2 MANUAL DEFAULT
3 FORCE
управляет использованием кэша результатов на уровне сессии|системы – планы выполнения запросов строятся с использованием оператора RESULT CACHE
В ручном режиме (значение MANUAL, по умолчанию) использование кэша регулируется подсказкой RESULT_CACHE на уровне целого запроса или его части (inline view, subquery_factoring_clause – конструкции WITH)
В форсированном режиме (FORCE) Oracle будет пытаться использовать кэш для всех запросов. Для отключения кэша на уровне запроса используется подсказка NO_RESULT_CACHE
Режим AUTO вплоть до 11.2.0.3 не работает см. Пример применения Result Cache на стороне сервера
Параметры общего администрирования / backup / recovery
_ALLOW_RESETLOGS_CORRUPTION
Недокументированный и неподдерживаемый параметр, позволяющий открывать бд в отсутствии необходимых для восстановления redolog / archivelog файлов
PREVENTION, DETECTION AND REPAIR OF DATABASE CORRUPTION: “При установке _ALLOW_RESETLOGS_CORRUPTION=TRUE проверки заголовков файлов (file header) [при открытии бд] не производится”
418476.1 RECOVER A DATAFILE WITH MISSING ARCHIVELOGS
Как открыть БД с повреждёнными (недоступными) log-файлами
В документе When to use unsupported parameters _corrupt_rollback_segments and others. [ID 1360786.1], который как раз и посвящён неосмотрительному применению сильно неподдерживаемых параметров при восстановлении бд и настоятельно рекомендует (“It is imperative that these parameters are not used without contacting Oracle first“) во избежание потери данных не применять эти параметры без предварительного контакта с техподдержкой Oracle:
_OFFLINE_ROLLBACK_SEGMENTS - неподдерживаемый параметр init.ora, применение которого может привести к повреждению файлов бд (logical database corruption)
_CORRUPTED_ROLLBACK_SEGMENTS – более опасен чем _OFFLINE_ROLLBACK_SEGMENTS. Использование этого параметра предотвращает доступ к заголовкам перечисленных сегментов отката (rollback segments headers), предполагая все транзакции завершёнными (committed). Это может легко привести к повреждению бд (logical database corruption)
log_archive_max_processes
ограничивает количество ARCn процессов, при использовании которых для архивации логов на physical standby рекомендуемое значение параметра ~ 5*standby_count
Параметры параллельного выполнения
PARALLEL_INSTANCE_GROUP
“Параметр Oracle RAC, использование которого совместно с именами сервисов или параметром INSTANCE_GROUPS позволяет ограничить распределение операций параллельного выполнения по инстансам”
RAC: How to control parallel execution across instances in 10g? [ID 1260674.1]
INST1.instance_groups='INST1' INST1.parallel_instance_group='INST1' # параллельные процессы стартуют и выполняются только на 1-й ноде INST2.instance_groups='INST2' INST2.parallel_instance_group='INST2' # параллельные процессы стартуют и выполняются только на 2-й ноде
Если параметру присвоено значение несуществующего сервиса (service) или группы (INSTANCE_GROUPS) операции будут выполняться непараллельно (serially)
Указание в качестве значения параметра PARALLEL_INSTANCE_GROUP имени сервиса ограничивает параллельное выполнение инстансами, на которых запущен указанный сервис независимо от имени сервиса, использованного при соединении – RAC: How to control parallel execution across instances in 11g? [ID 1207190.1]
PARALLEL_FORCE_LOCAL
Начиная с 11g, используется для ограничения параллельного выполнения одной нодой RAC – при установке параметра в значение TRUE. По умолчанию, такого ограничения нет (FALSE)
TBD
