Добрый день. На 11.2.0.4 RAC наблюдается интересная ситуация:

- есть запрос который работает 10 минут и читает множество таблиц. Работало стабильно.
- некий девелопер добавляет хинт PARALLEL(4) к одной части этого запроса, в результате небольшой кусок плана запроса становится параллельным.
- теперь запрос периодически падает. Причем падает так странно что ни в логах ничего не пишется, ни в alter.log ничего нет ( по словам dba ). Сессия просто пропадает с радаров.
- анализ gv$active_session_history и dba_audit_trail показывает что падение происходит тогда и только тогда когда в середине выполнения происходит add_partition к одной из таблиц , которая читается в этом запросе.
- казалось бы в этом случае должен происходить стандартный рестарт запроса по инвалидации. Но у нас вот сессия тихо падает без следов. Пристальный взгляд на gv$active_session_history показывает что действительно - в 24-OCT-19 13.59.28.553 ( это время добавления партиции ) пошел как бы рестарт и хард парс ( это видно по in_parse = 'Y' и in_hard_parse = 'Y' ). Но что-то пошло не так: параллельные слейвы схватили cursor: pin S wait on X, а затем еще более странное: разные параллельные слейвы получили разные планы выполнения по этому запросу! Часть сгенерила план 168711662, часть - 3817760802. Неудивительно что запрос помучившись так 10 секунд - падает вместе с сессий.


Кто-нибудь сталкивался с чем-то подобным? Я раньше думал, что параллельные слейвы будут долбить хардпарс пока не договорятся насчет плана, а если не смогут - пойдут сериально. Ну, мне кто-то такое рассказывал. Кусок gv$active_session_history ( все кроме полей program, machine, module, action ) прилагаю. Из него вырезано несколько строк в середине чтобы влезло в 150 килобайт - но там ничего интересного, тихое-мирное выполнение.

Продолжаю расследование...Если вкратце это сочетание вот этого:

https://support.oracle.com/epmos/faces/DocumentDisplay?_afrLoop=535731515206893&id=751588.1&_afrWindowMode=0&_adf.ctrl-state=8z31u8ipg_4

и вот этого:

https://support.oracle.com/epmos/faces/DocumentDisplay?_afrLoop=535733572185754&id=2040678.1&_afrWindowMode=0&_adf.ctrl-state=8z31u8ipg_45

Благодаря первому оракл выдает -12842 если за 10 попыток не удается получить нормальный ( или одинаковый для всех ) план .
Благодаря второму - полученный экцепшен проходит как нож сквозь масло через все попытки его записать.

Все еще непонятно почему за 10 попыток хард-парса для данного конкретного запроса не удается получить план. Что в нем такого особенного? Запустил снятие трейса 10053, может будет что-то яснеее...

Корректные ссылки на документы:

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


Oracle Support Document 2040678.1 (Partitioning Operation Silently Fails with ORA-12842 During Concurrent TRUNCATE) can be found at: https://support.oracle.com/epmos/faces/DocumentDisplay?id=2040678.1

Оказывается ошибок которые не ловятся when others довольно много. Век живи - век учись.

[quot Кобанчег]Valergrad...
только не совсем понятно что еще хочется узнать.


Почему оракл для этого запроса не может осуществить silent restart с 10 попыток. У нас довольно много запросов которые обращаются к этой и другим изменяющимся таблицам, но падает только этот.

ElicValergradПочему оракл для этого запроса не может осуществить silent restart с 10 попыток.У тебя с адекватностью всё в норме?
Не знаешь, где находится MOS?

Узнаю старого злого Elica. Если есть что сказать по теме - пишите. Если нет - промолчите. Оскорбления, обидки, пассивно-агрессивные вопросы и прочий мусор просьба оставить при себе.

КобанчегValergradПочему оракл для этого запроса не может осуществить silent restart с 10 попыток.Хочется чтоб выполнялся с 10, 20, 100500 попыток? Зачем? Как меряются попытки?


Это отрывок из ноты которую я скинул выше:

авторNote: Parallel retry was implemented in 11.2. Starting in 11.2, the error will be detected and the parallel query resubmitted automatically 10 times before failing with ORA-12842. In environments where the database is very busy with PMOPs on the table being queried, 10 retries may not be enough, in which case you would need to use one of the solutions below.

Оракл делает 10 попыток рестарта начиная с 11.2, и именно поэтому собственно такие запросы не падают каждый раз. Можешь сам убедиться - создать какой-нибудь тест, добавить в соседней сессиии партицию, и убедиться что запрос скорее всего тихо рестартанет без внешних ошибок.
Там же ниже:

авторHOWEVER, the fix for unpublished BUG 22258145 - CODE IN OPIEXE APPEARS TO BE INCORRECT effectively reversed the 10-retry behavior for some cases for ddl, pl/sql function, and call method, as it was found to cause errors. For example, if you are running a testcase using a pl/sql function with a UNION all query, and concurrently doing ddl in an infinite loop from another session to see if the code will retry, you will find that is does not retry. This code change is first found in 12.2, but if you have applied an interim patch for this bug, you will see the ORA-12842 with no retries in RDBMS version 11.2+ under certain conditions. Development is currently working to see if there is a way to reintroduce the 10-retry behavior.

Вроде бы у нас этого патча нет -




Так что должно быть именно 10 попыток. Я теоретически могу представить, что внутренняя функция добавления партиции держала какую-то блокировку, и таким образом 10 попыток хард-парса прошли быстрее чем она успела ее освободить. Но все же выглядит странно - тогда все время падали бы разные запросы, и одни и те же запросы бы то падали, то нет. Но падает ровно один запрос, и он падает всегда когда есть такое взаимодействие. Подобных взаимодействий на этой кодовой базе ( один запрос читает, другой пишет ) - буквально выше крыши, но падает только один запрос. Переписать всю кодовую базу чтобы в принципе избежать подобных взаимодействий - задача слишком большая, работу с партициями предыдущие разработчики разбросали буквально по всему коду, и никто на нее денег не даст. Но если понять при каких условиях возникает это "не могу с 10 попыток разпарсить", то можно будет где-то точечно поправить в нескольких местах и внедрить некие паттерны кода. Скажем, если это проблема именно с CTAS, то можно несколько CTAS переписать на insert as select, или добавить именно в эти места блоки ловящие 12842.

Кстати нашел немножко времени и проверил что если create table as select , заменить на insert select, то запрос при добавлении партиции в процессе не падает, а тихо рестартует как и должен. Т.е. проблема именно с CTAS.
К сожалению, в 11.2 для insert as select не работают блумфильтры и некоторые другие вещи, так что производительность такого способа решения не очень.

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

1) CTAS, на insertах происходит silent query restart.
2) Parallel, без параллел конечно все норм.
3) Должен быть DDL в параллельной сессии на объекте из запроса, разумеется.
4) Объект из запроса должен быть materialize, если его заинлайнить - то ошибки не происходит
5) Важен порядок операций в плане. Ошибка происходит если объект над которым делался DDL, был заматериалайзен до этого DDL, а используется - уже после него. Т.е. операции TEMP TABLE TRANSFORMATION и TABLE ACCESS STORAGE FULL должны быть по разную сторону от параллельного DDL.
6) Судя по всему, еще какие-то условия. Потому что когда я попытался создать голый пример на этих условиях, ошибку вызвать не получилось.


Также замечу, для тех кто не знал, что _fix_control"='7170213:OFF' избавляет от этой ошибки. Но в этом случае запрос работает после тихой инвалидации сериально, что конкретно нам не подойдет ( но кому-то может и подойдет ).