Oracle mechanics

Параметры

Описание некоторых инициализационных параметров Oracle, влияющих на механизмы оптимизации запросов и производительность бд

optimizer_features_enable

определяет фичи оптимизатора, действующие на уровне сессии / системы, в соответствии с указанным релизом Oracle. Изменение параметра в сторону более низкого релиза отключает новые фичи, исключая новые сопутствующие баги оптимизатора.

12.1.0.2.@ SQL> @pvalid optimizer_features_enable
 
Valid values for parameters %optimizer_features_enable%
 
PVALID_NAME                      ORD PVALID_VALUE PVALID_
------------------------- ----------- ------------ -------
optimizer_features_enable           1 8.0.0        
                                    2 8.0.3        
                                    3 8.0.4        
                                    4 8.0.5        
                                    5 8.0.6        
                                    6 8.0.7        
                                    7 8.1.0        
                                    8 8.1.3        
                                    9 8.1.4        
                                   10 8.1.5        
                                   11 8.1.6        
                                   12 8.1.7        
                                   13 9.0.0        
                                   14 9.0.1        
                                   15 9.2.0        
                                   16 9.2.0.8      
                                   17 10.1.0       
                                   18 10.1.0.3     
                                   19 10.1.0.4     
                                   20 10.1.0.5     
                                   21 10.2.0.1     
                                   22 10.2.0.2     
                                   23 10.2.0.3     
                                   24 10.2.0.4     
                                   25 10.2.0.5     
                                   26 11.1.0.6     
                                   27 11.1.0.7     
                                   28 11.2.0.1     
                                   29 11.2.0.2     
                                   30 11.2.0.3     
                                   31 11.2.0.4     
                                   32 12.1.0.1     
                                   33 12.1.0.2     DEFAULT
                                   34 12.1.0.2.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 между версиями :)

В описании параметра документации версии 12c перечислены фичи оптимизатора (optimizer features), доступные в версиях Oracle, начиная с 9.0.0

Кроме собственно фич, с изменением версии оптимизатора пополняется / изменяется список фиксов (_fix_control), действующих на уровне инстанса / отдельных сессий, отражённый, начиная с версии 11g в обзорах v$system_fix_control / v$session_fix_controlсоответственно:

SQL> select optimizer_feature_enable, count(*)
  2    from v$system_fix_control
  3   where optimizer_feature_enable like '11%'
  4      or optimizer_feature_enable like '12%'
  5   group by optimizer_feature_enable
  6   order by 1
  7  /
 
OPTIMIZER_FEATURE_ENABLE    COUNT(*)
------------------------- ----------
11.1.0.6                          41
11.1.0.7                          19
11.2.0.1                          66
11.2.0.2                         104
11.2.0.3                          80
11.2.0.4                         133
12.1.0.1                          50

Не рекомендуется менять значение параметра, в особенности на уровне системы:
Why you shouldn’t set OPTIMIZER_FEATURES_ENABLE
Use Caution if Changing the OPTIMIZER_FEATURES_ENABLE Parameter After an Upgrade (Doc ID 1362332.1)

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]

_disable_cursor_sharing

Доступен с 11.2.0.3

Позволяет форсировать создание новых курсоров (hard parse) для каждого выполнения запросов со связанными переменными

Как форсировать разбор SQL при каждом выполнении…

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) и регулируется фиксом:

SQL> @fix 7452863
 
  BUGNO VALUE SQL_FEATURE             DESCRIPTION                                                 OPTIMIZER_FEATURE_ENABLE  EVENT IS_DEFAULT
------- ----- ----------------------- ----------------------------------------------------------- ------------------------- ----- ----------
7452863     1 QKSFM_PARTITION_8284930 adjust DS level for large parallel tables based on the size 11.2.0.1                      0          1

Однако, если параметр 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_index_cost_adj на оптимизатор версии 11.1

[_]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

_OPTIMIZER_INVALIDATION_PERIOD

определяет период, в течение которого будут инвалидированы зависимые курсоры после изменения статистики объектов бд процедурами DBMS_STATS.GATHER_xxxxx_STATS, DBMS_STATS.SET_xxxxx_STATS,… с параметром NO_INVALIDATE => DBMS_STATS.AUTO_INVALIDATE (по умолчанию), по умолчанию 18000 секунд (5 часов):

SQL> @param_ _optimizer_invalidation_period

NAME                             VALUE  DSC
-------------------------------- ------ -----------------------------------------------------------
_optimizer_invalidation_period   18000  time window for invalidation of cursors of analyzed objects

Understanding how DBMS_STATS.AUTO_INVALIDATE works [ID 1476807.1]

Параметр пакета DBMS_STATS.NO_INVALIDATE

_OPTIM_PEEK_USER_BINDS

Контролирует механизм bind peeking, default TRUE = включен,для отключения:

SQL> alter session set "_OPTIM_PEEK_USER_BINDS"=FALSE;
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

Появился в версии Oracle 10g R2, начиная с Oracle 11.1 заменён отдельными параметрами COMMIT_LOGGING, COMMIT_WAIT

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’ — тем не менее рекомендуется к взвешенному рассмотрению, как один из надёжных способов уменьшения ожиданий WAITEVENT: «log file sync» Reference Note [ID 34592.1]:

Элегантный тест опций 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

NAME                     VALUE  IS_DEF  DSC
------------------------ ------ ------- ----------------------------------------
_optimizer_ignore_hints  FALSE  TRUE    enables the embedded hints to be ignored

_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
_smm_max_size
_smm_px_max_size

Начиная с 10.2, производные от размера PGA_AGGREGATE_TARGET (PAT) параметры, определяющие максимальный размер памяти, выделяемой выделенным (dedicated) процессам при автоматическом управлении распределением PGA:

NAME                    VALUE        IS_DEF   DSC
----------------------- ------------ -------- -----------------------------------------------------------------
workarea_size_policy    auto         FALSE    policy used to size SQL working areas (MANUAL/AUTO)               --
PGA_AGGREGATE_TARGET    10737418240  FALSE    Target size for the aggregate PGA memory consumed by the instance -- 10G
__pga_aggregate_target  10737418240  FALSE    Current target size for the aggregate PGA memory consumed         -- текущее значение
_pga_max_size           2147471360   TRUE     Maximum size of the PGA memory for one process                    -- макс.PGA процесса ~20 % PAT
_smm_max_size           1048570      TRUE     maximum work area size in auto mode (serial)                      -- макс.WA ~ 10% PAT, kilobytes
_smm_px_max_size        5242880      TRUE     maximum work area size in auto mode (global)                      -- макс.суммарная WA доступная процессам PX-slave при выполнении одного запроса ~ 50% PAT, kilobytes

https://forums.oracle.com/forums/message.jspa?messageID=3153769#3154639

http://www.sql.ru/forum/actualthread.aspx?tid=891804&pg=1&mid=11524272#11524272

How To Super-Size Work Area Memory Size Used By Sessions? [ID 453540.1]

Динамическое изменение:

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/

_very_large_object_threshold

Доступен начиная с версии 11.2, модифицируемый на уровне системы, сессии

Определяет способ чтение индексных блоков при операции INDEX FAST FULL SCAN (IFFS): ежели сегмент индекса занимает более процентов в буферном кэше SGA (рассчитанных от _db_block_buffers), чем значение этого параметра — чтение блоков будет выполняться в PGA, минуя общий кэш операцией serial direct path read, в противном случае блоки индекса будут читатся стандартным более медленным способом через buffer cache SGA.

Кроме этого, параметр (% от размера кэша) влияет на возможность отключения direct read с использованием event 10949

В 11.2 значение параметра по умолчанию 500, т.е. при выполнении IFFS будет использоваться serial direct read только для индексов в 5 раз превышающих размер буферного кэша, определяемого параметром _db_block_buffers

