Добрый день. Есть запрос (довольно большой и сложный: >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

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


Похоже, что нет: parallel_degree_policy = 'MANUAL' для системы, на уровне сессии не переопределяется

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

optimizer_adaptive_features = FALSE (DEFAULT_VALUE = TRUE)

А в Note только (этот план я собрал не в период загрузки, но параметры БД не менялись):

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


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

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

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

Обновление выполняется сложным MERGE'ем, на план которого, теоретически, может повлиять наличие лишнего индекса. На самом деле это была перестраховка, которая в итоге сыграла злую шутку

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

Как-то так. Но вообще это уже оффтоп )

Ладно, давайте всё по порядку:

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

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

Всё сработало, огромное спасибо Valergrad и всем остальным, кто пытался помочь!