Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Oracle Новый топик    Ответить
 Множественное построение плана одного и того же запроса во время выполнения  [new]
Valiot85
Member

Откуда:
Сообщений: 8
Добрый день.

Есть запрос (довольно большой и сложный: >1000строк). Обычно выполняется минут за 5, но периодически возникает ситуация, когда запрос зависает на несколько часов. Из симптомов за время наблюдения удалось заметить следующее (всё нижеописанное возникает только при зависании запроса):
  • План запроса периодически перестраивается, то есть после начала выполнения план может поменяться
  • В V$SESSION_LONGOPS периодически возникают повторные выполнения строк плана, которые уже пройдены. Например запрос добежал до 265-й строки плана, затем в Longops'ах появилась, например 795-я строка плана, а потом бац и снова 128-я. И так много раз
  • В V$SQL в поле LOADS количество может доходить до сотни и даже больше (при нормальном выполнении того же запроса количество варьируется в пределах 5-ти)
  • В Event'ах периодически мелькают события Cursor: Pin S wait on X, после которых строится новый план
  • В запросе есть хинт PARALLEL. Не уверен, но почему-то есть ощущение, что он здесь причастен

Такое ощущение, что запрос просто в какой-то момент останавливается и начинает сначала и так много раз. В результате выполнение затягивается на много часов вместо 5-ти минут.

Из того, что уже попробовал:
  • Убрать PARALLEL - вроде помогает, но выполнение становится значительно дольше 5-ти минут. Плюс я не гонял его на регламенте, а не на регламенте правильное выполнение может быть связано с отсутствием нагрузки БД
  • Зафиксировать план с помощью Baseline (я решил, что проблема в том, что Oracle не может построить правильный план и нужно ему просто подсказать, как это сделать) - не помогло, правильный Baseline не подтянулся к проблемному запросу
  • Зафиксировать план с помощью профилирования и SQL-патча - патч применился, план зафиксировался, но проблему это не решило: всё равно СУБД перестраивает план помногу раз. Каждый раз при этом строится один и тот же план (видимо, благодаря патчу), но выполнение опять-таки сбрасывается и начинается заново.

Ещё замечал, что подобные проблемы иногда возникали и с другими сложными запросами, поэтому думаю, что проблема не в конкретном запросе, но все запросы с которыми это происходило были довольно тяжёлыми и сложными.

Также хочу заметить, что какой-то аномально высокой загрузки БД в моменты зависания запроса, вроде, нет (хотя это и происходит в период загрузок, то есть нагрузка на базу есть, но она не аномальная): примерно 100 активных сессий (и-то не весь период выполнения, 100 - в пике), не более 30% темпового пространства занято.

С чем вообще может быть связано такое поведение запроса? Зачем СУБД может сбрасывать текущее выполнение и начинать его заново? Ошибок при этом не возникает.

Буду благодарен за любую наводку.

БД:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
PL/SQL Release 12.1.0.2.0 - Production
CORE 12.1.0.2.0 Production
TNS for Linux: Version 12.1.0.2.0 - Production
NLSRTL Version 12.1.0.2.0 - Production
2 дек 19, 18:07    [22030587]     Ответить | Цитировать Сообщить модератору
 Re: Множественное построение плана одного и того же запроса во время выполнения  [new]
Vivat!San
Member

Откуда: Москва
Сообщений: 656
Если план уже начал выполняться, то перестраиваться не может.
Как вариант посмотрите включён ли режим Automatic degree of Parallelism (ADOP).
2 дек 19, 18:11    [22030588]     Ответить | Цитировать Сообщить модератору
 Re: Множественное построение плана одного и того же запроса во время выполнения  [new]
SY
Member

Откуда: Middlebury, CT USA
Сообщений: 9674
Скорее всего adaptive plan - смотри план на предмет:

Note
-----
   - this is an adaptive plan


Если да, то отключи и проверь (тем более что есть патч для 12.1 который, кроме всего прочего, разбивает optimizer_adaptive_features на optimizer_adaptive_plans и optimizer_adaptive_statistics).

SY.
2 дек 19, 18:30    [22030609]     Ответить | Цитировать Сообщить модератору
 Re: Множественное построение плана одного и того же запроса во время выполнения  [new]
Valiot85
Member

Откуда:
Сообщений: 8
Vivat!San
Если план уже начал выполняться, то перестраиваться не может.