Параметр _very_large_object_threshold

_direct_read_decision_statistics_driven

Optimizer statistics-driven direct path read decision for full table scans (_direct_read_decision_statistics_driven)

11.2.0.2.@ SQL> @param_ _direct_read_decision_statistics_driven

NAME                                       VALUE  IS_DEF   DSC
------------------------------------------ ------ -------- ---------------------------------------------------------
_direct_read_decision_statistics_driven    TRUE   TRUE     enable direct read decision based on optimizer statistics

Начиная с 11.2.0.2 при выборе между direct path | conventional path read при чтении сегмента параметр определяет источник данных о размере сегмента:

  • При значени FALSE данные берутся из заголовка сегмента ALL_SEGMENTS, т.е используется действительное кол-во блоков, занятых сегментом
  • TRUE — данные берутся из статистики объектов (TAB$, IND$), допускающей отклонение и модификации

_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

Про особенности и связанные баги — в блоге Тимура Ахмадеева

Другие попавшиеся примеры по версиям:

11.2.0.2.@ SQL> SELECT *
  2    FROM (select *
  3            from gv$sql_plan
  4           where sql_id = '57pfs5p8xc07w'
  5             and child_number = 0
  6             and inst_id = 1) pt
  7  CONNECT BY PRIOR pt.id = pt.parent_id
  8   START WITH pt.id = 0
  9  /
          from gv$sql_plan
               *
ERROR at line 3:
ORA-00600: internal error code, arguments: [qctVCO:csform], [0], [0], [0], [0], [112], [2], [224], [], [], [], []

-- Bug 9906422 - ORA-600 [qctVCO:csform] from query using WITH, CONNECT BY and CLOBs / ORA-979 with CONNECT BY and SUBSTR [ID 9906422.8]

11.2.0.2.@ SQL> SELECT --+ opt_param('_connect_by_use_union_all' 'false')
  2   *
  3    FROM (select *
  4            from gv$sql_plan
  5           where sql_id = '57pfs5p8xc07w'
  6             and child_number = 0
  7             and inst_id = 1) pt
  8  CONNECT BY PRIOR pt.id = pt.parent_id
  9   START WITH pt.id = 0
 10  /
          from gv$sql_plan
               *
ERROR at line 4:
ORA-00600: internal error code, arguments: [qctVCO:csform], [0], [0], [0], [0], [112], [2], [224], [], [], [], []

-- изменение на уровне запроса с помощью OPT_PARAM не помогает, работает изменение параметра на уровне сессии:

11.2.0.2.ORC11202@SYS SQL> alter session set "_connect_by_use_union_all"=false;

Session altered.

11.2.0.2.@ SQL> SELECT
  2   *
  3    FROM (select *
  4            from gv$sql_plan
  5           where sql_id = '57pfs5p8xc07w'
  6             and child_number = 0
  7             and inst_id = 1) pt
  8  CONNECT BY PRIOR pt.id = pt.parent_id
  9   START WITH pt.id = 0
 10  /

no rows selected.

11.2.0.3.@ SQL> SELECT id as OPERATION_ID,
  2  lpad(' ', 2 * level) || pt.operation || ' ' || pt.options "Query Plan", pt.object_owner, pt.object_name
  3    FROM
  4         (select p.* from gv$sql_plan p, gv$sql s
  5                  where p.sql_id = 'cwza3a0w7n13n'
  6                    and p.child_number = 0
  7                    and p.inst_id = 1
  8                    and p.inst_id = s.inst_id
  9                    and p.sql_id = s.sql_id
 10                    and p.child_number = s.child_number
 11                    and p.address = s.address
 12                    and s.is_obsolete <> 'Y'
 13         ) pt
 14  CONNECT BY PRIOR pt.id = pt.parent_id
 15   START WITH pt.id = 0
 16  /
