Oracle удалить план запроса

Обновлено: 04.07.2024

У меня есть ситуация, когда Oracle создает несколько планов выполнения для данного запроса. Большую часть времени он выбирает определенный, который хорошо работает. Иногда, однако, он выбирает тот, который содержит декартовое соединение, что очень неправильно. Если мы удалим план декартовых объединений и запустим запрос с одним из других планов, он будет работать хорошо, что подсказывает мне, что базовые данные действительно не нуждались в картезианстве.

Мы попытались собрать статистику и поиграть с гистограммами, но, похоже, в итоге план выполнения декартовых соединений возвращается и используется с перерывами (иногда это занимает недели или месяцы).

Возможно ли в Oracle отключить конкретный план выполнения? Мы не можем просто удалить его, потому что он, кажется, возвращается, но оставляя его там, и его отключить должно служить исправлением, но я действительно не знаю, как это сделать или если это возможно.

спросил(а) 2017-06-09T08:20:00+03:00 4 года, 5 месяцев назад

Как упоминалось в комментариях, использование базовых линий SQL Plan является официальным способом отключения плана выполнения. Это работает, но это невероятно болезненно, как показывает приведенный ниже код.

Создайте плохой план, создав объекты и собирая статистику перед загрузкой данных.

План запроса неверен. Он думает, что есть только одна строка, когда запрос действительно вернет 100 000 строк. Он использует NESTED LOOPS, когда он должен использовать HASH JOIN.

Выполните вышеуказанный запрос без explain plan for создания реального SQL_ID. Затем найдите SQL_ID (5ukbyc726cdu3).

Используйте этот SQL_ID для создания базовой линии SQL для сбора информации о запросе.

Здесь вы можете увидеть базовый уровень SQL Plan. Сейчас он имеет только один план:

Позвольте создать лучший план, собрав статистику и повторный запуск.

Но подождите, что новый план еще не работает. Обратите внимание, что плохой план все еще используется. Обратите внимание на раздел " Note " - он использует базовый уровень плана SQL.

Теперь есть два плана для базовой линии SQL. Первый принят, а новый, лучший план - нет.

Развивайте план потенциального принятия нового плана. Точный выход функции здесь не имеет значения, но вы можете посмотреть на него, если вам интересно.

Еще раз взгляните на исходные данные, и они оба приняты.

Установите ENABLED для NO для старого плана и установите для него новый YES для YES. Это не обязательно, если новый план лучше, но это гарантирует, что старый план никогда не будет использоваться.

Убедитесь, что старый план больше не включен.

Теперь запрос будет использовать только новый, лучший план с Rows установленными на 100K, и используя HASH JOIN вместо NESTED LOOP.

Поздравляем, если вы сделали это так далеко

Вышеприведенный код ужасен. Oracle действительно бросил мяч с этой системой, но это "официальный" способ сделать это.

Обычно лучше избегать базовых показателей плана SQL и найти другое решение. Узнайте, что вызывает плохой план выполнения, и остановите его.

В этой небольшой заметке я хочу поделиться тем, как из процедурного кэша удалить определенные планы запросов. Когда может возникать такая необходимость? Например, хранимую процедуру запустили с таким параметром, который привел к формированию неоптимального плана запроса для хранимой процедуры. И скорее всего для этого конкретного случая план является оптимальным, но чаще эту процедуру используют совсем с другими параметрами, для которых план уже не будет оптимальным. Естественно, необходимо поработать над такой процедурой и запросами в ней, чтобы подобные случаи больше не повторялись, но в текущий момент нам необходимо убрать план запроса из кэша, чтобы новый запуск хранимой процедуры привел к компиляции и формированию нужного плана. Естественно при этом мы не хотим затронуть другие планы в кэше, чтобы не вызвать массовую перекомпиляцию запросов и лишнюю нагрузку.

Вариант первый, можно изменить хранимую процедуру саму на себя. Например в Object Explorer выбрать хранимую процедуру, сделать Script Store Procedure as -> ALTER to -> New Query Window и выполнить то, что получилось.

Script Stored Procedure

Это приведет к тому, что при следующем запуске хранимой процедуры произойдет ее перекомпиляция. Метод очень простой и быстрый, но имеет один недостаток: это ведет к изменению поля modify_date в системном представлении sys.objects, хотя по сути дела никаких изменений мы не вносили.

Вариант второй, более сложный, сделать запрос к системному преставлению sys.dm_exec_cached_plans, получить нужный хэндл (или хэндлы) неоптимальных планов и удалить их из кэша с помощью DBCC FREEPROCCACHE. Например, в своей тестовой базе я сделаю такой запрос и получу значение поля plan_handle.

SELECT TOP 10 cp.cacheobjtype, cp.objtype, cp.plan_handle, t.text FROM sys.dm_exec_cached_plans cp CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) t WHERE t.text LIKE '%uspGetBillOfMaterials%' AND t.dbid = DB_ID('AdventureWorks2008R2')

Проверяем, что в поле text у нас действительно нужная хранимая процедура, и после этого запускаем DBCC FREEPROCCACHE. В качестве параметра передаем значение поля plan_handle.

Все, ненужный нам план из кэша удален и при следующем запуске процедуры произойдет перекомпиляция.

Как правило оптимизатор запросов 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 без хинта.