А как же адаптивные планы в 12-м Oracle (https://oracle-base.com/articles/12c/adaptive-plans-12cr1)? Сам об этом узнал, когда искал решение по своей проблеме, но, похоже, план, всё-таки может меняться в процессе выполнения запроса

Vivat!San
Как вариант посмотрите включён ли режим Automatic degree of Parallelism (ADOP).

Похоже, что нет: parallel_degree_policy = 'MANUAL' для системы, на уровне сессии не переопределяется
2 дек 19, 18:54    [22030644]     Ответить | Цитировать Сообщить модератору
 Re: Множественное построение плана одного и того же запроса во время выполнения  [new]
Valiot85
Member

Откуда:
Сообщений: 8
SY
Скорее всего adaptive plan - смотри план на предмет:

Note
-----
   - this is an adaptive plan


Если да, то отключи и проверь (тем более что есть патч для 12.1 который, кроме всего прочего, разбивает optimizer_adaptive_features на optimizer_adaptive_plans и optimizer_adaptive_statistics).

SY.


Сам на них грешил, но, похоже, что нет:

optimizer_adaptive_features = FALSE (DEFAULT_VALUE = TRUE)

А в Note только (этот план я собрал не в период загрузки, но параметры БД не менялись):
Note
-----
   - dynamic statistics used: dynamic sampling (level=AUTO)
   - Degree of Parallelism is 4 because of hint
   - SQL patch "patch_con_attr" used for this statement


Сообщение было отредактировано: 2 дек 19, 18:59
2 дек 19, 18:58    [22030647]     Ответить | Цитировать Сообщить модератору
 Re: Множественное построение плана одного и того же запроса во время выполнения  [new]
Vivat!San
Member

Откуда: Москва
Сообщений: 656
Valiot85

А как же адаптивные планы в 12-м Oracle


адаптивность в плане влияния на оптимизатор, но не на уже построенный план.
2 дек 19, 19:05    [22030651]     Ответить | Цитировать Сообщить модератору
 Re: Множественное построение плана одного и того же запроса во время выполнения  [new]
Valergrad
Member

Откуда:
Сообщений: 692
Valiot85
Добрый день.


Такое ощущение, что запрос просто в какой-то момент останавливается и начинает сначала и так много раз. В результате выполнение затягивается на много часов вместо 5-ти минут.



Это может быть silent query restart. Параллельный запрос перезапускается заново ( с построением плана ) если в процессе его выполнения кто-то делает DDL по одной из таблиц которую он читает - это штатное поведение.
Чтобы убедиться что запрос именно перезапускается заново проверьте поле sql_exec_id в gv$session ( или gv$active_session_history ).
Если в процессе выполнения запросо это поле растет - то это именно оно.
Также на это указывает большое число invalidations в gv$sql или gv$sqlstat.
Проверьте - оно ли это. Если да, то подробности хорошо описаны в этой ноте:


Oracle Support Document 751588.1 (Parallel Query Fails Intermittently With ORA-12842 Starting in RDBMS 10.2.0.4+) can be found at: https://support.oracle.com/epmos/faces/DocumentDisplay?id=751588.1

Решение в общем-то простое: избегать DDL по таблицам в момент работы подобных запросов.
2 дек 19, 19:07    [22030656]     Ответить | Цитировать Сообщить модератору
 Re: Множественное построение плана одного и того же запроса во время выполнения  [new]
andrey_anonymous
Member

Откуда: Москва
Сообщений: 17760
Valiot85
добежал до 265-й строки плана, затем в Longops'ах появилась, например 795-я строка плана, а потом бац и снова 128-я. И так много раз

Это нормально.
Если лицензия позволяет, то понаблюдайте посредством dbms_sqltune.report_sql_monitor.

Что касается множественности планов - то это возможно, см. child_no.
2 дек 19, 19:08    [22030659]     Ответить | Цитировать Сообщить модератору
 Re: Множественное построение плана одного и того же запроса во время выполнения  [new]
SY
Member

Откуда: Middlebury, CT USA
Сообщений: 9674
Vivat!San

адаптивность в плане влияния на оптимизатор, но не на уже построенный план.


Adaptive Query Optimization
By far the biggest change to the optimizer in Oracle Database 12c is Adaptive Query Optimization. Adaptive Query
Optimization is a set of capabilities that enable the optimizer to make run-time adjustments to execution plans and
discover additional information that can lead to better statistics
. This new approach is extremely helpful when
existing statistics are not sufficient to generate an optimal plan. There are two distinct aspects in Adaptive Query
Optimization: adaptive plans, which focuses on improving the execution of a query and adaptive statistics, which
uses additional information to improve query execution plans.

У меня в некоторых случаях "бешеный adaptive принтер" создавал более 300 child cursor на sql_id с разными планами.

SY.
2 дек 19, 19:41    [22030698]     Ответить | Цитировать Сообщить модератору
 Re: Множественное построение плана одного и того же запроса во время выполнения  [new]
Vivat!San
Member

Откуда: Москва
Сообщений: 656
SY,

да мы тоже имели много проблем с ним,
но он может собрать статистику, если её не хватает, может ещё что-то на лету подставить по результатам выполнения
таких же запросов, но он не может менять план, который уже в работе.
2 дек 19, 19:46    [22030703]     Ответить | Цитировать Сообщить модератору
 Re: Множественное построение плана одного и того же запроса во время выполнения  [new]
SY
Member

Откуда: Middlebury, CT USA
Сообщений: 9674
Vivat!San
но он не может менять план, который уже в работе.


Согласен, но скорее всего у Valiot85 построение нового плана (у нас были случаи когда построение нового плана занимало несколько минут при том что со старым планом SQL выполнялся секунд за 40).

SY.
2 дек 19, 20:20    [22030729]     Ответить | Цитировать Сообщить модератору
 Re: Множественное построение плана одного и того же запроса во время выполнения  [new]
andrey_anonymous
Member

Откуда: Москва
Сообщений: 17760
SY
"бешеный adaptive принтер" создавал более 300 child cursor на sql_id с разными планами.
SY.

Хуже, когда этот принтер, "адаптировав" статистику, начинает генерировать неадекватные планы. С большой буквы Н.
3 дек 19, 14:07    [22031230]     Ответить | Цитировать Сообщить модератору
 Re: Множественное построение плана одного и того же запроса во время выполнения  [new]
Valiot85
Member

Откуда:
Сообщений: 8
Valergrad
Это может быть silent query restart. Параллельный запрос перезапускается заново ( с построением плана ) если в процессе его выполнения кто-то делает DDL по одной из таблиц которую он читает - это штатное поведение.

Вот это очень похоже на правду, хотя я раньше думал, что в таких случаях возникает ошибка Object no longer exists

Valergrad
Также на это указывает большое число invalidations в gv$sql или gv$sqlstat.

Да, invalidations действительно увеличено для этого запроса (не очень сильно: 10, но это вяжется с временем работы этого запроса: 30 минут вместо 5-ти) + проверил last_ddl_time объектов, которые используются в запросе - по некоторым таблицам оно как раз приходится на время загрузки.

Как показало дальнейшее расследование, скорее всего дело в нашем регламентном процессе, который на время обновления таблицы делает индексы на ней невидимыми: Invisible индекса рождает обновление DDL таблицы, ну и следовательно, приводит к silent query restart.

Есть некоторые несостыковки, конечно, например когда процесс грузился несколько часов, последний час, вроде, уже никаких DDL не должно было быть. Но проверить эти несостыковки сейчас уже нет возможности - V$SQL у нас обновляется довольно быстро.
Завтра отпишу, помогло ли отключение этого процесса.

Сообщение было отредактировано: 3 дек 19, 14:30
3 дек 19, 14:28    [22031253]     Ответить | Цитировать Сообщить модератору
 Re: Множественное построение плана одного и того же запроса во время выполнения  [new]
andrey_anonymous
Member

Откуда: Москва
Сообщений: 17760
Valiot85
в нашем регламентном процессе, который на время обновления таблицы делает индексы на ней невидимыми

А какой профит получаете от невидимости индексов во время обновления?
3 дек 19, 15:19    [22031302]     Ответить | Цитировать Сообщить модератору
 Re: Множественное построение плана одного и того же запроса во время выполнения  [new]
Valergrad
Member

Откуда:
Сообщений: 692
andrey_anonymous
Valiot85
в нашем регламентном процессе, который на время обновления таблицы делает индексы на ней невидимыми

А какой профит получаете от невидимости индексов во время обновления?


Вопрос хороший, присоединяюсь. Невидимость индексов - она для оптимизатора.
3 дек 19, 17:05    [22031414]     Ответить | Цитировать Сообщить модератору
 Re: Множественное построение плана одного и того же запроса во время выполнения  [new]
Valiot85
Member

Откуда:
Сообщений: 8
andrey_anonymous
А какой профит получаете от невидимости индексов во время обновления?

Обновление выполняется сложным MERGE'ем, на план которого, теоретически, может повлиять наличие лишнего индекса. На самом деле это была перестраховка, которая в итоге сыграла злую шутку
3 дек 19, 17:16    [22031431]     Ответить | Цитировать Сообщить модератору
 Re: Множественное построение плана одного и того же запроса во время выполнения  [new]
xtender
Member

Откуда: Мск
Сообщений: 5266
Valiot85,

не проще ли было хинт no_index заюзать...
3 дек 19, 18:18    [22031488]     Ответить | Цитировать Сообщить модератору
 Re: Множественное построение плана одного и того же запроса во время выполнения  [new]
andrey_anonymous
Member

Откуда: Москва
Сообщений: 17760
xtender
Valiot85,
не проще ли было хинт no_index заюзать...

Под загрузку merge-м?
3 дек 19, 18:48    [22031513]     Ответить | Цитировать Сообщить модератору
 Re: Множественное построение плана одного и того же запроса во время выполнения  [new]
Valiot85
Member

Откуда:
Сообщений: 8
xtender
не проще ли было хинт no_index заюзать...

Ну на самом деле всё довольно сложно, но если интересно, могу рассказать:
Это вообще общий механизм, который генерит SQL для разных таблиц, при этом индекс на PK использовать нужно, а вот все остальные индексы - нет. Написать NO_INDEX(<имя индекса>) нельзя, потому что на всех таблицах индексы по-разному называются (можно конечно докрутить механизм, но было как-то неоправдано сложно для решения "на всякий случай"), написать NO_INDEX(<алиас таблицы>) тоже нельзя по описанной выше причине (один индекс всё же использовать нужно), а тут как раз механизм отключения индексов разрабатывали и решили его заюзать. В принципе можно было и без него, скорее всего оптимизатор сам примет правильное решение, но, казалось, почему бы и нет.

Как-то так. Но вообще это уже оффтоп )
3 дек 19, 18:53    [22031519]     Ответить | Цитировать Сообщить модератору
 Re: Множественное построение плана одного и того же запроса во время выполнения  [new]
-2-
Member

Откуда:
Сообщений: 15075
Valiot85
Написать NO_INDEX(<имя индекса>) нельзя, потому что на всех таблицах индексы по-разному называются (можно конечно докрутить механизм, но было как-то неоправдано сложно для решения "на всякий случай"), написать NO_INDEX(<алиас таблицы>) тоже нельзя по описанной выше причине (один индекс всё же использовать нужно), а тут как раз механизм отключения индексов разрабатывали и решили его заюзать.
Не понял, чем перебор индексов для ddl оправданнее перебора индексов для хинтования. Если таки нужен один индекс то, очевидно, акпкбирать нужно этот один индекс +index(таблица индекс).
3 дек 19, 20:43    [22031576]     Ответить | Цитировать Сообщить модератору
 Re: Множественное построение плана одного и того же запроса во время выполнения  [new]
Valiot85
Member

Откуда:
Сообщений: 8
-2-
Не понял, чем перебор индексов для ddl оправданнее перебора индексов для хинтования. Если таки нужен один индекс то, очевидно, акпкбирать нужно этот один индекс +index(таблица индекс).

Ладно, давайте всё по порядку:
  • Изначально по архитектуре не предполагалось наличие индексов, поэтому на них и не закладывались
  • Через какое-то время конечные пользователи стали создавать индексы
  • В какой-то момент потребовался механизм отключения индексов на время загрузки (именно отключение, чтобы DML не тормозил из-за обновления индексов). Этот механизм потребовался в рамках отдельной задачи, не связанной с той, из-за которой стали делать индексы невидимыми.
  • Стали разрабатывать этот механизм, решили, что неплохо бы в него заложить возможность не только отключать индексы, но и делать их невидимыми (в случае, когда опасность только в том, что индекс может повлиять на план это оправдано, потому что сделать индекс невидимым быстрее, чем отключить, а потом включить)
  • Вспомнив, что у нас развелось много индексов решили, что можно использовать этот же механизм каждый раз, когда идёт загрузка той или иной таблицы и делать индексы невидимыми на ней на время загрузки

Механизм работает в автоматическом режиме и применяется ко всем загрузкам, поэтому добавить в него пару строк, чтобы он отключал индексы было значительно проще, чем дорабатывать функционал генерации SQL MERGE'ей (к вопросу выше)

Резюмируя: многое было завязано на существующие решения и архитектуру и так сделать было проще, но я уже понял, в чём просчитался

Сообщение было отредактировано: 4 дек 19, 17:21
4 дек 19, 17:18    [22032353]     Ответить | Цитировать Сообщить модератору
 Re: Множественное построение плана одного и того же запроса во время выполнения  [new]
Valiot85
Member

Откуда:
Сообщений: 8
Valiot85
Завтра отпишу, помогло ли отключение этого процесса.


Всё сработало, огромное спасибо Valergrad и всем остальным, кто пытался помочь!
4 дек 19, 17:19    [22032357]     Ответить | Цитировать Сообщить модератору
 Re: Множественное построение плана одного и того же запроса во время выполнения  [new]
Elic
Member

Откуда: 1984. Выбраковка финно-угром продолжается. КЯЗ
Сообщений: 29500
Valiot85
конечные пользователи стали создавать индексы
Пользователи тупы по своей натуре. Общесистемность для них как инопланетизм. Система, позволяющая такое тупицам, - труп.
5 дек 19, 07:59    [22032709]     Ответить | Цитировать Сообщить модератору
Все форумы / Oracle Ответить