SELECT id as OPERATION_ID,
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
Process ID: 15121
Session ID: 315 Serial number: 34277

-- from alert.log:
Exception [type: SIGSEGV, Address not mapped to object] [ADDR:0x0] [PC:0x910A803, qksopCheckPropOpt()+1031] [flags: 0x0, count: 1]

-- looks like Bug 15921179 : ORA-7445 [QKSOPCHECKPROPOPT()+1031] USING START WITH AND CONNECT BY
-- but in 11.2.0.3 OPT_PARAM works on query level:

11.2.0.3.@ SQL> SELECT /*+ opt_param('_connect_by_use_union_all' 'false') */ id as OPERATION_ID,
  2  lpad(' ', 2 * level) || pt.operation || ' ' || pt.options "Query Plan", pt.object_owner, pt.object_name
  3    FROM
  4         (select p.* from gv$sql_plan p, gv$sql s
  5                  where p.sql_id = 'cwza3a0w7n13n'
  6                    and p.child_number = 0
  7                    and p.inst_id = 1
  8                    and p.inst_id = s.inst_id
  9                    and p.sql_id = s.sql_id
 10                    and p.child_number = s.child_number
 11                    and p.address = s.address
 12                    and s.is_obsolete <> 'Y'
 13         ) pt
 14  CONNECT BY PRIOR pt.id = pt.parent_id
 15   START WITH pt.id = 0
 16  /

no rows selected.

Параметры технологий 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

USE_LARGE_PAGES

добавлен в 11.2.0.2 для облегчения управления использованием hugepages в бд — USE_LARGE_PAGES To Enable HugePages In 11.2 [ID 1392497.1]

11.2.0.3.@ SQL> @pvalid USE_LARGE_PAGES

Valid values for parameters %USE_LARGE_PAGES%

PARAMETER       ORD VALUE  DEFAULT
--------------- --- ------ -------
use_large_pages   1 TRUE   DEFAULT
                  2 AUTO           -- доступно с 11.2.0.3: задействуется процесс oradism для временного (без внесения изменений в /etc/sysctl.conf) изменения параметров ядра Linux - увеличения кол-ва hugepages
                  3 ONLY
                  4 FALSE

USE_LARGE_PAGES=ONLY не позволяет стартовать инстанс в случае невозможности разместить SGA в  hugepages

SKIP_UNUSABLE_INDEXES

полное описание в документации версий >= 11.2

SQL> @param skip_unusable_indexes

NAME                   VALUE  IS_DEF    DSC
---------------------- ------ --------  ------------------------------------
skip_unusable_indexes  TRUE   TRUE      skip unusable indexes if set to TRUE

Индекс в статусе unusable, подсказка INDEX и обновлённый RBO в Oracle 11.2
Проблема индексного доступа при наличии unusable index subpartition в версиях до 11g

_DATAFILE_WRITE_ERRORS_CRASH_INSTANCE

доступен с 11.2.0.2, значения:

TRUE (default) — любая неудачная попытка записи в не-SYSTEM файл данных по причине IO error инициирует аварийную остановку только этого инстанса (instance crash)
FALSE — поведение остаётся неизменным (как было до фикса): ошибка записи в не-SYSTEM файл данных меняет статус файла данных на offline (для всей бд), что потребует выполнения media recovery в дальнейшем

Bug 7691270 Crash the DB in case of write errors (rather than just offline files)
Job Oprel. Param ‘_datafile_write_errors_crash_instance’ , TRUE or FALSE?

JOB_QUEUE_PROCESSES

управление макс.кол-вом Job Queue Slaves процессов Jnnn инстанса, выполняющих задания DBMS_JOB / DBMS_SCHEDULER
Установка JOB_QUEUE_PROCESSES = 0 отключает выполнение этих заданий, и останавливает процесс Job Queue Coordinator CJQ0, который перезапускается при последующем увеличении параметра

Параметры параллельного выполнения

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)

Параметры FAST_START_PARALLEL_ROLLBACK и PARALLEL_MAX_SERVERS

Поиск избыточных PX-slave процессов, остающихся, например, после уменьшения PARALLEL_MAX_SERVERS:

select host_name, 'kill -9 '||spid from gv$px_process px join gv$instance using (inst_id) where px.status = 'AVAILABLE';

Параметры мониторинга выполнения запросов (Real-Time SQL Monitoring)

в обзорах V$SQL_MONITOR / V$SQL_PLAN_MONITORDoug’s Oracle Blog post:

11.2.@ SQL> @param_ sqlmon

NAME                      VALUE    DSC
------------------------- -------- ---------------------------------------------------------------------------
_sqlmon_binds_xml_format  default  format of column binds_xml in [G]V$SQL_MONITOR
_sqlmon_max_plan          320      Maximum number of plans entry that can be monitored. Defaults to 20 per CPU -- макс.кол-во наблюдаемых планов
_sqlmon_max_planlines     300      Number of plan lines beyond which a plan cannot be monitored                -- макс.размер наблюдаемых планов
_sqlmon_recycle_time      60       Minimum time (in s) to wait before a plan entry can be recycled
_sqlmon_threshold         5        CPU/IO time threshold before a statement is monitored. 0 is disabled        -- критерий наблюдения в секундах

Параметр _sqlmon_max_planlines меняется на уровне сессии, что удобно для включения мониторинга отдельных запросов с большим количеством строк в плане выполнения

Названия внутренних таблиц:

SQL> select view_name, substr(view_definition, -20)
  2    from v$fixed_view_definition
  3   where view_name like 'GV$SQL%MONITOR'
  4  /

VIEW_NAME                      SUBSTR(VIEW_DEFINITION,-20)
------------------------------ ---------------------------
GV$SQL_MONITOR                 time from X$KESWXMON
GV$SQL_PLAN_MONITOR            from X$KESWXMON_PLAN

совпадают с названиями областей shared pool SGA для данных мониторинга:

SQL> select * from v$sgastat where name like 'keswx%' ;

POOL         NAME                                            BYTES
------------ ------------------------------------------ ----------
shared pool  keswxNotify:tabPlans                            16384
shared pool  keswx:batch o                                  104272
shared pool  keswx:plan en                                  645216

Кластерные параметры

_gc_fusion_compression (по ссылке тесты)

с 11.2 определяет минимумальное кол-во свободного места в блоке бд для использования Global Cache Fusion Compression: «вместо передачи целого блока, LMS передаёт меньшую, только непустую часть блока, уменьшая тем самым сетевой трафик»

11.2.0.3.@ SQL> @param_ _gc_fusion_compression

NAME                    VALUE  DSC
----------------------- ------ ---------------------------------------------
_gc_fusion_compression  1024   compress fusion blocks if there is free space

Прочие параметры

_use_nosegment_indexes

начиная, 8.1.7 позволяет использовать виртуальные индексы (созданные с кляузой NOSEGMENT) при построении плана выполнения запроса:

SQL> @param_ _use_nosegment_indexes

NAME                   VALUE IS_DEF IS_MOD IS_ADJ DSC
---------------------- ----- ------ ------ ------ -------------------------------------
_use_nosegment_indexes FALSE TRUE   FALSE  FALSE  use nosegment indexes in explain plan

Tim Hall. Virtual Indexes

_DISABLE_ODM

Параметр для управления ODM — Oracle Disk Manager

_ASM_ALLOW_ONLY_RAW_DISKS

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

_ASM_AUSIZE / _ASM_STRIPESIZE

Скрытые параметры ASM metadata storage, определяющие размеры соответственно COARSE и FINE striping со следующими значениями по умолчанию:

NAME               VALUE      IS_DEF   DSC
------------------ ---------- -------- --------------------
_asm_ausize        1048576    TRUE     allocation unit size
_asm_stripesize    131072     TRUE     ASM file stripe size

