Хинты в oracle это
CBO: Оптимизация на основе затрат. Оптимизатор на основе затрат.
CBO оптимизаторСоздайте набор планов выполнения, которые можно использовать в соответствии с оператором SQL, оцените стоимость каждого плана выполнения и вызовите генератор планов (Генератор планов), чтобы сгенерировать план выполнения, сравните стоимость плана выполнения и, наконец, выберите наиболее дешевый план выполнения. ,
CBO состоит из следующих компонентов: Query Transformer, Estimator, Plan Generator
В Oracle 10g есть 2 необязательных режима работы для CBO:
(1) FIRST_ROWS(n)
(2) ALL_ROWS-значение по умолчанию в 10g
Просмотр режима CBO:
Три способа изменить модель CBO:
(1) Уровень сессий
(2) Системный уровень
Oracle предоставляет возможность использовать подсказку для установки типа оптимизатора в SQL на CBO или RBO.
(3) Уровень выписки
использование Hint(/*+ . */) Установить
Оптимизатор на основе затрат (CBO) очень умный, в большинстве случаев он выберет правильный оптимизатор, уменьшая нагрузку на администраторов баз данных. Но иногда он также является умным и ошибочным и выбирает очень плохой план выполнения, что делает выполнение определенного оператора чрезвычайно медленным.
На этом этапе администратору БД необходимо вмешательство человека, чтобы сказать оптимизатору использовать указанный путь доступа или тип соединения для генерации плана выполнения, чтобы оператор выполнялся эффективно. Например, если мы считаем, что более полное сканирование таблицы более эффективно, чем сканирование индекса для определенного оператора, то мы можем поручить оптимизатору использовать полное сканирование таблицы.
В Oracle цель оптимизации оптимизатора вмешательства достигается путем добавления подсказки (подсказки) к выражению.
Oracle Hint - это механизм, который позволяет оптимизатору сгенерировать план выполнения так, как мы его сообщаем.
Мы можем использовать Oracle Hint для достижения:
- 1) Тип используемого оптимизатора
- 2) Целью оптимизации оптимизатора на основе затрат является all_rows или first_rows.
- 3) Независимо от того, является ли путь доступа к таблице полным сканированием таблицы, индексным сканированием или rowid напрямую.
- 4) Тип соединения между таблицами
- 5) Порядок соединения между таблицами
- 6) Степень параллелизма высказываний
При использовании подсказки следует отметить следующее: Не все моменты подсказка работает.
Причины сбоя HINT следующие:
Если CBO считает, что использование подсказки приведет к неверным результатам, подсказка будет проигнорирована.
Если запись в индексе несовместима с записью таблицы из-за нулевого значения, результат неверен, а подсказка игнорируется.
Если в таблице указан псевдоним, псевдоним также должен использоваться в подсказке, иначе подсказка также будет игнорироваться.
- 1
- 2
- 3
- 4
- 5
- 1) DELETE, INSERT, SELECT и UPDATE - это ключевые слова, которые идентифицируют начало блока оператора, и комментарии, содержащие подсказки, могут появляться только после этих ключевых слов, в противном случае подсказка недействительна.
- 2) Знак «+» означает, что примечание является подсказкой, а знак плюс должен следовать сразу за «/ *» без пробелов.
- 3) Подсказка - это одна из конкретных подсказок, описанных ниже. Если имеется несколько подсказок, каждая подсказка должна быть разделена одним или несколькими пробелами.
- 4) текст другой пояснительный текст, который объясняет подсказку
Если вы не укажете Hint правильно, Oracle проигнорирует Hint и не выдаст никаких ошибок.
Другое: только один / после каждой команды SELECT / INSERT / UPDATE / DELETE + /, но может быть несколько приглашений, разделенных запятыми или пробелами.
Такие как:/ *+ ordered index() use_nl() */
Параллельное выполнение связанной подсказки
7. /*+CLUSTER(TABLE)*/
Подсказка ясно указывает, что метод доступа для выбора сканирования кластера для указанной таблицы действителен только для объекта кластера.
Например:
27. / +CACHE(TABLE) /
В операции полного сканирования таблицы, если вы используете это приглашение, Oracle поместит отсканированные блоки данных в LRU (наименее недавно использованный: недавно использованный список, это алгоритм для Oracle для определения активности блоков данных в памяти) Наиболее используемый конец (самый активный конец блока данных), так что блок данных может находиться в памяти в течение более длительного времени.
Если имеется небольшая таблица, к которой часто обращаются, этот параметр повысит производительность запроса. В то же время CACHE также является атрибутом таблицы. Если установлен атрибут кэширования таблицы, он имеет тот же эффект, что и подсказка. После полного сканирования таблицы блок данных Оставайтесь в самом активном конце списка LRU.
12. /*+INDEX_DESC(TABLE INDEX_NAME)*/
Показывает метод сканирования в порядке убывания выбора индекса в таблице.
Например:
30. /*+NOAPPEND*/
Запустите обычные операции вставки, остановив параллельный режим в течение срока действия оператора вставки.
Посказка BITMAP_TREE
оказалось неполным, и в этом, как обычно, помогла проблема с планом критичного запроса типа:
, который, как обычно неожиданно, из 2-х планов выбрал худший (PHV 2429571734 — второй по счёту в нижеприведённом сравнении) — с использование комбинации двух BITMAP ROWIDS индексных пребразований, соответствующих 2-м вхождениям/использованиям оператора OR в запросе — в строках [1] и [2] (more…)
31. NO_INDEX: указать, какие индексы не используются
/ + NO_INDEX ( table [index [index]…] ) /
13. /*+INDEX_FFS(TABLE INDEX_NAME)*/
Выполните быстрое полное сканирование индекса по указанной таблице вместо полного сканирования таблицы.
Например:
Таблица подключения порядок подсказка
3. /*+CHOOSE*/
Указывает, что если в словаре данных есть статистическая информация о таблице доступа, она будет основана на методе оптимизации служебных данных и обеспечит наилучшую пропускную способность.
Покажите, что если в словаре данных отсутствует статистическая информация о таблице доступа, она будет основана на методе оптимизации издержек правила;
Comments on Schema and Nonschema Objects
You can use the COMMENT command to associate a comment with a schema object (table, view, materialized view, operator, indextype, mining model) or a nonschema object (edition) using the COMMENT command. You can also create a comment on a column, which is part of a table schema object. Comments associated with schema and nonschema objects are stored in the data dictionary. Refer to COMMENT for a description of this form of comment.
18. /*+REWRITE*/
Вы можете принять вид в качестве параметра.
parallel
Укажите параллелизм выполнения в SQL, это значение переопределит его собственный параллелизм
Что касается параллельности таблицы, мы можем указать ее при создании таблицы, например:
You can create two types of comments:
Comments within SQL statements are stored as part of the application code that executes the SQL statements.
Comments associated with individual schema or nonschema objects are stored in the data dictionary along with metadata on the objects themselves.
4. /*+RULE*/
Это указывает на то, что метод оптимизации на основе правил выбран для блока предложения.
Например:
5. /*+FULL(TABLE)*/
Указывает, что для таблицы выбран метод глобального сканирования.
Подсказка сообщает оптимизатору доступ к данным через полное сканирование указанной таблицы.
Например:
Следует отметить, что если таблица имеет псевдоним, псевдоним также следует использовать в подсказке
10. /*+INDEX_COMBINE*/
Выберите путь доступа к битовой карте для указанной таблицы. Если индекс в качестве параметра не указан в INDEX_COMBINE, будет выбран метод логической комбинации индекса битовой карты.
Например:
11.03.2019
Функциональный хинт IGNORE_ROW_ON_DUPKEY_INDEX
По следам обсуждения необоснованного роста ASSM табличных сегментов в результате автоматической обработки исключений с использованием добавленной в 11.2 подсказки IGNORE_ROW_ON_DUPKEY_INDEX — Почему занимаемый таблицей размер растет при холостых инсертах?
В версии 12.1.0.1 основная проблема была тихо исправлена (во всяком случае мне не удалось обнаружить упоминаний соотв.бага):
— сегмент не растёт, однако скорость выполнения вставки с использованием такого хинта остаётся весьма невысокой по сравнению, например, с обработкой встроенного исключения DUP_VAL_ON_INDEX в PL/SQL блоке:
Что проще всего, похоже, проверить с помощью INSTEAD OF INSERT триггера (more…)
Подсказки - это механизм, предоставляемый Oracle, чтобы сообщить оптимизатору, чтобы он сгенерировал план выполнения в том виде, в котором мы его указываем. Мы можем использовать подсказки для достижения:
Тип используемого оптимизатора
Целью оптимизации оптимизатора на основе затрат является all_rows или first_rows.
Путь доступа к таблице - это полное сканирование таблицы, сканирование индекса или непосредственно идентификатор строки.
Тип связи между таблицами
Порядок соединения таблиц
2. HINT может работать по следующим правилам.
Порядок подключения таблиц, способ подключения таблиц, путь доступа, параллелизм
3. Область применения HINT
Если язык (синтаксис) неправильный, ORACLE автоматически проигнорирует письменный СОВЕТ и не будет сообщать об ошибке.
Он показывает, что для блока предложений выбран метод оптимизации на основе затрат, достигается лучшая пропускная способность и минимизировано потребление ресурсов.
SELECT /+ALL_ROWS/ EMP_NO,EMP_NAM,DAT_IN FROM BSEMPMS WHERE EMP_NO=‘SCOTT’;
Это указывает на то, что для блока предложений выбран метод оптимизации на основе затрат и достигается лучшее время отклика для минимизации потребления ресурсов.
SELECT /+FIRST_ROWS/ EMP_NO,EMP_NAM,DAT_IN FROM BSEMPMS WHERE EMP_NO=‘SCOTT’;
Указывает, что при наличии статистической информации для доступа к таблице в словаре данных будет использоваться метод оптимизации, основанный на накладных расходах, для получения максимальной пропускной способности;
Указывает, что при отсутствии статистической информации для доступа к таблице в словаре данных используется метод оптимизации, основанный на накладных расходах правила;
SELECT /+CHOOSE/ EMP_NO,EMP_NAM,DAT_IN FROM BSEMPMS WHERE EMP_NO=‘SCOTT’;
Указывает, что для блока предложений выбран метод оптимизации на основе правил.
SELECT /*+ RULE */ EMP_NO,EMP_NAM,DAT_IN FROM BSEMPMS WHERE EMP_NO=‘SCOTT’;
Указывает метод выбора глобального просмотра таблицы.
SELECT /+FULL(A)/ EMP_NO,EMP_NAM FROM BSEMPMS A WHERE EMP_NO=‘SCOTT’;
Приглашение ясно указывает, что доступ к указанной таблице осуществляется в соответствии с ROWID.
SELECT /+ROWID(BSEMPMS)/ * FROM BSEMPMS WHERE ROWID>=‘AAAAAAAAAAAAAA’
В подсказке четко указано, что для указанной таблицы выбран метод доступа к сканированию кластера, который действителен только для объектов кластера.
SELECT /*+CLUSTER */ BSEMPMS.EMP_NO,DPT_NO FROM BSEMPMS,BSDPTMS
WHERE DPT_NO=‘TEC304’ AND BSEMPMS.DPT_NO=BSDPTMS.DPT_NO;
Указывает метод сканирования для выбора индекса в таблице.
SELECT /*+INDEX(BSEMPMS SEX_INDEX) USE SEX_INDEX BECAUSE THERE ARE FEWMALE BSEMPMS */ FROM BSEMPMS WHERE SEX=‘M’;
Указывает метод сканирования для выбора индекса в возрастающем порядке для таблицы.
SELECT /*+INDEX_ASC(BSEMPMS PK_BSEMPMS) */ FROM BSEMPMS WHERE DPT_NO=‘SCOTT’;
Выберите путь доступа к битовой карте для указанной таблицы.Если индекс в качестве параметра не указан в INDEX_COMBINE, будет выбрана логическая комбинация индекса битовой карты.
SELECT /+INDEX_COMBINE(BSEMPMS SAL_BMI HIREDATE_BMI)/ * FROM BSEMPMS
Предложите оптимизатору использовать индекс в качестве пути доступа.
SELECT /+INDEX_JOIN(BSEMPMS SAL_HMI HIREDATE_BMI)/ SAL,HIREDATE
FROM BSEMPMS WHERE SAL
Указывает метод сканирования для выбора индекса в порядке убывания таблицы.
SELECT /*+INDEX_DESC(BSEMPMS PK_BSEMPMS) */ FROM BSEMPMS WHERE DPT_NO=‘SCOTT’;
Выполните быстрое сканирование полного индекса указанной таблицы вместо полного сканирования таблицы.
SELECT /+INDEX_FFS(BSEMPMS IN_EMPNAM)/ * FROM BSEMPMS WHERE DPT_NO=‘TEC305’;
Подсказка сделать четкий выбор плана выполнения и объединить просмотры нескольких индексов в один столбец.
SELECT /+INDEX_FFS(BSEMPMS IN_DPTNO,IN_EMPNO,IN_SEX)/ * FROM BSEMPMS WHERE EMP_NO=‘SCOTT’ AND DPT_NO=‘TDC306’;
Условие OR после WHERE в запросе преобразуется в комбинированный запрос UNION ALL.
SELECT /+USE_CONCAT/ * FROM BSEMPMS WHERE DPT_NO=‘TDC506’ AND SEX=‘M’;
Для оператора запроса OR или IN-LIST после WHERE NO_EXPAND предотвратит его расширение на основе оптимизатора.
SELECT /+NO_EXPAND/ * FROM BSEMPMS WHERE DPT_NO=‘TDC506’ AND SEX=‘M’;
Операция перезаписи запроса блока запроса запрещена.
Вы можете использовать представление как параметр.
Возможность соответствующим образом объединить каждый запрос представления.
SELECT /*+MERGE(V) */ A.EMP_NO,A.EMP_NAM,B.DPT_NO FROM BSEMPMS A (SELET DPT_NO
,AVG(SAL) AS AVG_SAL FROM BSEMPMS B GROUP BY DPT_NO) V WHERE A.DPT_NO=V.DPT_NO
Объединяемые представления больше не объединяются.
SELECT /*+NO_MERGE(V) */ A.EMP_NO,A.EMP_NAM,B.DPT_NO FROM BSEMPMS A (SELECT DPT_NO,AVG(SAL) AS AVG_SAL FROM BSEMPMS B GROUP BY DPT_NO) V WHERE A.DPT_NO=V.DPT_NO AND A.SAL>V.AVG_SAL;
В соответствии с порядком, в котором таблицы появляются в FROM, ORDERED заставляет ORACLE присоединяться к ним в этом порядке.
SELECT /+ORDERED/ A.COL1,B.COL2,C.COL3 FROM TABLE1 A,TABLE2 B,TABLE3 C WHERE A.COL1=B.COL1 AND B.COL1=C.COL1;
Соедините указанную таблицу с источником строк вложенного соединения и используйте указанную таблицу в качестве внутренней.
SELECT /+ORDERED USE_NL(BSEMPMS)/ BSDPTMS.DPT_NO,BSEMPMS.EMP_NO,BSEMPMS.EMP_NAM FROM BSEMPMS,BSDPTMS WHERE BSEMPMS.DPT_NO=BSDPTMS.DPT_NO;
Соедините указанную таблицу с другими источниками строк через соединение сортировки слиянием.
SELECT /+USE_MERGE(BSEMPMS,BSDPTMS)/ * FROM BSEMPMS,BSDPTMS WHERE BSEMPMS.DPT_NO=BSDPTMS.DPT_NO;
Соединить указанную таблицу с другими источниками строк с помощью хэш-соединения.
SELECT /+USE_HASH(BSEMPMS,BSDPTMS)/ * FROM BSEMPMS,BSDPTMS WHERE BSEMPMS.DPT_NO=BSDPTMS.DPT_NO;
Принудительно выполнить запрос к таблице, отличной от местоположения, выбранного ORACLE.
SELECT /+DRIVING_SITE(DEPT)/ * FROM BSEMPMS,[email protected] WHERE BSEMPMS.DPT_NO=DEPT.DPT_NO;
Используйте указанную таблицу в качестве первой таблицы в последовательности подключения.
При выполнении полного сканирования таблицы CACHE предлагает разместить поисковый блок таблицы в буферном кеше, который является наименее использованным концом списка LRU.
SELECT /*+FULL(BSEMPMS) CAHE(BSEMPMS) */ EMP_NAM FROM BSEMPMS;
При выполнении полного сканирования таблицы CACHE предлагает разместить извлекаемый блок таблицы в буферном кеше, который является наименее использованным концом списка LRU.
SELECT /*+FULL(BSEMPMS) NOCAHE(BSEMPMS) */ EMP_NAM FROM BSEMPMS;
Вставка непосредственно в конец стола может увеличить скорость.
insert /+append/ into test1 select * from test4 ;
Запускайте обычные вставки, останавливая параллельный режим на время существования оператора вставки.
insert /+noappend/ into test1 select * from test4 ;
- NO_INDEX: укажите, какие индексы не используются
/*+ NO_INDEX ( table [index [index]…] ) */
select /+ no_index(emp ind_emp_sal ind_emp_deptno)/ * from emp where deptno=200 and sal>300;
select /+ parallel(emp,4)/ * from emp where deptno=200 and sal>300;
Кроме того: после каждой команды SELECT / INSERT / UPDATE / DELETE может быть только один / * + * /, но содержимое приглашения может быть множественным, которое может быть разделено запятыми или пробелами.
Например: / * + упорядоченный индекс () use_nl () * /
Утверждение, подобное следующему: вставить в xxxx select / * + parallel (a) * / * from xxx a; Объем данных составляет около 75 ГБ. Этот брат бегал с утра до полудня и не завершил его. Он подошел и спросил меня, что происходит. Говорят, что вещи, которые можно запустить за 2 часа, обычно работают несколько часов и продолжают двигаться. Проверка производительности системы также относительно нормальная, cpu, io не заняты, средняя скорость READ составляет около 80M / s (почти не работает), но средняя скорость записи составляет всего менее 10M. Ожидание большого количества «PX Deq Credit: send blkd» в случае, здесь вы можете увидеть, что существует проблема с параллельным использованием, и наконец узнали, что существует проблема с параллельным использованием. Операция была завершена через 20 минут после изменения. Правильный подход должен быть:
alter session enable dml parallel;
insert /*+parallel(xxxx,4) / into xxxx select /+parallel(a) */ * from xxx a;
Поскольку Oracle не открывает PDML по умолчанию, операторы DML должны быть включены вручную. Кроме того, я должен сказать, что параллелизм не является масштабируемой функцией. Это всего лишь инструмент для нескольких людей, таких как хранилище данных или администратор базы данных, чтобы полностью использовать ресурсы во время пакетных операций с данными. Однако использование параллелизма в среде OLTP требует большой осторожности. Фактически, PDML по-прежнему имеет много ограничений, таких как отсутствие поддержки триггеров, ссылочных ограничений, расширенной репликации и распределенных транзакций и т. Д., И это также приведет к дополнительному заполнению пространства.
From wid_serv_prod_mon_1100 a
where a.acct_month = 201010
and a.partition_id = 10
and serv_state not in (‘2HB’, ‘2HL’, ‘2HJ’, ‘2HP’, ‘2HF’)
and online_flag in (0)
and incr_product_id in (2000020)
and product_id in (2020966, 2020972, 2100297, 2021116)
and billing_mode_id = 1
and exp_date > to_date(‘201010’, ‘yyyymm’)
and not exists (select /+no_index (b IDX_W_CDR_MON_SERV_ID_1100)/
Метод ассоциации таблиц Подсказка
29. /*+APPEND*/
Вставка непосредственно в конец таблицы может увеличить скорость.
Предложите базе данных загрузить данные в базу данных путем прямой загрузки.
Этот намек используется очень часто. особенно вВставьте большое количество данных, Как правило, используйте этот совет.
29.12.2013
25. / +DRIVING_SITE(TABLE) /
Принудительно задать таблицу с местоположением, отличным от того, которое выбрано ORACLE для выполнения запроса.
Например:
Другое Подсказка
В процессе оптимизации операторов SQL мы часто используем подсказки, а теперь обобщаем общее использование Oracle HINT в процессе оптимизации SQL:
22. /*+USE_NL(TABLE)*/
Подключите указанную таблицу к вложенному связанному источнику строк и используйте указанную таблицу в качестве внутренней таблицы.
В запросе на сопоставление с несколькими таблицами укажите использование гнездовых циклов для сопоставления с несколькими таблицами.
Например:
07.07.2019
Comments Within SQL Statements
Comments can make your application easier for you to read and maintain. For example, you can include a comment in a statement that describes the purpose of the statement within your application. With the exception of hints, comments within SQL statements do not affect the statement execution. Refer to Hints on using this particular form of comment.
A comment can appear between any keywords, parameters, or punctuation marks in a statement. You can include a comment in a statement in two ways:
Begin the comment with a slash and an asterisk (/*). Proceed with the text of the comment. This text can span multiple lines. End the comment with an asterisk and a slash (*/). The opening and terminating characters need not be separated from the text by a space or a line break.
Begin the comment with -- (two hyphens). Proceed with the text of the comment. This text cannot extend to a new line. End the comment with a line break.
Some of the tools used to enter SQL have additional restrictions. For example, if you are using SQL*Plus, by default you cannot have a blank line inside a multiline comment. For more information, refer to the documentation for the tool you use as an interface to the database.
A SQL statement can contain multiple comments of both styles. The text of a comment can contain any printable characters in your database character set.
These statements contain many comments:
8. /*+INDEX(TABLE INDEX_NAME)*/
Указывает метод сканирования для выбора индексов в таблице.
Индексная подсказка сообщает оптимизатору доступ к данным путем индексации указанной таблицы. При доступе к индексу будет получен неполный набор результатов, оптимизатор проигнорирует этот совет.
Индекс используется только тогда, когда в предикате есть поле индекса.
9. /*+INDEX_ASC(TABLE INDEX_NAME)*/
Указывает, что метод сканирования для выбора индекса в порядке возрастания на таблице.
Например:
«Подсказка» BEGIN_OUTLINE_DATA
— с виду безобидная конструкция, обрамляющая (вместе с END_OUTLINE_DATA) список подсказок плана выполнения при выводе в формате:
Интересно, что при применении в виде отдельного хинта/комментария в запросе конструкция /*+ BEGIN_OUTLINE_DATA */ сама по себе оказывает осмысленное влияние на оптимизатор версий 11.2/12.1
Например, стандартный запрос для демонстрации «новой» (для Oracle 11g) операции HASH JOIN ANTI NA: (more…)
26. / +LEADING(TABLE) /
Используйте указанную таблицу в качестве первой таблицы в порядке подключения.
В запросе, включающем несколько таблиц, подсказка указывает, какая таблица используется в качестве таблицы управления, и сообщает оптимизатору, какой таблице следует сначала получить доступ к данным.
31.05.2014
15. /*+USE_CONCAT*/
Преобразуйте условие OR после WHERE в запросе в объединенный запрос UNION ALL.
Например:
05.06.2016
21. /*+ORDERED*/
В соответствии с порядком, в котором таблицы отображаются в FROM, ORDERED заставляет ORACLE соединять их в этом порядке.
Подсказка указывает Oracle выбирать таблицу дисков в порядке, следующем за таблицей, следующей за From. Oracle рекомендует использовать Leading при выборе таблицы дисков, что является более гибким.
14. /*+ADD_EQUAL TABLE INDEX_NAM1,INDEX_NAM2. */
Предложите четко выбрать план выполнения и объединить несколько сканирований индекса по одной колонке.
Например:
Особенности, наблюдаемые при применении подсказки STATEMENT_QUEUING
Проблема с непараллельным выполнением казалось бы гарантированно захинтованного запроса, SQL Monitor которого показывал:
2. /*+FIRST_ROWS*/
Он показывает, что для блока операторов выбран метод оптимизации на основе затрат, и наилучшее время отклика достигается для минимизации потребления ресурсов.
Если для режима оптимизации CBO задано значение FIRST_ROWS (n), при выполнении SQL Oracle будет отдавать приоритет первым n записям в наборе результатов и отсылать самые быстрые, тогда как другие результаты не нужно возвращать одновременно.
В примере подкачки каждый раз, когда из набора результатов берутся 10 записей, записи сортируются в соответствии с полем x.
Примечание: поле x, используемое для сортировки, должно быть проиндексировано, иначе CBO будет игнорировать FIRST_ROWS (n) и использовать ALL_ROWS.
20. /*+NO_MERGE(TABLE)*/
Представления, которые можно объединить, больше не объединяются.
Например:
30.01.2016
Таймауты выполнения PL/SQL процедуры, Гауссово распределение и статистика будущих периодов
По жалобе на превышение 20 секундного таймаута при выполнении кастомизированной процедуры XXWHY_NOT_INTF.REQUEST, оценка статистики выполнения соответствующего запроса оказалась не очень информативной:
— хотя понятно, что в среднем большая часть времени выполнения процедуры тратилась на ЦПУ (и/или чтение/обработку блоков буферного кэша)
В поисках потенциального источника таймаутов может пригодиться древо ожиданий вызова как самой процедуры (sql_id=arhv5vxgj80uf), так и генерируемых ею рекурсивных запросов: (more…)
23. /*+USE_MERGE(TABLE)*/
Соедините указанную таблицу с другими источниками строк через соединение сортировки слиянием.
В запросе ассоциации с несколькими таблицами укажите использование объединения слиянием для ассоциации с несколькими таблицами.
Например:
Hints
Hints are comments in a SQL statement that pass instructions to the Oracle Database optimizer. The optimizer uses these hints to choose an execution plan for the statement, unless some condition exists that prevents the optimizer from doing so.
Hints were introduced in Oracle7, when users had little recourse if the optimizer generated suboptimal plans. Now Oracle provides a number of tools, including the SQL Tuning Advisor, SQL plan management, and SQL Performance Analyzer, to help you address performance problems that are not solved by the optimizer. Oracle strongly recommends that you use those tools rather than hints. The tools are far superior to hints, because when used on an ongoing basis, they provide fresh solutions as your data and database environment change.
Hints should be used sparingly, and only after you have collected statistics on the relevant tables and evaluated the optimizer plan without hints using the EXPLAIN PLAN statement. Changing database conditions as well as query performance enhancements in subsequent releases can have significant impact on how hints in your code affect performance.
The remainder of this section provides information on some commonly used hints. If you decide to use hints rather than the more advanced tuning tools, be aware that any short-term benefit resulting from the use of hints may not continue to result in improved performance over the long term.
A statement block can have only one comment containing hints, and that comment must follow the SELECT , UPDATE , INSERT , MERGE , or DELETE keyword.
The following syntax diagram shows hints contained in both styles of comments that Oracle supports within a statement block. The hint syntax must follow immediately after an INSERT , UPDATE , DELETE , SELECT , or MERGE keyword that begins the statement block.
The plus sign (+) causes Oracle to interpret the comment as a list of hints. The plus sign must follow immediately after the comment delimiter. No space is permitted.
hint is one of the hints discussed in this section. The space between the plus sign and the hint is optional. If the comment contains multiple hints, then separate the hints by at least one space.
string is other commenting text that can be interspersed with the hints.
The --+ syntax requires that the entire comment be on a single line.
Oracle Database ignores hints and does not return an error under the following circumstances:
The hint contains misspellings or syntax errors. However, the database does consider other correctly specified hints in the same comment.
The comment containing the hint does not follow a DELETE , INSERT , MERGE , SELECT , or UPDATE keyword.
A combination of hints conflict with each other. However, the database does consider other hints in the same comment.
The database environment uses PL/SQL version 1, such as Forms version 3 triggers, Oracle Forms 4.5, and Oracle Reports 2.5.
A global hint refers to multiple query blocks. Refer to Specifying Multiple Query Blocks in a Global Hint for more information.
With 19c you can use DBMS_XPLAN to find out whether a hint is used or not used. For more information, see the Database SQL Tuning Guide .
Specifying a Query Block in a Hint
You can specify an optional query block name in many hints to specify the query block to which the hint applies. This syntax lets you specify in the outer query a hint that applies to an inline view.
The syntax of the query block argument is of the form @ queryblock , where queryblock is an identifier that specifies a query block in the query. The queryblock identifier can either be system-generated or user-specified. When you specify a hint in the query block itself to which the hint applies, you omit the @queryblock syntax.
The system-generated identifier can be obtained by using EXPLAIN PLAN for the query. Pretransformation query block names can be determined by running EXPLAIN PLAN for the query using the NO_QUERY_TRANSFORMATION hint. See NO_QUERY_TRANSFORMATION Hint.
The user-specified name can be set with the QB_NAME hint. See QB_NAME Hint.
Specifying Global Hints
Many hints can apply both to specific tables or indexes and more globally to tables within a view or to columns that are part of indexes. The syntactic elements tablespec and indexspec define these global hints .
You must specify the table to be accessed exactly as it appears in the statement. If the statement uses an alias for the table, then use the alias rather than the table name in the hint. However, do not include the schema name with the table name within the hint, even if the schema name appears in the statement.
Specifying a global hint using the tablespec clause does not work for queries that use ANSI joins, because the optimizer generates additional views during parsing. Instead, specify @ queryblock to indicate the query block to which the hint applies.
When tablespec is followed by indexspec in the specification of a hint, a comma separating the table name and index name is permitted but not required. Commas are also permitted, but not required, to separate multiple occurrences of indexspec .
Specifying Multiple Query Blocks in a Global Hint
Oracle Database ignores global hints that refer to multiple query blocks. To avoid this issue, Oracle recommends that you specify the object alias in the hint instead of using tablespec and indexspec .
For example, consider the following view v and table t :
The following examples use the EXPLAIN PLAN statement, which enables you to display the execution plan and determine if a hint is honored or ignored. Refer to EXPLAIN PLAN for more information.
The LEADING hint is ignored in the following query because it refers to multiple query blocks, that is, the main query block containing table t and the view query block v :
The following SELECT statement returns the execution plan, which shows that the LEADING hint was ignored:
The LEADING hint is honored in the following query because it refers to object aliases, which can be found in the execution plan that was returned by the previous query:
The following SELECT statement returns the execution plan, which shows that the LEADING hint was honored:
Hints by Functional Category
Table 2-23 lists the hints by functional category and contains cross-references to the syntax and semantics for each hint. An alphabetical reference of the hints follows the table.
Официально не поддерживается, начиная с Oracle 10, используется оптимизация по точно оределённым правилам (Rule Based Optimization) без учёта статистики объектов бд. Описание применяемых правил для инилизационного параметра optimizer_mode = rule
В частности, несмотря на использование подсказки RULE Oracle будет использовать Cost-Based Optimization, если:
- кроме RULE в запросе используются другие подсказки
- запрос использует партицированные или таблицы, организованные в виде индекса (IOT), или материализованные представления (mview)
- в запросе используются кляузы SAMPLE, SPREADSHEET, конструкции GROUPING SETS
- в запросе используются ANSI left|full outer join
- запрос выполняется параллельно
- используется Flashback cursor (as of [scn|timestamp])
- …
/*+ ALL_ROWS */
«Подсказка ALL_ROWS определяет целью скорейшее выполнение всего запроса с минимальным расходом ресурсов (best throughput при извлечении всего результирующего набора данных). При одновременном с ALL_ROWS или FIRST_ROWS указании подсказок, определяющих методы доступа к данным (NO_INDEX_SS, INDEX_COMBINE. ) или указывающие методы объединения объектов БД (LEADING, USE_NL_WITH_INDEX. ), оптимизатор отдаёт предпочтение подсказкам методов доступа и объединения»
/*+ FIRST_ROWS */
Из документации Oracle 8: «Подсказка FIRST_ROWS определяет стоимостной подход (cost-based approach) для оптимизации блоков запроса (statement block) с целью лучшего времени отклика (response time, минимального расхода ресурсов для возвращения первых строк запроса). В соответствии с этой подсказкой оптимизатор делает следующие предпочтения [в выборе операций доступа к данным и методов соединения]:
- При наличии оптимизатор использует сканирование по индексу (index scan) вместо полного сканирования таблицы (full table scan)
- Если доступно сканирование по индексу (index scan), оптимизатор выбирает nested loops join вместо sort-merge join в случае, когда сканируемая индексированная таблица может быть использована как ведомая таблица (inner table) для операции nested loops
- Если использование индекса (index scan) может быть использовано для получения отсортированных данных (в порядке, определённом фразой ORDER BY), оптимизатор выбирает индексный доступ во избежание дополнительной сортировки»
Начиная с Oracle 9i: «Подсказка FIRST_ROWS указанная без аргументов, предназначенная для оптимизации плана выполнения с целью скорейшего возвращения первой строки запроса, сохраняется только для обратной совместимости (backward compatibility) и стабильности планов выполнения (plan stability)»
Значение инилизационного параметра OPTIMIZER_MODE=FIRST_ROWS (что равносильно применению подсказки FIRST_ROWS для всех запросов) аннонсируется в документации вплоть до версии Oracle 11.2
/*+ FIRST_ROWS(n) */
Оптимизация, основанная на стоимости (Cost Based Optimization) + использование правил (предпочтений в выборе плана) с целью получения лучшего времени отклика для получения первых n строк. План рассчитывается с учётом значения n, как целевого количества выбранных запросом строк (query cardinality).
См. описание правил для параметра optimizer_mode = first_rows
Не совсем понятная фраза в документации: « Оптимизатор игнорирует эту подсказку в SQL предложениях DELETE и UPDATE и в запросах SELECT, включающих блокирующие операции, такие как сортировки и группировки . Такие SQL предложения не могут быть оптимизированы с целью наименьшего времени отклика (best response time), поскольку Oracle должен обработать все строки запроса до того, как вернуть первую строку результата. При указании этой подсказки запросы указанного типа оптимизируются с целью лучшего времени получения всех строк запроса с минимальным расходом ресурсов (best throughput, как при использовании подсказки ALL_ROWS)»
В Oracle 11.2 ничего не изменилось — независимо от количества обновляемых строк (Rows), устанавливаемого функцией rownum ни планы выполнения (кроме дополнительной операции COUNT STOPKEY), ни стоимость, ни ожидаемое время (Time) не меняются:
— при попытке обновить только одну строку Oracle выбирает тот же план с недешёвой операцией HASH JOIN SEMI (в запроса используется конструкция EXISTS) — более подходящей для получения всех строк обновляемой таблицы (ALL_ROWS mode)
Простой тест для показывает, как по-разному режимы ALL_ROWS, FIRST_ROWS и FIRST_ROWS(n) влияют на поведение оптимизатора: собственно, планы выполнения и методы доступа к данным + Cost + Rows :) на примерах сортировки или группировки при использовании бессмысленного условия object_id > 1 — которому удовлетворяют все строки таблицы T1 — по условию создания таблицы min(object_id)=2)*:
*) статистика для таблицы T1 актуальна и оптимизатор прекрасно «знает» о минимальном и максимальном значении T1.Object_id:
или так, попроще:
Access Path Hints
Подсказки,определяющие конкретные способы доступа к данным, порядок и применяемые методы объединения промежуточных наборов данных (result sets)*
Том Кайт называет эти подсказки плохими (bad hints):
«Плохие подсказки указывают оптимизатору как следует действовать [при выполнении запроса], какой индекс использовать, в каком порядке обрабатывать таблицы, с помощью какой операции (join technique) производить соединение [источников данных]»
*) Перед применение необходимо учитывать вероятность изменения статистики системы и объектов (таблиц и индексов), используемых в запросе со временем и, как следствие, возможную неоптимальность указываемых операций для будущих наборов данных. План выполнения, оптимизированный с помощью подсказок этого типа в среде разработки, может оказаться далеко не оптимальным для боевой бд (production system) из-за отличающихся наборов данных и характеристик системы («железо»)
/*+ LEADING( [@query_block] [tablespec],[tablespec]. ) */
«Подсказка LEADING указывает оптимизатору использовать перечисленный порядок доступа к таблицам при построении плана выполнения запроса… более гибкая, чем ORDERED… Полностью игнорируются при использовании двух или более конфликтующих подсказок LEADING. Для оптимизатора подсказка ORDERED имеет преимущество против LEADING»
В версии 11.2 может игнорироваться оптимизатором при выполнении преобразований, основанных на стоимости (Cost-Based Query Transformation), например, Table Expansion
/*+ ORDERED */
«Подсказка ORDERED указывает Oracle [при выполнении запроса] проводить соединение таблиц в том же порядке, в котором таблицы перечислены в конструкции FROM. Oracle рекомендует вместо ORDERED использовать подсказку LEADING, обладающую большей гибкостью…», т.е. дающей оптимизатору больше возможностей в выборе плана выполнения
/*+ USE_HASH( [@query_block] [tablespec] [tablespec]… ) */
/*+ NO_USE_HASH( [@query_block] [tablespec] [tablespec]… ) */
…указывает оптимизатору использовать / не использовать операцию hash join для соединения каждой указанной таблицы с прочими источниками данных Операции CBO: Hash Join
Обратил внимание, что в стандартном автоматически созданном (Sql Tuning Advisor) SQL Profile на фоне многочисленных корректирующих OPT_ESTIMATE лишь иногда (нечасто) проскакивают хинты типа TABLE_STATS / INDEX_STATS / COLUMN_STATS:
, фиксирующие текущую статистику таблиц, столбцов и индексов: (more…)
19. /*+MERGE(TABLE)*/
Возможность объединить различные запросы представления соответственно.
Например:
23.03.2014
Оптимизатор режима Подсказка
28. /*+NOCACHE(TABLE)*/
Путь доступа Подсказка
11. /*+INDEX_JOIN(TABLE INDEX_NAME)*/
В приглашении четко указано, что оптимизатор использует индекс в качестве пути доступа.
Например:
Параллельное исполнение подсказки
12c: hardcoded подсказка _fix_control и новая проблема High Version Count
Как бы продолжая тему багов/особенностей 12c, появился запрос с заметной долей Shared Pool Concurrency ожиданий в процессе выполнения с ASH мониторингом вида:
— на этапах Hard/Soft Parse, доля которых для запроса составляла ~ 2/3 общего времени выполнения: (more…)
Комментарии к записи 12c: hardcoded подсказка _fix_control и новая проблема High Version Count отключены
24. /*+USE_HASH(TABLE)*/
Соедините указанную таблицу с другими источниками строк с помощью хеш-соединения.
В запросе на сопоставление с несколькими таблицами укажите использование хеш-соединения для выполнения сопоставления с несколькими таблицами.
Например:
12c: как надёжно отключить Automatic Dynamic Statistics на уровне запроса?
В предыдущей заметке 12c: Automatic Dynamic Statistics я сослался на документ поддержки Dynamic Sampling Level Is Changed Automatically in 12C (Doc ID 2002108.1), рекомендующий в качестве метода отключения ADS
использовать подсказку на следующем примере:
Disabling Dynamic Statistics
ADS can be disabled but setting optimizer_dynamic_sampling to 0 either with a parameter or using a hint.
…
Disable for all tables:
select /*+ dynamic_sampling(0) */ …
Пробуя отключить Automatic Dynamic Sampling в одном из тестовых запросов, обратил внимание, что метод с /*+ DYNAMIC_SAMPLING(0) */ нельзя признать надёжным: (more…)
Комментарии к записи 12c: как надёжно отключить Automatic Dynamic Statistics на уровне запроса? отключены
1. /*+ALL_ROWS*/
Он показывает, что для блока операторов выбран метод оптимизации на основе затрат, получена наилучшая пропускная способность и минимизировано потребление ресурсов.
Когда режим CBO установлен на ALL_ROWS, Oracle выполнит SQL с максимальной скоростью и вернет все наборы результатов. Разница между ним и FIRST_ROWS (n) заключается в том, что ALL_ROWS подчеркивает, что SQL будет выполняться с самой быстрой скоростью, и будет Все результирующие наборы возвращаются, и FIRST_ROWS (n) фокусируется на времени выполнения возврата первых n записей.
17. /*+NOWRITE*/
Операции перезаписи запросов в блоках запросов запрещены.
Как форсировать применение DYNAMIC SAMPLING на примерах LiveSQL версии 19c
- If you use the hint in the “cursor-level” form: /*+ dynamic_sampling () */ e.g. /*+ dynamic_sampling(4) */, this is equivalent to setting the parameter optimizer_dynamic_sampling to that level for the duration of that query.
- If you use the hint in the “table-level” form /*+ dynamic_sampling( ) */ e.g. /*+ dynamic_sampling(ord 4) */ you are directing Oracle to sample a specific table unconditionally. In this form, the sample size for levels 1 to 9 is 2 level-1 * “basic sample size”(which is set to 32 blocks by default by parameter _optimizer_dyn_smp_blks.) You can have multiple hints of this form in a single query, one for each table you want sampled.
Комментарии к записи Как форсировать применение DYNAMIC SAMPLING на примерах LiveSQL версии 19c отключены
Подсказка преобразования запроса
16. /*+NO_EXPAND*/
Для операторов запроса OR или IN-LIST после WHERE NO_EXPAND предотвратит его расширение на основе оптимизатора.
Например:
6. /*+ROWID(TABLE)*/
Подсказка ясно указывает, что указанная таблица доступна в соответствии с ROWID.
Например:
08.11.2015
Подсказка INDEX и bitmap-операции с B-tree индексами
Причиной увеличения DB Time/Elapsed:
— с соопутствующим скачком Load Average (*) был хорошо заметный в том же AWR-отчёте запрос:
, выполнявшийся в проблемый период попеременно по 2-м разным планам согласно статистике V$SQL/V$SQL_SHARED_CURSOR: (more…)
25.07.2015
Читайте также: