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

Откуда:
Сообщений: 686
Добрый день. На 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 килобайт - но там ничего интересного, тихое-мирное выполнение.

К сообщению приложен файл (ParallelSlaves.zip - 136Kb) cкачать
24 окт 19, 21:09    [22002096]     Ответить | Цитировать Сообщить модератору
 Re: Параллельные слейвы не могут договориться насчет плана выполнения?  [new]
Valergrad
Member

Откуда:
Сообщений: 686
Продолжаю расследование...Если вкратце это сочетание вот этого:

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, может будет что-то яснеее...
25 окт 19, 15:49    [22002780]     Ответить | Цитировать Сообщить модератору
 Re: Параллельные слейвы не могут договориться насчет плана выполнения?  [new]
Valergrad
Member

Откуда:
Сообщений: 686
Корректные ссылки на документы:

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 довольно много. Век живи - век учись.
25 окт 19, 15:51    [22002784]     Ответить | Цитировать Сообщить модератору
 Re: Параллельные слейвы не могут договориться насчет плана выполнения?  [new]
Кобанчег
Member

Откуда: Рахів
Сообщений: 593
Valergrad
...
в середине выполнения происходит add_partition к одной из таблиц
...
должен происходить стандартный рестарт запроса по инвалидации
...

Доводилось иметь проблемы подобного характера, прежде всего стоит сказать, что после разговоров с Oracle Support было выяснено,
что нет гарантированного пути выяснить был ли рестарт или нет.
Не существует какой-либо статистики, хитрого ивента или чего-то еще.
В некоторых случаях косвенно можно понять по v$sql.invalidations или множественным выполнениям в v$sql_monitor,
но можно сымитировать рестарт когда ни там ни там ничего не будет отражено.

Если вкратце, это очень нехорошая идея делать DDL на секционированных таблицах когда на них в то же время выполняются тяжелые insert/selects.

Типичные проблемы с которыми сталкивались при таком сценарии.
1. Одновременные вставка в таблицу и добавление партиции. Крайне медленная производительность, множественные рестарты.
Было решено секции нарезать пачками чтоб этого избежать.
Insert Statement On Partitioned Tables Is Restarted After Invalidation (Doc ID 1462003.1)
2. Одновременное удаление секции в таблице и вставка (или чтение).
Либо ужасная производительность с постоянными "cursor: pin S wait on X" из-за ре-парса либо вообще могло упасть с
"ORA-04031: unable to allocate 32 bytes of shared memory"
Bug 19461270 - high PRTMV allocations in shared pool executing concurrent DML and DDLs on interval partitioned tables (Doc ID 19461270.8)

Это могут быть случаи немного отличные от твоего и можно конечно ковырять дальше, только не совсем понятно что еще хочется узнать.
Основная мысль, что надо избежать любые altre table во время выполнения тяжелых запросов.

Если вопрос, увеличивает ли параллельное выполнения вероятность возникновения проблемы, ответ - да.
В некоторых (многих) случаях без параллельности не воспроизводится.
25 окт 19, 17:09    [22002879]     Ответить | Цитировать Сообщить модератору
 Re: Параллельные слейвы не могут договориться насчет плана выполнения?  [new]
Valergrad
Member

Откуда:
Сообщений: 686
[quot Кобанчег]
Valergrad
...
только не совсем понятно что еще хочется узнать.


Почему оракл для этого запроса не может осуществить silent restart с 10 попыток. У нас довольно много запросов которые обращаются к этой и другим изменяющимся таблицам, но падает только этот.
28 окт 19, 13:27    [22004166]     Ответить | Цитировать Сообщить модератору
 Re: Параллельные слейвы не могут договориться насчет плана выполнения?  [new]
Elic
Member