, для VLDB рекомендуют увеличить — Deployment of very large databases (10TB to PB range) with Automatic Storage Management (ASM) (Doc ID 368055.1) значения с установкой FINE striping для файлов всех типов:

o _asm_ausize=16777216
o _asm_stripesize=1048576

Параметры действуют в момент создания ASM Disk Group и, если найти значение V$ASM_DISKGROUP.ALLOCATION_UNIT_SIZE просто, то значение _asm_stripesize для уже созданной DG найти не совсем тривиально — см. у Максима Филатова How to know _asm_stripesize for disk group, либо оценить через alert+ASM.log
Узнать этот параметр бывает важно в случаях, например, нестандартной установки параметров, вследствие чего возможны всякие чудные Bug 20286051 : LGWR SPINNING 90% CPU WAITING FOR LOG FILE PARALLEL WRITE WITH REDOLOGS ON ASM

_mv_refresh_costing

метод определения и/или порядок применения методов обновления матвью / mview refresh:

 
SQL> @param_ _mv_refresh_costing
 
NAME                 VALUE     IS_DEF   IS_MOD     IS_ADJ   DSC
-------------------- --------- -------- ---------- -------- ------------------------------------------
_mv_refresh_costing  rule      TRUE     FALSE      FALSE    refresh decision based on cost or on rules
 
10-11g@ SQL> @pvalid_ _mv_refresh_costing
 
PVALID_NAME                                        PVALID_VALUE
-------------------------------------------------- -------------------------
_mv_refresh_costing                                COST
_mv_refresh_costing                                RULE             -- default
_mv_refresh_costing                                RULE_FA_PD_PT_CO -- default?
_mv_refresh_costing                                RULE_FA_PT_PD_CO
_mv_refresh_costing                                RULE_PD_PT_FA_CO
_mv_refresh_costing                                RULE_PT_PD_FA_CO
 
12c@ SQL> @pvalid_ _mv_refresh_costing
 
PVALID_NAME                                        PVALID_VALUE
-------------------------------------------------- -------------------------
_mv_refresh_costing                                COST
_mv_refresh_costing                                RULE                      -- default
_mv_refresh_costing                                RULE_FA_OF_PD_PT_OP_CO_OC -- default?
_mv_refresh_costing                                RULE_FA_OF_PT_PD_OP_CO_OC
_mv_refresh_costing                                RULE_PD_PT_OP_FA_OF_CO_OC
_mv_refresh_costing                                RULE_PT_PD_OP_FA_OF_CO_OC
_mv_refresh_costing                                RULE_OF_FA_OP_PD_PT_OC_CO
_mv_refresh_costing                                RULE_OF_FA_OP_PT_PD_OC_CO
_mv_refresh_costing                                RULE_OP_PD_PT_OF_FA_OC_CO
_mv_refresh_costing                                RULE_OP_PT_PD_OF_FA_OC_CO

--  details:

FA= Fast refresh using mv$logs if possible:
PT= PCT with Truncate if possible
PD= PCT with Delete if possible
CO= COmplete refresh 
OF= Out-of-place Fast
OP= Out-of-place PCT
OC= Out-of-place Complete

TBD

1 комментарий »


RSS feed for comments on this post. TrackBack URI

Добавить комментарий

Заполните поля или щелкните по значку, чтобы оставить свой комментарий:

Логотип WordPress.com

Для комментария используется ваша учётная запись WordPress.com. Выход / Изменить )

Фотография Twitter

Для комментария используется ваша учётная запись Twitter. Выход / Изменить )

Фотография Facebook

Для комментария используется ваша учётная запись Facebook. Выход / Изменить )

Google+ photo

Для комментария используется ваша учётная запись Google+. Выход / Изменить )

Connecting to %s

Создайте бесплатный сайт или блог на WordPress.com.

%d такие блоггеры, как: