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

Откуда:
Сообщений: 107
oracle 12.2.0.1 RAC 2 node
ни с того ни с сего одна из сессий с таким ожиданием "повесила" весь кластер.
самая ординарная сессия. она подвесила еще пару тысяч точно таких же..

К сообщению приложен файл. Размер - 68Kb
29 май 19, 12:08    [21896558]     Ответить | Цитировать Сообщить модератору
 Re: library cache lock куда копать?  [new]
Надфиль
Member

Откуда:
Сообщений: 107
дополню.
попытался по параметрам идентифицировать объект
select to_char(29188906832,'xxxxxxxxxxxxxx') from dual;

6cbcb5f50

select * from x$kglob where kglhdpar =hextoraw('00000006CBCB5F50');

из "интересного" 
kglobtyp  126 
KGLOBTYD Optimizer Finding

Куда мне пойти дальше пока не придумал :-)
29 май 19, 12:25    [21896584]     Ответить | Цитировать Сообщить модератору
 Re: library cache lock куда копать?  [new]
CrazyCat
Member

Откуда:
Сообщений: 2953
смотри кто ее блокирует и что он делает. обычно это DDL...
29 май 19, 15:13    [21896785]     Ответить | Цитировать Сообщить модератору
 Re: library cache lock куда копать?  [new]
Надфиль
Member

Откуда:
Сообщений: 107
CrazyCat
смотри кто ее блокирует и что он делает. обычно это DDL...

нету там ничего похожего на DDL...
это обычная сессия. коннект. несколько селектов. дисконнект.
по результатам селекта возможны инсерты и апдейты, но в другую базу. на майскуле.
29 май 19, 15:28    [21896825]     Ответить | Цитировать Сообщить модератору
 Re: library cache lock куда копать?  [new]
andrey_anonymous
Member

Откуда: Москва
Сообщений: 17580
Надфиль
коннект. несколько селектов. дисконнект.

Параметры "нескольких селектов" литералами?
Sequences nocache?
Кто-то индексы перестраивает или таблички двигает?
29 май 19, 15:35    [21896851]     Ответить | Цитировать Сообщить модератору
 Re: library cache lock куда копать?  [new]
Надфиль
Member

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

1. нет этот программист следует моим рекомендациям "используй переменные, а то ногами забью" :D
2. у меня нет в базе секвенсев которые дергаются часто и не кэшируются.
3. да не. это слишком тривиально. я бы такое заметил. ну и обычно это ведет к невалидности кучи объектов. а тут самая обычная сессия расшалилась. причем у меня сложилось впечатление, что она повесила этот "лок" сразу после коннекта....
29 май 19, 15:43    [21896872]     Ответить | Цитировать Сообщить модератору
 Re: library cache lock куда копать?  [new]
xtender
Member

Откуда: Мск
Сообщений: 5216
Надфиль,

покажите
sho parameter opt%adaptive
sho parameter _optimizer_dsdir_usage_control
29 май 19, 15:53    [21896903]     Ответить | Цитировать Сообщить модератору
 Re: library cache lock куда копать?  [new]
xtender
Member

Откуда: Мск
Сообщений: 5216
Надфиль
KGLOBTYD Optimizer Finding

У Игоря Усольцева уже было: https://iusoltsev.wordpress.com/2018/03/25/tm-library-cache-deadlock-cursor-parsing-optimizer-finding/
29 май 19, 15:54    [21896905]     Ответить | Цитировать Сообщить модератору
 Re: library cache lock куда копать?  [new]
Надфиль
Member

Откуда:
Сообщений: 107
xtender
Надфиль,

покажите
sho parameter opt%adaptive
sho parameter _optimizer_dsdir_usage_control


SQL> sho parameter opt%adaptive

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
optimizer_adaptive_plans             boolean     FALSE
optimizer_adaptive_reporting_only    boolean     FALSE
optimizer_adaptive_statistics        boolean     TRUE
SQL> sho parameter _optimizer_dsdir_usage_control
SQL>
29 май 19, 16:08    [21896945]     Ответить | Цитировать Сообщить модератору
 Re: library cache lock куда копать?  [new]
Надфиль
Member

Откуда:
Сообщений: 107
xtender
У Игоря Усольцева уже было: https://iusoltsev.wordpress.com/2018/03/25/tm-library-cache-deadlock-cursor-parsing-optimizer-finding/

видел уже. увидел там DDl в начале, пропустил. ча еще раз посмотрю.
29 май 19, 16:09    [21896952]     Ответить | Цитировать Сообщить модератору
 Re: library cache lock куда копать?  [new]
andrey_anonymous
Member

Откуда: Москва
Сообщений: 17580
Надфиль
optimizer_adaptive_plans             boolean     FALSE
optimizer_adaptive_reporting_only    boolean     FALSE
optimizer_adaptive_statistics        boolean     TRUE
SQL> sho parameter _optimizer_dsdir_usage_control

Наоборот жеж...
29 май 19, 16:10    [21896956]     Ответить | Цитировать Сообщить модератору
 Re: library cache lock куда копать?  [new]
Надфиль
Member

Откуда:
Сообщений: 107
andrey_anonymous
Надфиль
optimizer_adaptive_plans             boolean     FALSE
optimizer_adaptive_reporting_only    boolean     FALSE
optimizer_adaptive_statistics        boolean     TRUE
SQL> sho parameter _optimizer_dsdir_usage_control

Наоборот жеж...

поясни если не жалко?
29 май 19, 16:12    [21896965]     Ответить | Цитировать Сообщить модератору
 Re: library cache lock куда копать?  [new]
andrey_anonymous
Member

Откуда: Москва
Сообщений: 17580
Надфиль
andrey_anonymous
пропущено...
Наоборот жеж...

поясни если не жалко?

Если по рекомендациям для 12с выставляли, то адаптивные планы - добро, адаптивная статистика - зло.
29 май 19, 16:13    [21896968]     Ответить | Цитировать Сообщить модератору
 Re: library cache lock куда копать?  [new]
Надфиль
Member

Откуда:
Сообщений: 107
andrey_anonymous
Если по рекомендациям для 12с выставляли, то адаптивные планы - добро, адаптивная статистика - зло.

адаптивные планы я отрубил сразу после апгрейда с 11.2 до 12.
запарили "новые, улучшенные" планы выполнения. когда запрос работавший менее одной сотой секунды начинал работать 10000 секунд... это с практической ТЗ приводило к фактическому падению всего хозяйства. ибо когда запрос вызываемые сотни раз в секунду начинает работать на десять порядков медленней....
а до статистики я видимо не дошел. но вряд ли рискну менять такие параметры без веских оснований на работающей базе. полгода уже работает. и пускай работает. хотя описываемый инцидент испортил мне карму).
еще я полностью(надеюсь) отрубил автоматическое "распараллеливание"..
29 май 19, 16:20    [21896976]     Ответить | Цитировать Сообщить модератору
 Re: library cache lock куда копать?  [new]
andrey_anonymous
Member

Откуда: Москва
Сообщений: 17580
Надфиль
andrey_anonymous
Если по рекомендациям для 12с выставляли, то адаптивные планы - добро, адаптивная статистика - зло.

адаптивные планы я отрубил сразу после апгрейда с 11.2 до 12.
запарили "новые, улучшенные" планы выполнения

Так проблема растет из баговой адаптивной статистики, адаптивные планы сами по себе проблем особых не доставляли.
29 май 19, 16:27    [21896993]     Ответить | Цитировать Сообщить модератору
 Re: library cache lock куда копать?  [new]
Надфиль
Member

Откуда:
Сообщений: 107
andrey_anonymous
Так проблема растет из баговой адаптивной статистики, адаптивные планы сами по себе проблем особых не доставляли.

спасибо, попробую таки в одно из окон. отключение статистки может привести к массовым перестроениям планов и т.д?
почитаю что пишут по этому поводу.
29 май 19, 16:33    [21897009]     Ответить | Цитировать Сообщить модератору
 Re: library cache lock куда копать?  [new]
xtender
Member

Откуда: Мск
Сообщений: 5216
Надфиль,

Это вы должны были прочитать ещё при апгрейде на 12.2
29 май 19, 16:49    [21897032]     Ответить | Цитировать Сообщить модератору
 Re: library cache lock куда копать?  [new]
usolcew
Member

Откуда: Россия
Сообщений: 121
поддержу рекомендацию:
OPTIMIZER_ADAPTIVE_STATISTICS => FALSE отключит _OPTIMIZER_DSDIR_USAGE_CONTROL => 0, что исключит влияние SQL Plan Directives+Findings

но проблема всё-таки, возможно не в этом (CrazyCat выше писал, ASH в помощь)
и library cache lock/pin в этом же сценарии "поймать" ещё получится, просто объект Library Cache будет другой, например, Cursor
29 май 19, 16:53    [21897048]     Ответить | Цитировать Сообщить модератору
 Re: library cache lock куда копать?  [new]
Надфиль
Member

Откуда:
Сообщений: 107
xtender
Надфиль,

Это вы должны были прочитать ещё при апгрейде на 12.2

понятно что должен. просто столько всего нужно прочитать при этом.
апгрейд был "эпичным" смена одновременная, железа, ОС, версии БД, ну и как вишенка на торте переход, на кластер.
я прямо таки удивлен, что это получилось работоспособным..
29 май 19, 16:57    [21897061]     Ответить | Цитировать Сообщить модератору
 Re: library cache lock куда копать?  [new]
run09
Member

Откуда:
Сообщений: 42
Надфиль
xtender
Надфиль,

Это вы должны были прочитать ещё при апгрейде на 12.2

понятно что должен. просто столько всего нужно прочитать при этом.
апгрейд был "эпичным" смена одновременная, железа, ОС, версии БД, ну и как вишенка на торте переход, на кластер.
я прямо таки удивлен, что это получилось работоспособным..

При таких эпиках я оставляю optimizer_feature_enable = оригинальной версии на очень-очень долго
30 май 19, 12:51    [21897627]     Ответить | Цитировать Сообщить модератору
 Re: library cache lock куда копать?  [new]
Надфиль
Member

Откуда:
Сообщений: 107
usolcew
поддержу рекомендацию:
OPTIMIZER_ADAPTIVE_STATISTICS => FALSE

передернул вчера, ничего не предвещало.
утром заработало пара участков не активных в выходные. и получил в паре мест ora-13831
утро пнд не тот время когда нужно разбираться с чем то не до конца ясным. откатил назад.
что странно, запросов для которых прибил план через smp много, и совершенно точно большая часть из них работала сутки.
но споткнулось на парочке....
ладно как говорят японцы "хусим", вернусь к этому вопросу когда будет больше времени на реагирование.
3 июн 19, 15:00    [21900574]     Ответить | Цитировать Сообщить модератору
 Re: library cache lock куда копать?  [new]
andrey_anonymous
Member

Откуда: Москва
Сообщений: 17580
Doc ID 2360823.1
3 июн 19, 15:17    [21900599]     Ответить | Цитировать Сообщить модератору
 Re: library cache lock куда копать?  [new]
Надфиль
Member

Откуда:
Сообщений: 107
andrey_anonymous
Doc ID 2360823.1

спасибо, я уже читал, но не вникал. повторю опыт после установки ПСУ.
3 июн 19, 15:27    [21900606]     Ответить | Цитировать Сообщить модератору
 Re: library cache lock куда копать?  [new]
large5
Member

Откуда: Баку
Сообщений: 25
обычно полезнее всего : копаьт в сторону ухода от литералов и переписывания запросов с использованием bind variables .
3 июн 19, 17:20    [21900734]     Ответить | Цитировать Сообщить модератору
 Re: library cache lock куда копать?  [new]
Надфиль
Member

Откуда:
Сообщений: 107
large5
обычно полезнее всего : копаьт в сторону ухода от литералов и переписывания запросов с использованием bind variables .

я уже писал на эту тему. в той части функционала, которая породила проблемную ссесию, используются только переменные.
хотя есть несколько модулей? "авторов" которых мне полностью не удается "переубедить" и они не полностью пока избавились от дурной привычки. ну или им не хватает квалификации, во что я, конечно, поверить не могу :-)
памяти на серверах вагон, врядли закончилось место в шаред пуле..
буду благодарен за "аргументы" в моей дискуссии с программистами за использование и переменных и против использования конструкций when others then вместо тривиального no_data_found...
3 июн 19, 21:31    [21900953]     Ответить | Цитировать Сообщить модератору
 Re: library cache lock куда копать?  [new]
xtender
Member

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

Во-первых, причем тут литералы? А во-вторых, не всегда нужно литералы заменять на бинды, иногда надо и наоборот.
4 июн 19, 00:18    [21901031]     Ответить | Цитировать Сообщить модератору
 Re: library cache lock куда копать?  [new]
alex-ls
Member

Откуда: Иркутская обл - Пенза - Москва
Сообщений: 6710
Надфиль
и против использования конструкций when others then вместо тривиального no_data_found...

если они пишут
when others then null;

где попало, то тут только руки отрубать и голову...
хотя если такая конструкция используется например при загрузке всякого мусора из внешних источников, то вполне себе :)
правда все равно лучше в лог записать, что там было
4 июн 19, 05:57    [21901076]     Ответить | Цитировать Сообщить модератору
 Re: library cache lock куда копать?  [new]
Надфиль
Member

Откуда:
Сообщений: 107
alex-ls
when others then null;

написал же что 90% замена when no_data_found
в обработке, как правило, устанавливаются дефолтные значения перемененных которых в запросе не нашлось.
alex-ls
где попало, то тут только руки отрубать и голову...

но пишут где попало, и уже бывали прецеденты, что "замаскированная" ошибка приводила к неправильной работе и трудноуловимым ошибкам..
alex-ls
хотя если такая конструкция используется например при загрузке всякого мусора из внешних источников, то вполне себе :)
правда все равно лучше в лог записать, что там было

я и сам иногда могу использовать, когда нужно побыстрому что нибудь сделать.. но это исключения скорей.
4 июн 19, 09:07    [21901162]     Ответить | Цитировать Сообщить модератору
 Re: library cache lock куда копать?  [new]
Надфиль
Member

Откуда:
Сообщений: 107
xtender
large5,

Во-первых, причем тут литералы?

ну прям вот в мастерноте 1952395.1 по поводу ошибки как одна из основных причин ожиданий приводит проблема с литерами и шаред пулом. но я у себя ничего из перечисленного не обнаружил.
xtender
А во-вторых, не всегда нужно литералы заменять на бинды, иногда надо и наоборот.

ну это экзотика. в общем случае.
4 июн 19, 09:10    [21901165]     Ответить | Цитировать Сообщить модератору
 Re: library cache lock куда копать?  [new]
alex-ls
Member

Откуда: Иркутская обл - Пенза - Москва
Сообщений: 6710
Надфиль
написал же что 90% замена when no_data_found

вот возникнет too_many_rows и разберись потом что там было :D

Вы просто пишите помогите с аргументами, т.е. они сами не понимают почему надо писать по-другому? Какие специальные аргументы нужны?

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

вот за такое надо наказывать, особенно если разбирать этот говнокод потом другим приходится, на прошлой работе любили такое...
4 июн 19, 09:13    [21901168]     Ответить | Цитировать Сообщить модератору
 Re: library cache lock куда копать?  [new]
alex-ls
Member

Откуда: Иркутская обл - Пенза - Москва
Сообщений: 6710
Надфиль
xtender
А во-вторых, не всегда нужно литералы заменять на бинды, иногда надо и наоборот.

ну это экзотика. в общем случае.

хочешь 2 разных плана для запроса, можно литералы разные поставить, вместо bind например :)
4 июн 19, 09:14    [21901169]     Ответить | Цитировать Сообщить модератору
 Re: library cache lock куда копать?  [new]
Надфиль
Member

Откуда:
Сообщений: 107
alex-ls
вот возникнет too_many_rows и разберись потом что там было :D

да там все что угодно может возникнуть. и никто об этом не узнает. во всяком случае сразу.
alex-ls
Вы просто пишите помогите с аргументами, т.е. они сами не понимают почему надо писать по-другому? Какие специальные аргументы нужны?

да эта просьба скорей жалоба во вселенную.
понимают. но гораздо проще же без переменных сочинить where потом в group by, да и Order by он мутирует. не меняя особо сути запроса. короче, чтобы написать правильный универсальный запрос нужно напрягаться.
этот аргумент перевешивает.
4 июн 19, 09:22    [21901175]     Ответить | Цитировать Сообщить модератору
 Re: library cache lock куда копать?  [new]
alex-ls
Member

Откуда: Иркутская обл - Пенза - Москва
Сообщений: 6710
Надфиль
понимают. но гораздо проще же без переменных сочинить where потом в group by, да и Order by он мутирует. не меняя особо сути запроса. короче, чтобы написать правильный универсальный запрос нужно напрягаться.
этот аргумент перевешивает.

это не проще, ведь потом этот говнокод падает и его надо разгребать! кому хочется это делать? проще сразу нормально написать
4 июн 19, 10:10    [21901224]     Ответить | Цитировать Сообщить модератору
 Re: library cache lock куда копать?  [new]
Надфиль
Member

Откуда:
Сообщений: 107
alex-ls
это не проще, ведь потом этот говнокод падает и его надо разгребать! кому хочется это делать? проще сразу нормально написать

да я не спорю. результат потом один. проблемы везде.
4 июн 19, 10:12    [21901228]     Ответить | Цитировать Сообщить модератору
 Re: library cache lock куда копать?  [new]
xtender
Member

Откуда: Мск
Сообщений: 5216
Надфиль
ну прям вот в мастерноте 1952395.1
конкретно в твоем случае причину уже указали и она с литералами не связана, и, более того, проблемы с sql plan directives чаще на запросах с биндами и появляются. И бинды и литералы надо использовать с умом. Если нужно два разных курсора, то лучше сделать литералы, чем мучаться с хреново работающими adaptive фичами.
4 июн 19, 11:17    [21901324]     Ответить | Цитировать Сообщить модератору
 Re: library cache lock куда копать?  [new]
Надфиль
Member

Откуда:
Сообщений: 107
xtender
конкретно в твоем случае причину уже указали и она с литералами не связана

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

в моей случае использование литералов уж точно не от большого ума.
из 1с, например, лазят в базу. объяснить "программисту" 1с правила доступа к ораклу не просто... а уж научить передавать переменные из псевдо языка......
но он почти уже справился.)
4 июн 19, 11:35    [21901347]     Ответить | Цитировать Сообщить модератору
 Re: library cache lock куда копать?  [new]
xtender
Member

Откуда: Мск
Сообщений: 5216
Надфиль
optimizer_adaptive_statistic?
мне показалось что это одно из мнений. вероятная причина.

Это говорит достаточно однозначно:
Надфиль
kglobtyp  126 
KGLOBTYD Optimizer Finding
4 июн 19, 12:05    [21901394]     Ответить | Цитировать Сообщить модератору
 Re: library cache lock куда копать?  [new]
Надфиль
Member

Откуда:
Сообщений: 107
xtender,
хорошо, спасибо.
поменяю вместе с глобальным патчем настройку.
4 июн 19, 12:12    [21901400]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: 1 2      [все]
Все форумы / Oracle Ответить