Откуда: 1984. Выбраковка финно-угром продолжается. КЯЗ
Сообщений: 29402
Valergrad
Почему оракл для этого запроса не может осуществить silent restart с 10 попыток.
У тебя с адекватностью всё в норме?
Не знаешь, где находится MOS?
28 окт 19, 13:40    [22004184]     Ответить | Цитировать Сообщить модератору
 Re: Параллельные слейвы не могут договориться насчет плана выполнения?  [new]
Valergrad
Member

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


Узнаю старого злого Elica. Если есть что сказать по теме - пишите. Если нет - промолчите. Оскорбления, обидки, пассивно-агрессивные вопросы и прочий мусор просьба оставить при себе.
28 окт 19, 16:58    [22004441]     Ответить | Цитировать Сообщить модератору
 Re: Параллельные слейвы не могут договориться насчет плана выполнения?  [new]
Pavel_PV
Member

Откуда:
Сообщений: 73
Знакомо, было.
Лечили хинтами. Делаешь план ровно таким каким он должен быть используя стандартные хинты leading/full/index/cardinality/no_merge и т.д...счас глянул, ещё это вкорячил: OPTIMIZER_FEATURES_ENABLE('11.2.0.3').
Обращай внимание на то каким образом распределяются параллели.
Помню запрос был на строк на 500, хинтовал наверное пару дней. Но после всё стабилизировалось, работает стабильно.

Как простой вариант, можешь попробовать отрубить DS или поставить хинтом в 0. Если прокатит и заработает, то считай повезло.
30 окт 19, 05:17    [22005642]     Ответить | Цитировать Сообщить модератору
 Re: Параллельные слейвы не могут договориться насчет плана выполнения?  [new]
Кобанчег
Member

Откуда: Рахів
Сообщений: 593
Valergrad
Если есть что сказать по теме - пишите.
Но ведь понятно примерно, что возникает если параллельно идет обращение к таблице и на ней же выполняется DDL. Кроме того, возникает если запрос выполняется в пареллели.
Мало кто будет спорить, что описанное поведение баг и вроде как очевидно, что надо избегать DDL, но если хочется ковыряться дальше...
Valergrad
Почему оракл для этого запроса не может осуществить silent restart с 10 попыток.
Хочется чтоб выполнялся с 10, 20, 100500 попыток? Зачем? Как меряются попытки?
(кстати, вспомнил, что еще рестарты можно косвенно отследить по v$active_session_history.sql_exec_id, v$active_session_history.sql_exec_start)
Valergrad
но падает только этот
Читателям предлагается поупражняться в угадывании?
Ну может быть от тривиальных причин типа использования временных таблиц до сложно вылавливаемого когда воспроизводится, если данных нет в буферном кеше.
Вот было например, что при параллельном выполнении были wrong results для
select *
from fact f join dim d on d.dt = :d and d.id = f.dim_id
where f.dt = :d
and ...
Но всё ок
select *
from fact f join dim d on d.dt = f.dt and d.id = f.dim_id
where f.dt = :d
and ...
(обе таблицы секционированы по дате - dt)
Кстати, оно лечилось и без переписывания с помощью "alter session set optimizer_features_enable", но это всё-таки проблема немного из другой оперы.
Мысль в том, что сценарий может быть достаточно специфический, но вместо ковыряния лучше приложить усилия для устранения основных факторов.

PS. Прикрепленный архив не смотрел, в моём колхозе запрещено качать файло с форумов.

PPS. Единственный смысл ковыряния пожалуй, это чтоб не прививались ложные страхи.
Потому как в Оракле для параллельных запросов огромное число багов самого разного характера, но как правило для возникновения таки нужна некоторая особенность.
Знание вот этих "особенностей" может помочь, чтоб не создавалось впечатление что параллельные запросы - полнейшая кривизна.
А то есть категория личностей контуженных ANSI синтаксисом на ранних версиях которые видимо до конца жизни будут пихать плюсики везде где надо и не надо.
30 окт 19, 14:08    [22006054]     Ответить | Цитировать Сообщить модератору
 Re: Параллельные слейвы не могут договориться насчет плана выполнения?  [new]
Valergrad
Member

Откуда:
Сообщений: 686
Кобанчег
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.


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

select * From GV$System_FIX_CONTROL where bugno = 22258145;


Так что должно быть именно 10 попыток. Я теоретически могу представить, что внутренняя функция добавления партиции держала какую-то блокировку, и таким образом 10 попыток хард-парса прошли быстрее чем она успела ее освободить. Но все же выглядит странно - тогда все время падали бы разные запросы, и одни и те же запросы бы то падали, то нет. Но падает ровно один запрос, и он падает всегда когда есть такое взаимодействие. Подобных взаимодействий на этой кодовой базе ( один запрос читает, другой пишет ) - буквально выше крыши, но падает только один запрос. Переписать всю кодовую базу чтобы в принципе избежать подобных взаимодействий - задача слишком большая, работу с партициями предыдущие разработчики разбросали буквально по всему коду, и никто на нее денег не даст. Но если понять при каких условиях возникает это "не могу с 10 попыток разпарсить", то можно будет где-то точечно поправить в нескольких местах и внедрить некие паттерны кода. Скажем, если это проблема именно с CTAS, то можно несколько CTAS переписать на insert as select, или добавить именно в эти места блоки ловящие 12842.
30 окт 19, 14:44    [22006097]     Ответить | Цитировать Сообщить модератору
 Re: Параллельные слейвы не могут договориться насчет плана выполнения?  [new]
Кобанчег
Member

Откуда: Рахів
Сообщений: 593
Valergrad
Можешь сам убедиться - создать какой-нибудь тест, добавить в соседней сессиии партицию, и убедиться что запрос скорее всего тихо рестартанет без внешних ошибок.
Спасибо, я уже достаточно наигрался с одновременными DDL и запросами на секционированных таблицах.
Но с удовольствием почитаю когда ты напишешь в чём была соль твоего конкретно случая. :)
30 окт 19, 19:13    [22006474]     Ответить | Цитировать Сообщить модератору
 Re: Параллельные слейвы не могут договориться насчет плана выполнения?  [new]
Надфиль
Member

Откуда:
Сообщений: 159
у меня тоже с партициями были затруднения.
для каждого поставщика была заведена отдельная партиция. при новом прайсе старая партиция дропалась и пересоздавалась.
на 11 оракле и без кластера работало.
на 12 запрос к партиции часто падал с ошибкой "логического чтения".
тема на форуме гдето есть.
перестали дропать. заработало норм.
30 окт 19, 20:37    [22006533]     Ответить | Цитировать Сообщить модератору
 Re: Параллельные слейвы не могут договориться насчет плана выполнения?  [new]
Valergrad
Member

Откуда:
Сообщений: 686
Кстати нашел немножко времени и проверил что если create table as select , заменить на insert select, то запрос при добавлении партиции в процессе не падает, а тихо рестартует как и должен. Т.е. проблема именно с CTAS.
К сожалению, в 11.2 для insert as select не работают блумфильтры и некоторые другие вещи, так что производительность такого способа решения не очень.
31 окт 19, 17:28    [22007416]     Ответить | Цитировать Сообщить модератору
 Re: Параллельные слейвы не могут договориться насчет плана выполнения?  [new]
K790
Member

Откуда:
Сообщений: 617
Valergrad,

Здесь говорят не обращайте внимания
31 окт 19, 17:43    [22007439]     Ответить | Цитировать Сообщить модератору
 Re: Параллельные слейвы не могут договориться насчет плана выполнения?  [new]
Valergrad
Member

Откуда:
Сообщений: 686
Еще немножко поизучал тестовый пример пытаясь понять, что делает его таким специфичным что наверх всплывает 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' избавляет от этой ошибки. Но в этом случае запрос работает после тихой инвалидации сериально, что конкретно нам не подойдет ( но кому-то может и подойдет ).
2 ноя 19, 14:06    [22008703]     Ответить | Цитировать Сообщить модератору
Все форумы / Oracle Ответить