хинты sql что это
SQL-Ex blog
Новости сайта «Упражнения SQL», статьи и переводы
Хинты в SQL Server
Я с подозрением относился к использованию хинтов в SQL Server, а теперь не могу представить себе работу без них.
Мое мнение по этому поводу менялось на протяжении нескольких последних лет в связи с большим числом проблем с производительностью, над которыми приходилось работать. Я выступал на SQLSaturday 1000 (Oregon 2020) на прошлых выходных, и мой доклад был в основном о вещах, которые я узнал об оптимизации сборки мусора и аналогичных дополнительных процессах. Во время этой работы я столкнулся с рядом проблем с запросами, подобными следующему примеру для базы данных WideWorldImporters:
Вопросы заказов
Логика здесь достаточно проста. Ранее в процессе мы обнаружили заказы, которые хотели удалить в соответствии с политикой хранения, и поместили значения OrderID в табличную переменную, оптимизированную для памяти (motv). Затем мы используем motv для удаления из всех связанных таблиц, и наконец из таблицы Orders.
Этот запрос не имеет предложения WHERE. Ясно, что мы хотим сделать, чтобы это работало. У нас имеется 100 строк в нашей motv, и мы хотим удалить связанные строки в Invoices. Однако я увидел проблемы, вызываемые планами выполнения, которые нарушают порядок:
Табличные переменные не имеют статистики, поэтому оптимизатор не знает, сколько строк будет предположительно получено из этой операции (заметим, что табличные переменные иначе компилируются в SQL Server 2019, что может решить проблему). Время от времени я вижу план с порядком соединения, который не совпадает с моими ожиданиями. Здесь все портит отсутствие предложения WHERE, однако тут не существует предложения, которое я могу применить и которое выполнит фильтрацию лучше, чем это уже сделано в моей табличной переменной.
Согласованность
Я работаю с сотнями баз данных, имеющих одну и ту же схему. Они имеют только различные наборы данных и распределения, разные размеры, и их статистика обновляется в разное время. Но если одна из них выбирает плохой план, я должен отбросить всякую другую работу, чтобы исследовать причину высоких значений ЦП на базе данных xyz.
Согласованность весьма важна для меня. И в этом случае ответ прост. Да, я хочу быстро просканировать сначала небольшую оптимизированную для памяти табличную переменную, и использовать её для фильтрации большой более медленной таблицы. Добавление хинта соединения или указанного порядка должно согласовать план и производительность.
Оба варианта навязывают порядок соединения. Хинт INNER LOOP JOIN имеет дополнительное преимущество, гарантируя, что план использует соединение вложенными циклами. Соединение hash match не дало бы эффекта при размере пакета в несколько сотен или тысяч строк. Merge join потребовало бы, вероятно, сортировки одного из входов, а это не то, что нужно.
Индексные хинты
Мне пришлось использовать индексный хинт в следующем примере:
Это был пример процесса сборки мусора. План не выявляет проблемы, но следует с подозрением отнестись к сканированию здесь:
Сканирование таблицы читает только 100 строк, но это потому, что используется оператор ТОР. Первые 100 строк отвечают нашему фильтру, поэтому запрос заканчивается в этом месте. Если никакие строки не отвечают критерию (или их меньше 100), нам придется сканировать всю таблицу.
На столбце RecordedWhen имеется индекс; просто он не был использован. Это другой случай, когда применение хинта кажется очевидным. Возможно, обновление статистики также решило бы проблему, но это дает мне больше уверенности.
Большая ответственность
При использовании хинтов мы принимаем на себя некоторую ответственность, за которую не отвечает SQL Server, и мы можем получить новые проблемы. Ниже приводятся некоторые моменты, которые следует принять во внимание, прежде чем вы попытаетесь применить хинт.
Один из моих коллег недавно решил проблему с производительностью, изменив порядок соединения с помощью хинта, или, по его словам, «выполнив Jared Poche». Это говорит о том, как часто я использовал подсказки, и как часто они срабатывали.
Обратные ссылки
Нет обратных ссылок
Комментарии
Показывать комментарии Как список | Древовидной структурой
Хинты планера в PostgreSQL
Известно, что SQL — декларативный язык, который указывает, «что» мы хотим выбрать из базы, а «как» это сделать — СУБД решает сама. Задачу выбора для SQL-запроса конкретного способа его выполнения(плана) решает планировщик запросов, который есть практически в любой СУБД. Но иногда он выбирает не самый лучший план. Многие коммерческие СУБД предоставляют на этот случай «хинты», которые позволяют в ручном режиме подсказывать базе, как лучше выполнить запрос. В Open Source СУБД PostgreSQL такого механизма не было.
И вот, наконец, случилось то, о чем многие мечтали и чего уже устали ждать, а другие боялись. Японские разработчики из NTT реализовали хинты планера PostgreSQL. Причем, им удалось это сделать, не меняя ядро, в виде отдельного модуля pg_hint_plan, поддерживающего версии PostgreSQL 9.1 и 9.2. Модуль реализует хинты, позволяющие устанавливать методы сканирования и соединения таблиц, установку значений GUC. За деталями установки и использования добро пожаловать под кат.
С сайта можно скачать архивы исходников отдельно под версии 9.1 и 9.2, которые, правда, не отличаются абсолютно ничем и одинаково собираются под обе версии. Ну да ладно. Сборка и установка модуля проблем не вызывает: make && make install. Для сборки потребуется dev-пакет PostgreSQL от вашего любимого дистрибутива. Для того, чтобы PostgreSQL подхватил модуль, никакого SQL выполнять не нужно, достаточно добавить pg_hint_plan в переменную shared_preload_libraries в файле postgresql.conf (вместо этого можно подгружать модуль в каждую сессию, где это необходимо, с помощью команды LOAD). После перезапуска сервера станут доступны три новые GUC переменные: pg_hint_plan.enable_hint, pg_hint_plan.debug_print, pg_hint_plan.parse_messages. Первая из них отвечает за доступность хинтов (по умолчанию включены), оставшиеся две — за логирование.
Хинты указываются в комментариях к запросу, оформленных с помощью /* и */. Чтобы комментарий интерпретировался как хинт, у него в начале должен стоять знак +, например /*+ SeqScan(t1) */. Хинты бывают следующих видов.
Хинты, отвечающие за метод сканирования таблицы
Хинты, отвечающие за метод соединение таблиц
Предположим у нас есть запрос, фильтрующий данные по значениям двух полей.
Планер решает объединить результаты сканирования индексов по каждому из полей с помощью Bitmap Scan.
Однако мы можем заставить его использовать обычный Index Scan.
И даже заставить его использовать другой индекс.
Пример посложнее. Соединение двух таблиц с фильтрацией по полю одной таблицы, сортировкой по полю другой и LIMIT.
Планер выбирает план c Index Scan по test1_value1_idx и Nested Loop.
Предположим, мы хотим использовать другой тип соединения таблиц: HashJoin.
Планер подчиниться, добавив внутрь Bitmap Index Scan по test2, а снаружи — сортировку с Limit.
Если, к примеру, задать тип соединения MergeJoin и IndexScan по индексу test2_value_idx, то планер, опять таки добавит необходимые сортировки и Limit.
Что такое хинты в T-SQL (Microsoft SQL Server)
Приветствую Вас на сайте Info-Comp.ru! В данном материале мы с Вами поговорим о том, что же такое хинты в языке T-SQL (Microsoft SQL Server), Вы узнаете, какие типы хинтов бывают, для чего и в каких случаях мы их можем использовать.
В предыдущих материалах мы с Вами рассмотрели архитектуру выполнения запроса в Microsoft SQL Server, план выполнения запроса, поговорили об операторах плана запроса, сегодня же мы поговорим о том, как мы можем влиять на процесс формирования плана выполнения запроса, а влиять на него мы можем как раз с помощью хинтов.
Хинты в T-SQL
Хинт (hint) – это указание оптимизатору запросов, которое переопределяет его поведение по умолчанию на время выполнения SQL инструкции.
Иными словами, с помощью хинта мы можем сказать оптимизатору запросов, как именно поступить в той или иной ситуации в процессе построения плана запроса.
Например, мы можем сказать, какой конкретно индекс использовать, какой конкретно алгоритм физического соединения таблиц применить, или, допустим, на время выполнения запроса разрешить «грязное чтение».
Обычно оптимизатор запросов SQL Server выбирает самый оптимальный план выполнения запроса и крайне редко ошибается, поэтому использовать подсказки, т.е. хинты, без острой на то необходимости не рекомендуется.
Однако оптимизатор запросов все же может ошибиться или построить план, который нас будет не устраивать, и в этом случае мы можем подсказать оптимизатору с помощью хинтов, как лучше было бы поступить в той или иной ситуации.
Обязательно стоит отметить, при использовании хинтов Вы должны четко понимать, как изменится план выполнения запроса и как запрос с хинтом будет отрабатывать в случае изменения каких-либо факторов, которые влияют на процесс формирования плана. Например, что будет, если у нас объем данных в таблицах, участвующих в запросе, увеличится в несколько раз, т.е. в данном случае Вы должны понимать, не просядет ли производительность с нашим хинтом в случае увеличения объема данных, иными словами, наш хинт так же эффективно будет работать, как и с текущим объемом данных.
Примечание! Факторов, влияющих на построение плана выполнения запроса, на самом деле много, поэтому использование хинтов рекомендуется только опытным разработчикам и администраторам баз данных, да и то в самом крайнем случае, когда другого решения нет.
Типы хинтов в T-SQL
В Microsoft SQL Server хинты мы можем использовать в разных секциях запроса, и на основе этого можно выделить следующие типы хинтов.
Join Hints
С помощью именно этого типа хинтов мы можем влиять на выбор алгоритма физического соединения таблиц.
Существуют следующие хинты:
Пример.
В этом примере с помощью хинта Hash мы принудительно задали тип физического соединения таблиц Hash Match.
Более подробно о типах физического соединения таблиц мы говорили в отдельном материале.
Query Hints
Хинты на уровне запросов распространяются на весь запрос. Иными словами, они влияют на все операторы в SQL инструкции.
Определяются такие хинты с помощью ключевого слова OPTION. В SQL запросе может быть определено только одно предложение OPTION, при этом в нем мы можем указать несколько хинтов.
Существует много различных хинтов, которые мы можем использовать на уровне запросов, например, у OPTION существуют следующие параметры (представлена лишь небольшая часть):
Пример.
В этом запросе мы использовали хинт RECOMPILE.
Table Hints
Табличные хинты относятся только к таблицам и представлениям, для которых они указаны. Такие хинты предназначены для определения способа блокировки, указания конкретного индекса, способа обработки данных в таблицах, например, сканирование или поиск.
Заметка! Всем тем, кто только начинает свое знакомство с языком SQL, рекомендую прочитать книгу «SQL код» – это самоучитель по языку SQL для начинающих программистов. В ней очень подробно рассмотрены основные конструкции языка.
Вот несколько популярных табличных хинтов:
Табличные хинты задаются в предложении FROM с помощью ключевого слова WITH, которое мы указываем после названия таблицы или представления.
Пример.
В данном примере для таблицы Production.Product мы указали хинт INDEX, с помощью которого задали конкретный индекс, а для таблицы Production.ProductReview мы указали хинт NOLOCK, который позволяет обрабатывать данную таблицу в режиме READ UNCOMMITTED, т.е. мы можем читать неподтверждённые данные (грязное чтение).
Примечание! Популярные хинты, которыми часто пользуются разработчики и администраторы, мы подробно рассмотрим в отдельной статье, поэтому следите за выходом новых статей в моих группах в социальных сетях: ВКонтакте, Facebook, Одноклассники и Twitter. Подписывайтесь, и Вы не пропустите выход нового материала!
На сегодня это все, надеюсь, материал был Вам полезен, пока!
Oracle mechanics
Подсказки (Oracle Hints)
SQL Hints в документации Oracle
Ссылки
Замечания
Подсказка, указанная после некорректной (например, синтаксически) подсказки или текста в том же комментарии также может быть проигнорирована
Подсказки
— общие цели оптимизатора
— способы выполнения [под]запроса
Optimization Goals and Approaches hints
Подсказки,определяющие общие цели и подходы для оптимизации плана выполнения запроса, включая правила и методы доступа к данным. Соответсвуют (почти) описаниям значений параметра OPTIMIZER_MODE
Официально не поддерживается, начиная с Oracle 10, используется оптимизация по точно оределённым правилам (Rule Based Optimization) без учёта статистики объектов бд. Описание применяемых правил для инилизационного параметра optimizer_mode = rule
В частности, несмотря на использование подсказки RULE Oracle будет использовать Cost-Based Optimization, если:
В 11.2 при использовании RBO может формироваться трейс оптимизатора и использоваться некоторые преобразования запросов за исключением Cost-Based Query Transformation (что логично:) — см. замечания в Индекс в статусе unusable, подсказка INDEX и обновлённый RBO в Oracle 11.2
/*+ 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, минимального расхода ресурсов для возвращения первых строк запроса). В соответствии с этой подсказкой оптимизатор делает следующие предпочтения [в выборе операций доступа к данным и методов соединения]:
Начиная с 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
В 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) производить соединение [источников данных]»
/*+ 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
/*+ USE_NL ( [@query_block] [inner_table] ) */
/*+ NO_USE_NL ( [@query_block] [inner_table] ) */
/*+ USE_NL_WITH_INDEX ( [@query_block] inner_table [indexspec]) */
/*+ USE_MERGE( [@query_block] [tablespec] … ) */
/*+ NO_USE_MERGE( [@query_block] [tablespec] … ) */
/*+ USE_HASH_AGGREGATION([@query_block]) */
/*+ NO_USE_HASH_AGGREGATION([@query_block]) */
указывают оптимизатору использовать или не использовать вместо классической операции Sort group by относительно «новую» операцию группировки Hash Group By
/*+ NATIVE_FULL_OUTER_JOIN*/
/*+ NO_NATIVE_FULL_OUTER_JOIN*/
управление использованием механизма Native Full Outer Join
/*+ INDEX_JOIN ( [@query_block] tablespec [indexspec],… ) */
использовать для получения результатов запроса временный индекс, получающийся в рез-те объединения существующих индексов методом Index [Hash] Join
/*+ INDEX_COMBINE ( [@query_block] tablespec [indexspec],… ) */
использовать для получения результатов запроса Bitmap операции с ROWID, полученными при индексном доступе.
/*+ NUM_INDEX_KEYS( table index numkeys ) */
добавлен в версии 10.2 для управления кол-вом индексных ключей, используемых в INLIST ITERATOR с индексом по нескольким полям Bug 5152325 Enh: Add NUM_INDEX_KEYS hint
Функционал подсказки исправлен для 11.2.0.3 и зафиксирован в 12.2 — Unresolved quiz: Avoiding in-list iterator
/*+ DRIVING_SITE ( [@query_block] [tablespec] ) */
«… подсказка указывает оптимизатору выполнять запрос на сайте [сайте таблицы, указанной в хинте], отличном от выбранного бд [Oracle]. Хинт полезен для оптимизации выполнения распределённых запросов»
В зависимости от подсказки запрос выполняется полностью на удалённом сайте — при указании удалённой таблицы в хинте DRIVING_SITE(e):
, либо локально, с копированием данных из удалённой таблицы на локальный инстанс — при использовании подсказки DRIVING_SITE(dual), указывающей на локальную таблицу dual. В плане указывается операция REMOTE_TO_SERIAL (R->S):
Версии: 9.2.0.1 — 11.2.0.2 … распределённый DML должен выполняться в бд, где расположена целевая таблица DML. Подсказка DRIVING_SITE не меняет этого поведения
DRIVING_SITE предполагает управление выполнением (mapping) всего курсора (а не отдельных подзапросов)… [однако можно перенести выполнение части курсора / подзапроса в удалённый обзор]
Подсказка DRIVING_SITE предназначена для оптимизации запросов (SELECT) и не предназначена для DML или DDL
/*+ MATERIALIZE */
www.club-oracle.com: « Подсказка Materialize представляет собой технику оптимизации запросов и может быть особенно полезна для больших наборов данных. Материализация подзапроса означает создание определённого типа динамической временной таблицы (dynamic temporary table) для использования во время выполнения запроса»
Соответствующий параметр, доступный с версии 11.1:
/*+ INLINE */
Противоположна по смыслу предыдущей подсказке /*+ MATERIALIZE*/:
Позволяет в случаях, когда Oracle обязан материализовать подзапрос конструкции WITH без подсказок (согласно правилам выполнения subquery factoring, например, когда подзапрос используется в основном запросе > 1 раза), не делать этого, рассматривая подзапрос в качестве inline view.
Полезен в случаях, когда материализуемый подзапрос используется редко в соединениях с малым количеством строк и накладные расходы на создание temporary table, подразумевающее ALL ROWS mode, оказываются больше стоимости нескольких выполнений подзапроса в режиме FIRST ROWS, например, с использованием индексов
На примере тестовой схемы можно видеть как применение подсказки уменьшает полную стоимости всего запроса, используя при выполнении одного из подзапросов WITH созданный индекс:
/*+ PRECOMPUTE_SUBQUERY */
Позволяет выделить выполнение подзапроса в отдельный курсор
Подсказка недокументирована и может быть использована в боевых условиях только по согласованию с техподдежкой во избежание ORA-00600: [kglUnKeepHandle] при превышении лимита количества дочерних курсоров
Подсказки, динамически влияющие на статистику оптимизатора
/*+ DYNAMIC_SAMPLING ( [@query_block] [tablespec] degree_of_sampling ) */
При использовании подсказки DYNAMIC_SAMPLING:
Если в бд для таблицы имеется [актуальная] статистика по количеству строк,…, оптимизатор использует эту статистику. Иначе запросы dynamic sampling будет выполнены для оценки этой статистики. Если в подсказке указана таблица и имеется [актуальная] статистика по количеству строк:
Значения параметра подсказки degree_of_sampling :
Подсказка подходит для оптимизации отчётных запросов с длительным временем выполнения, в случаях, когда время выполнения запроса (например, десятки секунд) много больше времени подготовки запроса (SQL hard parse, включающего в себя dynamic sampling и выбор плана выполнения запроса с учётом результатов), составляющее миллисекунды при значении уровня degree_of_sampling = 10.0.0), т.е. dynamic sampling применяется, но только для таблиц без собранной статистики, этого должно быть достаточно для быстрых запросов в OLTP системах, когда увеличение времени подготовки запроса (hard parse) может заметно увеличить общее время выполнения. Рекомендуется применять с точным указанием таблицы или синонима — такой синтаксис указывает оптимизатору использовать dynamic sampling для указанной таблицы в безусловном порядке:
поскольку применение в упрощённом виде (что эквивалентно установке соотв.значения параметра OPTIMIZER_DYNAMIC_SAMPLING для запроса)
— которое, должно вызывать применение механизма для всех таблиц запроса, может приводить к неожиданному результату
Например, в Oracle 10.2.0.4 — CBO может вообще не применять динамического анализа, либо может применять, но не использовать результатов
Применение механизма DYNAMIC SAMPLING можно проконтролировать по записям в трейс файлах 10053 event:
механизм запускается (TRUE) только при использовании /*+ DYNAMIC_SAMPLING(e 3)*/
и не запускается (FALSE) в случае использования подсказки в виде /*+ DYNAMIC_SAMPLING(3)*/либо /*+ DYNAMIC_SAMPLING(@query_block 3)*/
Запросы с комментарием /* OPT_DYN_SAMP */ в файле SQL trace:
/*+ DYNAMIC_SAMPLING_EST_CDN( [@query_block] [tablespec] ) */
To force cardinality estimation even for an analyzed table, you can use a further hint, dynamic_sampling_est_cdn
Подсказка оптимизатору — заново оценить количество строк в таблице (cardinality), независимо от состояния статистики таблицы
Может успешно применяться для уточнения избирательности запросов с условиями по множеству столбцов (complex predicates) при наличии правильных индексов, в случаях когда обычный механизм Dynamic Sampling (стимулируемый параметром optimizer_dynamic_sampling) неэффективен — Randolf Geist.Dynamic Sampling (III) – Real-Life Data – Part II
/*+ CARDINALITY( objectname [,] integer ) */
Подсказка применяется для явного указания оптимизатору количества строк (cardinality), возвращаемых объектом бд или из набора данных для расчётов плана выполнения.
Объектом может быть обычная (heap) таблица, глобальная временная таблица (global temporary table), табличные функции (pipelined function), подзапрос и т.д.
Вместе с другими методами (использование механизма DYNAMIC SAMPLING для сбора статистики «на лету», «ручного» ввода статистических данных с помощью процедур пакета DBMS_STATS и др.) рекомендуется для оптимизации запросов с участием объектов, для которых отсутствует (или не может быть собрана, или для целей оптимизации требуется изменённая) статистика по количеству строк — Metalink Note 356540.1: How to workaround issues with objects that have no statistics.
Пример с сайта lazydba.com:
Механизм DYNAMIC SAMPLING с табличной функцией не работает до версии 11.1.0.7:
Рекомендуется в качестве workaround при некоторых багах оптимизатора, например Bug 11826450 : WRONG CARDINALITY WITH CONNECT BY PRIOR IN 10.2.0.4
/*+ OPT_ESTIMATE( [query block] operation_type identifier adjustment ) */
Предназначен для влияния на план выполнения через корректировку оценки кол-ва строк (cardinality), получаемых в результате операций доступа к данным и, как следствие, стоимости плана выполнения
Часто используется при создании SQL Profile, является развитием / замещением подсказки CARDINALITY
Управления преобразованиями, выполняемыми в процессе оптимизации запросов
/*+ NO_QUERY_TRANSFORMATION*/
«… инструктирует оптимизатор пропустить весь этап преобразований запроса (query transformations), влючая, но не ограничиваясь OR-expansion, view merging, subquery unnesting, star transformation, materialized view rewrite …»
Кроме собственно улучшения плана выполнения за счёт исключения неоптимальных операций/трансформаций, может быть полезет для сокращения времени разбора (parse time) запросов с [очень] большим количеством таблиц
/*+ PUSH_SUBQ( [@query_block] ) */
«Указывает оптимизатору выполнять непреобразованный подзапрос (nonmerged subqueries) на самом раннем возможном шаге плана выполнения запроса. В то время, как обычно, непреобразованные подзапросы (nonmerged subqueries) выполняются на последнем шаге плана выполнения. Раннее выполнение подзапроса может значительно ускорить производительность, если подзапрос относительно лёгок и может значительно уменьшить количество обрабатываемых строк»
При указании на уровне всего запроса в виде /*+ PUSH_SUBQ */ без указания блока подсказка не работает, начиная с версии Oracle 10g, хотя план и изменился по сравнению с оригинальным из-за действия подсказки /*+ NO_UNNEST*/:
Таким образом можно точно указать оптимизатору какие подзапросы должны и будут выполняться на ранних этапах плана выполнения, а какие на последних — соответственно, с помощью следующей подсказки
/*+ NO_PUSH_SUBQ( [@query_block] ) */
«Указывает оптимизатору выполнять непреобразованный подзапрос (nonmerged subqueries) на последнем шаге плана выполнения запроса [т.е. так, как это обычно делает оптимизатор — видимо, имеет смысл при каком-то необычном поведении оптимизатора]. Может положительно влиять на производительность, если подзапрос относительно тяжёлый или незначительно уменьшает количество строк»
Синтаксис аналогичен синтаксису подсказки /*+ PUSH_SUBQ */
/*+ NO_UNNEST*/
Добавляется к подзапросу для предотвращения операции объединения подзапроса с головным запросом (subquery unnesting)
Пример с сайта Jonathan Lewis-а — без хинтов оптимизатор объединяет подзапрос с основным запросом, используя условия запроса (через access predicate и filter) только на последнем шаге выполнения:
Подсказка NO_UNNEST меняет план, выделяя выполнение подзапроса (включая вычисление avg(inner.sal) — аггрегацию SORT AGGREGATE) по условию «INNER».»DEPTNO»=:B1:
/*+ UNNEST*/
Использование подсказки UNNEST, напротив, форсирует по возможности операцию объединения (unnests specified subquery block if possible) и в этом примере план выполнения возвращается к более дешёвому плану, используемому оптимизатором по умолчанию:
/*+ NO_ELIMINATE_OBY(@query_block) */
/*+ ELIMINATE_OBY(@query_block) */
Подсказка (совмеместно с QB_NAME) запрещает оптимизатору исключать избыточные с его точки зрения сортировки (ORDER BY) из подзапроса (inline view)
/*+ MERGE([@query_block] [tablespec]) */
/*+ NO_MERGE([@query_block] [tablespec]) */
Используются для явного разрешения /*+ MERGE*/ или запрещения /*+ NO_MERGE*/ использования оптимизатором механизма объединения complex view merging основного запроса и встроенных обзоров в секции FROM (inline view)
«В случае, если всроенный обзор содержит запрос с конструкцией GROUP BY или оператором DISTINCT оптимизатор может объединить такой обзор с основным запросом только если complex view merging разрешено. Тот же механизм complex merging может быть использован [оптимизатором] для объединения подзапроса из секции IN (IN subquery) с основным запросом, если подзапрос не зависит от основного запроса (subquery is uncorrelated)»
/*+ USE_CONCAT ([@query_block] [ OR_PREDICATES(1) [ PREDICATE_REORDERS(() ())] ] ) */
«… инструктирует оптимизатор преобразовывать дизъюнктивные запросы (OR-conditions in the WHERE clause) в объединение запросов с использованием оператора UNION ALL (CONCATENATION) [т.е. выполнять преобразование OR-Expansion]. В отсутствии подсказки трансформация выполняется на основе стоимостного подхода…»
В виде, указанном в документации /*+ USE_CONCAT ([@query_block]) */, подсказка может не работать:
Корректно отрабатывает с параметром OR_PREDICATES(1) на версиях >= 11.2
В данных Outline можно найти другой параметр подсказки PREDICATE_REORDERS:
, позволяющий менять порядок выполнения конкатенации:
Кроме стимулирования использования операции CONCATENATION при явном использовании OR в условиях WHERE запроса, подсказка может быть успешно использована для директивного запрещения/отключения операции INLIST ITERATOR в случае индексного доступа — Конкатенация против INLIST ITERATOR
/*+ NO_EXPAND ( [@query_block] ) */
Подсказка противоположная хинту USE_CONCAT: сигнализирует оптимизатору не использовать преобразование OR-expansion для запросов, содержащих дизъюнктивные условия OR или IN-lists в секции WHERE, несмотря на стоимость:
/*+ PUSH_PRED ( [@query_block] [tablespec] ) */
/*+ NO_PUSH_PRED ( [@query_block] [tablespec] ) */
указание использовать / не использовать CBQT Join Predicate Push-Down с обзором / таблицей, указанными в параметрах подсказки, например:
/*+ FACTORIZE_JOIN( @query_set [tablespec@query_block1 tablespec@query_block2 […]])*/
/*+ NO_FACTORIZE_JOIN( @query_set )*/
Начиная с 11.2, для управления операцией Join Factorization
/*+ OR_EXPAND([@query_block] tablespec Column1 [Column2 … ]) */
Может быть использован для инициирования OR EXPAND query transformation Query Transformation Hints
Подсказки, связанные с генерацией курсоров
/*+ CURSOR_SHARING_EXACT */
/*+ BIND_AWARE*/
Начиная с 11.1.0.7 отключает Adaptive Cursor Sharing (фазу мониторинга для оценки необходимости последующего применения ECS), форсируя применение Extended Cursor Sharing для курсоров с отличающимися наборами связанных переменных в случаях, когда ECS не срабатывает автоматически, например, при отсутствии гистограмм:
/*+ NO_BIND_AWARE*/
Отключает применение технологии Extended Cursor Sharing (и, как следствие, Adaptive Cursor Sharing за отсутствием необходимости) на уровне запроса
Прочее
/*+ QB_NAME(query_block_name) */
Добавлен с Oracle 10g Используется для точного внешнего определения названия блока запроса
Название блока запроса (queryblock identifier) может быть либо сгенерировано Oracle, либо определено пользователем с помощью подсказки QB_NAME. Сгенерированное Oracle название блока запроса можно найти в выводе команды EXPLAIN PLAN
, или c помощью запроса актуального плана выполненияк из обзора v$sql_plan (QBLOCK_NAME)
/*+ GATHER_PLAN_STATISTICS */
Используется для сбора [расширенной] статистики выполнения запроса в соответствии с планом выполнения (или просто статистики плана выполнения запроса). Действует аналогично установке значения параметра statistics_level = ALL (или _rowsource_execution_statistics = TRUE) на уровне сессии или системы. Данные по статистике плана выполнения при этом сохраняется в обзоре v$sql_plan_statistics_all
Команда set serveroutput OFF выполняется на случай, если serveroutput был включен в сессии, т.к. в этом случае получим:
т.е. последним в сессии SQL*PLUS будет PL/SQL блок DBMS_OUTPUT, который и будет пытаться обработать функция dbms_xplan.display_cursor(»,»,’…’) при запуске с NULL-левыми параметрами sql_id и child_number.
В случае, если запрос с подсказкой GATHER_PLAN_STATISTICS выполнялся не последним, статистику плана выполнения можно получить запросом по обзору v$sql (предварительно снабдив запрос оригинальным комментарием MY_QUERY_TAG для облегчения дальнейшего поиска):
/*+ OPT_PARAM(parameter_name [,] parameter_value) */
добавлен начиная с 10g R2, согласно документации 11g R2 «… позволяет установить инициализационные параметры [оптимизатора] на время выполнения запроса.
В документе поддержки OPT_PARAM Hint [ID 377333.1] в примере применения дополнительно указываются след.параметры оптимизатора, доступные для корректировки на время запроса
там же ссылаются на, к сожалению, недоступный документ Note:986618.1 Parameters useable by OPT_PARAM hint с полным перечнем параметров
Также доступно изменение след.параметров оптимизатора:
— для уменьшения времени разбора SQL (в случае длительного времени разбора запроса — long parse time, например, при большом кол-ве таблиц, используемых в запросе — от 10), доступно начиная с 10.2.0.4 по крайней мере. В трейсе 10053 при этом можно видеть:
— изменения параметра optimizer_features_enable на уровне запроса
При получении ошибок, причиной которых являются баги в реализации Cost-Based Join Predicate Push:
рекомендованный (Bug 9671977 — ORA-600 [kkocxj : pjpCtx] optimizing query with outer joins if JPPD is attempted [ID 9671977.8]) для отключения проблемной фичи параметр можно установить на уровне запроса:
Включение / отключение конкретного _fix_control
Например, попробовать отключить механизм cardinality feedback через отключение _fix_control=6699059:
на уровне запроса можно так:
/*+ OPTIMIZER_FEATURES_ENABLE(‘NN.N.N.N’) */
подсказка для уточнения/ограничения действия опций оптимизатора (optimizer features) в зависимости от версии Oracle на уровне запроса
полезна при обновлениях для получения ожидаемого плана выполнения запроса
значения версии (‘NN.N.N.N’) соответствуют значениям параметра OPTIMIZER_FEATURES_ENABLE
/*+ APPEND */
/*+ NOAPPEND*/
«Подсказка APPEND форсирует использование оптимизатором direct-path INSERT в запросах вида INSERT INTO… SELECT …
установленный признак параллельности (USER_TABLES.DEGREE) для целевой таблицы на этапе создания/модификации (CREATE|ALTER TABLE…PARALLEL n)
или подсказкой PARALLEL во время выполнения DML
или параметером PARALLEL_DEGREE_POLICY = AUTO (11.2+)]
В этом случае традиционный (conventional) метод вставки строк в таблицу будет применяться только при использовании подсказки NOAPPEND.
… При использовании direct-path INSERT, данные добавляются в конец таблицы [добавляя новые блоки и повышая High Water Mark (HWM)], вместо того, чтобы использовать свободное место в уже выделенных блоках таблицы. В результате direct-path INSERT может быть значительно быстрее традиционной (conventional) операции вставки»
Особенности использования подсказки APPEND в запросах вида INSERT INTO … VALUES, см. APPEND Hint (Direct-Path) Insert with Values Causes Excessive Space Usage on 11G [ID 842374.1]:
1. В версиях 10g, 10g R2, подсказка APPEND игнорируется
2. В 11g R1, хинт APPEND запускает режим direct-path insert даже в случае использования INSERT INTO … VALUES. Поведение аналогично использованию подсказки APPEND_VALUES в версиях, начиная с 11g R2.
3. Начиная с версии 11g R2, появляется новая подсказка APPEND_VALUES, позволяя т.о использовать механизм direct path load только в случае использования APPEND_VALUES. Подсказка APPEND игнорируется для предложений типа INSERT … VALUES…
Рекомендуется использовать хинт APPEND (режим прямой вставки) для загрузки больших объёмов данных, а не для вставки одиночных строк, т.к. в последнем случае не будет получено преимуществ
В версии 11.1 при включении режима direct-path insert в запросах типа INSERT /*+ APPEND*/ INTO … VALUES в PL/SQL конструкциях FORALL … SAVE EXCEPTIONS встречается Bug 7688258: ORA-38910 USING APPEND HINT WITH FORALL IN 11.1.0.6, где для отключения режима direct-path insert предлагается использовать параметр:
[После установки параметра] подсказка APPEND во всех запросах типа insert /*+ append */…values… будет игнорироваться в рамках сессии … аналогично поведению в версиях Oracle до 11g, где этот хинт игнорируется и в запросах insert …values always всегда используется традиционный (conventional) режим вставки независимо от наличия хинта»
В течении direct-path INSERT, бд устанавливает исключительную блокировку [TM lock mode 6 (eXclusive)] на таблицу (либо на все партиции таблицы). В результате пользователи не могут выполнять никаких конкурентных операций insert, update, или delete на таблице, также невозможны операции создания и построения индексов [при конкурентных DML сессии будут ожидать enq: TM — contention для установки блокировки TM lock в режиме (mode) 3 — row-X (SX) Row Exclusive — SubExclusive]
/*+ APPEND_VALUES */
Подсказка APPEND_VALUES форсирует использование оптимизатором режима прямой вставки direct-path INSERT [только] в запросах вида INSERT INTO… VALUES …, доступна с версии 11.2
/*+ PARALLEL */
/*+ SHARED */
Синоним подсказки PARALLEL, обычно используемый Oracle для управления параллельным выполнением подзапроса на дальней стороне db link
Работает также локально:
/*+ RESULT_CACHE*/
/*+ NO_RESULT_CACHE*/
Ручное управление кэшированием результатов запросов (или частей) на стороне сервера.
Примеры использования для inline view:
для WITH view:
Использовании кэша результатов подзапросов отключает операции оптимизации (трансформации, merging) между внешними и внутренними блоками запроса
Кэширование результатов запросов логично отменяет действие подсказки DRIVING_SITE
/*+ STATEMENT_QUEUING*/
/*+ NO_STATEMENT_QUEUING*/
Подсказки для управления очерёдностью и способом параллельного выполнения запросов (parallel statement queuing)
Использование подсказки /*+ NO_STATEMENT_QUEUING */ позволяет аналогично отключить автоматическую очерёдность выполнения запросов при PARALLEL_DEGREE_POLICY = AUTO
/*+ TABLE_STATS(Table [SCALE|NULL] [blocks|rows=Number]) */
/*+ INDEX_STATS(Table Index [SCALE|NULL] [blocks|index_rows|keys|clustering_factor=Number]) */
/*+ COLUMN_STATS(Table Column [SCALE|NULL] [length|distinct|nulls|min|max=Number]) */
— подсказки точного управления статистикой объектов на уровне запроса