Oracle удалить план запроса
Как правило оптимизатор запросов Oracle работает хорошо, но бывают ситуации когда эта с*ка начинает чудить, использовать неоптимальный план, и некогда быстрые запросы начинают тормозить. Найдя проблемный запрос, ты правишь его хинтами, но понимаешь, что развернуть его перекомпилировав пакет на боевой среде не можешь. В этой ситуации на помощь может прийти SQL Plan Management в лице пакета dbms_spm. Он позволяет зафиксировать обновленный план выполнения запроса, не меняя текста самого запроса хинтами.
Для того чтобы следовать инструкциям в статье и выполнять их не из под пользователя SYS, выдайте необходимые привилегии вашему пользователю:
grant select on V_$SESSION to test;
grant select on V_$SQL_PLAN to test;
grant select on V_$SQL_PLAN_STATISTICS_ALL to test;
grant select on V_$SQL to test;
Итак, у нас есть тестовая таблица с набором данных и двумя индексами:
Выполним запрос к этой таблице:
Данные на месте. Проверим какой индекс используется для выборки данных:
Plan hash value: 2822388801
Note
-----
— dynamic sampling used for this statement (level=2)
Вполне ожидаемо используется индекс idx_t_plan_test_1, но допустим что оптимизатор не должен был его использовать и мы хотим сделать так, чтобы в этом запросе использовался индекс idx_t_plan_test_2.
Для начала узнаем sql_id запроса, который мы хотим поменять. Для этого возьмем значение Plan hash value из вывода предыдущего запроса и подставим его в следующий запрос:
select t.* from t_plan_test t where t.n1 = 100 2w8k1yhuk8k1v 2822388801
explain plan for select t.* from t_plan_test t where t.n1 = 100 2rcswv78fcp13 2822388801
Обратите внимание на то, что нас интересует именно sql_id оригинального запроса, а не запроса explain plan для него.
Запомнив sql_id и plan_hash_value из запроса приступим к его оптимизации. Для начала добавим хинт на использование индекса idx_t_plan_test_2 и выполним его:
После этого проверим что запрос использовал именно этот индекс:
Plan hash value: 3105628069
1 — access(«T».«N1»=100)
filter(«T».«N1»=100)
Note
-----
— dynamic sampling used for this statement (level=2)
Всё верно, используется индекс idx_t_plan_test_2.
В очередной раз взяв значение Plan hash value определим sql_id нового запроса.
explain plan for select /*+INDEX(t idx_t_plan_test_2)*/t.* from t_plan_test t where t.n1 = 100 4jnycbw4j9cav 3105628069
select /*+INDEX(t idx_t_plan_test_2)*/ t.* from t_plan_test t where t.n1 = 100 43x83pbfc3x5n3105628069
Отлично. Теперь у нас есть все необходимые параметры для изменения и фиксации плана запроса.
Следующие инструкции выполняются из под пользователя SYS. Выдайте необходимые привилегии вашему пользователю, если хотите выполнять их из под него.
Во-первых, необходимо запомнить план первого запроса, взяв его sql_id и plan_hash_value:
Во-вторых, необходимо определить sql_handle нашего запроса и отключить этот план:
SQL_cbead429bd574d05 «select t.* from t_plan_test t where t.n1 = 100» SQL_PLAN_cruqn56ypfm8591102725
В-третьих, необходимо зафиксировать план второго запроса, указав в качестве sql_handle значение предыдущего плана и взяв sql_id и plan_hash_value второго запроса:
Готово. Теперь, если посмотрите план выполнения первого запроса, увидите что используется индекс idx_t_plan_test_2 без хинта.
Plan hash value: 3105628069
1 — access(«T».«N1»=100)
filter(«T».«N1»=100)
Note
-----
— SQL plan baseline «SQL_PLAN_cruqn56ypfm8500b9313e» used for this statement
Oracle Query Optimizer generally works well, but sometimes it makes your fast and polished queries slow down. Often, you can not simply add hints and recompile them on production environment. In such case be ready to use dbms_spm package.
Don’t forget about appropriate grants.
grant select on V_$SESSION to test;
grant select on V_$SQL_PLAN to test;
grant select on V_$SQL_PLAN_STATISTICS_ALL to test;
grant select on V_$SQL to test;
OK, we have a table with a set of data and two indexes.
Let’s execute a query to check a data.
Data is here. Let’s check which index is used.
Optimizer used idx_t_plan_test_1 as expected. But let’s imaging, optimizer is wrong and it should use idx_t_plan_test_2 instead.
First, we need to know our query’s sql_id. Take “Plan hash value” from the previous query and get sql_id using the following query.
Please do not confuse with explain plan query.
Let’s optimize our query now and add a hint so it will use idx_t_plan_test_2 index instead.
Check idx_t_plan_test_2 index is used.
OK, now take “Plan hash value” of a second query and get it sql_id.
Fine. Now we have all necessary parameters to fix query plan .
Following queries executing using SYS user. Grant all appropriate privileges to your user, if you need to.
First, we need to load first query plan using correspond sql_id and plan_hash_value.
Second, we need to get it sql_handle and disable it.
Third, we need to fix second query plan using it sql_id и plan_hash_value and sql_handle from a previous query.
All done. Let’s check first query plan.
1 thought on “Oracle DB. Changing and fixing query execution plan using dbms_spm.”
просто и понятно, спасибо
Leave a Comment Cancel Reply
Featured
Right off the bat, this is what you'll get in the end: Synhronous standby server using repmgrRepmgr daemons running not… Read more…
ORADeveloper is the first IDE for Oracle Databases on Android.Directly connect to an existing database without any middleware, browse objects,… Read more…
Databases are bad for email. Howbeit, sometimes you really don't need fast mailing system, but want to use an existing… Read more…
I don't know why, but installing Android x86 6.0 and 7.0 on VMWare ESXi is not quite simple as it… Read more…
Oracle has an interesting package to provide an event-based logic named dbms_alert. In this article I will use it to… Read more…
Как правило оптимизатор запросов Oracle работает хорошо, но бывают ситуации когда он начинает чудить, использовать неоптимальный план, и некогда быстрые запросы начинают тормозить. Найдя проблемный запрос, ты правишь его хинтами, но понимаешь, что развернуть его перекомпилировав пакет на боевой среде ты не можешь. В этой ситуации на помощь может прийти SQL Plan Management в лице пакета dbms_spm. Он позволяет зафиксировать обновленный план выполнения запроса, не меняя текста самого запроса хинтами.
Для того чтобы следовать инструкциям в статье и выполнять их не из под пользователя SYS, выдайте необходимые привилегии вашему пользователю:
grant select on V_$SESSION to test;
grant select on V_$SQL_PLAN to test;
grant select on V_$SQL_PLAN_STATISTICS_ALL to test;
grant select on V_$SQL to test;
Итак, у нас есть тестовая таблица с набором данных и двумя индексами:
Выполним запрос к этой таблице:
Данные на месте. Проверим какой индекс используется для выборки данных:
Вполне ожидаемо используется индекс idx_t_plan_test_1, но допустим что оптимизатор не должен был его использовать и мы хотим сделать так, чтобы в этом запросе использовался индекс idx_t_plan_test_2.
Для начала узнаем sql_id запроса, который мы хотим поменять. Для этого возьмем значение Plan hash value из вывода предыдущего запроса и подставим его в следующий запрос:
Обратите внимание на то, что нас интересует именно sql_id оригинального запроса, а не запроса explain plan для него.
Запомнив sql_id и plan_hash_value из запроса приступим к его оптимизации. Для начала добавим хинт на использование индекса idx_t_plan_test_2 и выполним его:
После этого проверим что запрос использовал именно этот индекс:
Всё верно, используется индекс idx_t_plan_test_2.
В очередной раз взяв значение Plan hash value определим sql_id нового запроса.
Отлично. Теперь у нас есть все необходимые параметры для изменения и фиксации плана запроса.
Следующие инструкции выполняются из под пользователя SYS. Выдайте необходимые привилегии вашему пользователю, если хотите выполнять их из под него.
Во-первых, необходимо запомнить план первого запроса, взяв его sql_id и plan_hash_value:
Во-вторых, необходимо определить sql_handle нашего запроса и отключить этот план:
В-третьих, необходимо зафиксировать план второго запроса, указав в качестве sql_handle значение предыдущего плана и взяв sql_id и plan_hash_value второго запроса:
Готово. Теперь, если посмотрите план выполнения первого запроса, увидите что используется индекс idx_t_plan_test_2 без хинта.
На первый взгляд это может показаться странным — зачем кто-то будет писать такой бессмысленный запрос? Но такое может происходить, если мы используем генерированный запрос или обращаемся к представлениям (view).
Трансформация inner join
Давайте рассмотрим небольшой пример (скрипты выполнялись на Oracle 11.2).
Теперь попробуем выполнить простой запрос и посмотрим на его план:
Несмотря на то, что мы запрашиваем колонку только из таблицы child, Oracle, тем не менее, выполняет честный inner join и впустую делает обращение к таблице parent.
Получается, оптимизатор не понимает, что в этом запросе соединение этих двух таблиц не приводит к какой-либо фильтрации или размножению строк. Значит, нужно помочь ему это понять.
Свяжем эти таблицы с помощью foreign key из child на parent и посмотрим на то, как изменится план запроса:
Как видно из плана запроса — этого оказалось достаточно.
Чтобы Oracle смог удалить лишние таблицы из запроса, соединенные через inner join, нужно чтобы между ними существовала связь foreign key — primary key (или unique constraint).
Трансформация outer join
Для того, чтобы Oracle мог убрать лишние таблицы из запроса в случае outer join — достаточно на колонке внешней таблицы, участвующей в соединении, был первичный ключ (primary key) или ограничение уникальности (unique constraint).
И попробуем выполнить следующий запрос:
Как видно из плана запроса, в этом случае Oracle так же догадался, что таблица parent_3 лишняя и ее можно удалить.
Число таблиц, которое может быть удалено из запроса, не ограничено. Join elimination удобно использовать, если существует дочерняя таблица, несколько родительских таблиц и результат их соединения выставлен в виде представления.
Создадим такое представление, которое объединит все наши таблицы и попробуем использовать его в запросе:
Как видно из плана, Oracle отлично справился и с таким запросом тоже.
Трансформация semi join и anti join
Для того, чтобы была возможность таких трансформаций: между таблицами должна быть связь foreign key — primary key, как и в случае inner join.
Сначала рассмотрим пример semi join:
А теперь пример anti join:
Как видно, с такими типами запросов Oracle тоже научился работать.
Трансформация self join
Гораздо реже, но встречаются запросы с соединением одной и той же таблицы. К счастью, join elimination распространяется и на них, но с небольшим условием — нужно чтобы в условии соединения использовалась колонка с первичным ключом (primary key) или ограничением уникальности (unique constraint).
Такой запрос тоже с успехом трансформируется:
Rely disable и join elimination
Есть еще одна интересная особенность join elimination — он продолжает работать даже в том случае, когда ограничения (foreign key и primary key) выключены (disable), но помечены как доверительные (rely).
Для начала просто попробуем отключить ограничения и посмотрим на план запроса:
Вполне ожидаемо, что join elimination перестал работать. А теперь попробуем указать rely disable для обоих ограничений:
Как видно, join elimination заработал вновь.
На самом деле, rely предназначен для немного другой трансформации запроса . В таких случаях требуется, чтобы параметр query_rewrite_integrity был установлен в «trusted» вместо стандартного «enforced», но, в нашем случае, он ни на что не влияет и все прекрасно работает и при значении «enforced».
К сожалению, ограничения rely disable вызывают join elimination только с inner join. Стоит так же отметить, что несмотря на то, что мы можем указывать rely disable primary key или rely disable foreign key для представлений — работать для join elimination это, к сожалению, не будет.
Параметр _optimizer_join_elimination_enabled
Вместе с таким замечательным способом трансформации запроса добавился еще и скрытый параметр _optimizer_join_elimination_enabled, который по умолчанию включен (true) и отвечает за использование этой трансформации.
Если она вам надоест, то ее всегда можно выключить:
Подсказки ELIMINATE_JOIN и NO_ELIMINATE_JOIN
Добавлено после комментария xtender.
Так же, чтобы контролировать эту трансформацию, можно применять подсказки оптимизатора.
Для того, чтобы включить трансформацию, используют подсказку ELIMINATE_JOIN:
Для того, чтобы выключить трансформацию, используют подсказку NO_ELIMINATE_JOIN:
Когда join elimination плохло
В комментариях ниже xtender дал ссылку на свой интересный пример, в котором показывается, что join elimination может ухудшать план выполнения запроса. А так же дал некоторые пояснения в дальнейших комментариях.
Трансформация одинаковых соединений
Есть еще один вариант трансформации — удаление одинаковых соединений из запроса:
Эта трансформация так же отлично работает и с подзапросами, которые превращаются в соединения (subquery unnesting):
Но, такой вариант трансформации имеет некоторые отличия.
1) Для него необязательно иметь связь foreign key — primary key (или unique constraint):
2) На него не влияет отключение параметра _optimizer_join_elimination_enabled:
Но хотя бы действуют подсказки:
Итог
Подводя краткий итог, хочется сказать, что такой способ трансформации может быть действительно полезен в ряде случаев. Но полагаться на него надо тоже с умом. Если внутри вашего представления что-то поменяется и Oracle больше не сможет гарантированно определять то, что связь с таким представлением не фильтрует или не умножает строки, вы получите неожиданную потерю скорости выполнения запроса.
Узнайте, как получить расчетный и фактический план выполнения SQL при использовании команд Oracle EXPLAIN PLAN ДЛЯ или GATHER_PLAN_STATISTICS.
Вступление
В этой статье мы рассмотрим, как мы можем получить план выполнения SQL в Oracle.
При настройке производительности план выполнения незаменим, так как он позволяет понять операции, выполняемые базой данных при выполнении определенного SQL-запроса.
Каков план выполнения SQL
Как я объяснил в этой статье , SQL описал Что а не то Как , и это одна из основных причин, по которой SQL был самым популярным способом запроса базы данных на протяжении более 40 лет.
При выполнении SQL-запроса база данных сначала проанализирует его и сгенерирует AST (Абстрактное синтаксическое дерево) для входящего запроса. Анализатор также проверяет синтаксис SQL-запроса и проверяет, существуют ли ресурсы базы данных, на которые ссылаются. Анализатор может переписать входящий запрос, чтобы оптимизировать его выполнение, не влияя на логику выборки результирующего набора.
Сгенерированное дерево запросов отправляется оптимизатору, которому необходимо определить наилучший способ извлечения данных, необходимых для текущего SQL-запроса. Таким образом, оптимизатор создает план выполнения, представляющий собой список операций, которые необходимо выполнить для удовлетворения требований SQL – запроса.
Полученный план выполнения отправляется Исполнителю, который запускает его и отправляет полученные данные обратно клиенту базы данных.
Этапы обработки и выполнения SQL можно визуализировать на следующей диаграмме.
Предполагаемый план выполнения SQL
Предполагаемый план выполнения генерируется оптимизатором без выполнения SQL-запроса. Вы можете сгенерировать предполагаемый план выполнения из любого клиента SQL, используя ОБЪЯСНИТЬ ПЛАН ДЛЯ или вы можете использовать Oracle SQL Developer для этой задачи.
ОБЪЯСНИТЕ ПЛАН ДЛЯ
При использовании Oracle, если вы добавляете команду ОБЪЯСНИТЬ ПЛАН ДЛЯ к заданному SQL-запросу, база данных сохранит предполагаемый план выполнения в соответствующей таблице PLAN_TABLE :
Чтобы просмотреть предполагаемый план выполнения, вам необходимо использовать DBMS_XPLAN.DISPLAY , как показано в следующем примере:
Параметр ВСЕ +СХЕМА форматирование позволяет получить более подробную информацию о предполагаемом плане выполнения, чем при использовании параметра форматирования по умолчанию.
Предполагаемый план будет выглядеть примерно так:
Обратите внимание, что некоторые столбцы были удалены, чтобы
Разработчик Oracle SQL
Если у вас установлен SQL Developer, вы можете легко получить расчетный план выполнения для любого SQL-запроса без необходимости добавлять ПЛАН ОБЪЯСНЕНИЯ ДЛЯ команды :
Фактический план выполнения SQL
Фактический план выполнения SQL генерируется оптимизатором при выполнении SQL-запроса. Таким образом, в отличие от предполагаемого плана выполнения, вам необходимо выполнить SQL-запрос, чтобы получить его фактический план выполнения.
Фактический план не должен существенно отличаться от расчетного, если табличная статистика была должным образом собрана базовой реляционной базой данных.
Подсказка запроса GATHER_PLAN_STATISTICS
Чтобы указать Oracle хранить фактический план выполнения для данного SQL-запроса, вы можете использовать подсказку GATHER_PLAN_STATISTICS запрос:
Для визуализации фактического плана выполнения вы можете использовать DBMS_XPLAN.DISPLAY_CURSOR To visualize the actual execution plan, you can use DBMS_XPLAN.DISPLAY_CURSOR
И фактический план будет выглядеть так:
Вы также можете использовать подсказку GATHER_PLAN_STATISTICS запроса для проверки плана выполнения SQL, связанного с данным запросом JPQL или API критериев.
Для получения более подробной информации об этой теме ознакомьтесь с этой статьей .
Включите СТАТИСТИКУ на уровне подключения к БД
Если вы хотите получить планы выполнения для всех запросов, созданных в рамках данного сеанса, вы можете установить параметр STATISTICS_LEVEL сеанса в ВСЕ :
Это будет иметь тот же эффект, что и установка подсказки GATHER_PLAN_STATISTICS запроса для каждого запроса на выполнение. Таким образом, как и в случае с подсказкой GATHER_PLAN_STATISTICS запроса, вы можете использовать DBMS_XPLAN.DISPLAY_CURSOR для просмотра фактического плана выполнения.
Вам следует сбросить параметр STATISTICS_LEVEL в режим по умолчанию, как только вы закончите сбор интересующих вас планов выполнения. Это очень важно, особенно если вы используете пул соединений, а в соединениях с базой данных отказано.
Вывод
Знание разницы между расчетным и фактическим планом выполнения SQL очень важно при использовании системы реляционных баз данных.
Если вы хотите ускорить определенный SQL-запрос, вам необходимо проверить план выполнения, чтобы определить, что именно вызывает медленное выполнение запроса. Без плана выполнения SQL невозможно определить, что база данных может делать под капотом при выполнении данного SQL-запроса.
Читайте также: