Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Oracle Новый топик    Ответить
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
 cursor: pin S wait on X и блокировки в памяти  [new]
OEBSовед
Member

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

Подскажите, нормальная ли ситуация, когда при одновременном запуске некоторого количества одинаковых запросов происходит фактически их не параллельное выполнение в системе, а последовательное (т.е. 1 запрос лочит область памяти, а остальные "курят").

База 11.1.0.7 (поставлен последний 11.1.0.7.11 + one-off'ы по мелочи), SR заведен аж с 1 приоритетом, толку - 0.

SELECT S.LOGON_TIME, S.SID, S.LOCKWAIT, S.SQL_ADDRESS, S.SQL_ID, S.LAST_CALL_ET, S.EVENT, s.BLOCKING_SESSION 
  FROM V$SESSION S 
 WHERE USERNAME='WEBGATE' AND STATUS='ACTIVE'
 ORDER BY LAST_CALL_ET DESC



1	27.06.2012 12:20:12	1914		07000001EB330EA0	3mfchmxg0fg16	45	cursor: pin S wait on X	1966
2	27.06.2012 12:20:12	1926		07000001EB330EA0	3mfchmxg0fg16	43	cursor: pin S wait on X	1966
3	27.06.2012 12:25:58	1946		07000001EB330EA0	3mfchmxg0fg16	43	cursor: pin S wait on X	1966
4	27.06.2012 12:26:07	1956		07000001EB330EA0	3mfchmxg0fg16	34	cursor: pin S wait on X	1966
5	27.06.2012 12:26:16	1929		07000001EB330EA0	3mfchmxg0fg16	25	cursor: pin S wait on X	1966
6	27.06.2012 12:20:04	1959		07000001EB330EA0	3mfchmxg0fg16	21	cursor: pin S wait on X	1966
7	27.06.2012 12:20:10	1921		07000001EB330EA0	3mfchmxg0fg16	17	cursor: pin S wait on X	1966
8	27.06.2012 12:20:04	1966		07000001EB330EA0	3mfchmxg0fg16	12	latch: row cache objects	
9	27.06.2012 12:20:10	1927		07000001EB330EA0	3mfchmxg0fg16	8	cursor: pin S wait on X	1966
10	27.06.2012 12:26:37	1922		07000001EB330EA0	3mfchmxg0fg16	3	cursor: pin S wait on X	1966


К сообщению приложен файл. Размер - 134Kb
27 июн 12, 12:50    [12782072]     Ответить | Цитировать Сообщить модератору
 Re: cursor: pin S wait on X и блокировки в памяти  [new]
Dimitry Sibiryakov
Member

Откуда:
Сообщений: 51780

OEBSовед
при одновременном запуске некоторого количества одинаковых запросов

И эти запросы - select for update?..

Posted via ActualForum NNTP Server 1.5

27 июн 12, 12:56    [12782128]     Ответить | Цитировать Сообщить модератору
 Re: cursor: pin S wait on X и блокировки в памяти  [new]
OEBSовед
Member

Откуда:
Сообщений: 7
Dimitry Sibiryakov
И эти запросы - select for update?..

Разумеется нет, т.к. LOCKWAIT -> NULL.
Запрос достаточно сложный, формируется XML, данные из кучи таблиц, переделать нельзя, т.к. сам запрос автономно работает 2-3 сек. А если их запустить сразу 10, то начинается такой затуп. По 2-3 нормально.

Блокировка памяти в данном случае обоснована? Надо просто понять - бага это или нет, т.к. все остальные запросы работают нормально, косяк именно здесь почему то.
27 июн 12, 13:04    [12782197]     Ответить | Цитировать Сообщить модератору
 Re: cursor: pin S wait on X и блокировки в памяти  [new]
ДохтаР
Member [заблокирован]

Откуда: Новоукраинск
Сообщений: 16864
OEBSовед
параллельное выполнение в системе, а последовательное (т.е. 1 запрос лочит область памяти, а остальные "курят").

База 11.1.0.7 (поставлен последний 11.1.0.7.11 + one-off'ы по мелочи), SR заведен аж с 1 приоритетом, толку - 0.




Попробуй ручное управление памятью.
Посмотри на максимумы и поставь эти значения явно в ручном управлении.
Нам помогло.
27 июн 12, 13:06    [12782221]     Ответить | Цитировать Сообщить модератору
 Re: cursor: pin S wait on X и блокировки в памяти  [new]
OEBSовед
Member

Откуда:
Сообщений: 7
ДохтаР
Попробуй ручное управление памятью.
Посмотри на максимумы и поставь эти значения явно в ручном управлении.
Нам помогло.

Если имеете ввиду SGA_TARGET - то поставил сразу 0, но ничего не изменилось.
Грешу на "особенность" парсинга SQL с элементами XML, но никаких схожих багов не нашел.
Нашел ряд патчиков для других платформ, для версий даже 11.2, но хз - мож просто похожее чтото - там такие же большие ожидания cursor: pin S wait on X
27 июн 12, 13:16    [12782311]     Ответить | Цитировать Сообщить модератору
 Re: cursor: pin S wait on X и блокировки в памяти  [new]
Пиннер Библиотечный
Guest
Блокирующая сессия парсит запрос.
Пока не распарсит - остальные желающие выполнить тот же запрос будут ждать.

www.freelists.org/archives/oracle-l/04-2006/msg01150.html
27 июн 12, 13:19    [12782331]     Ответить | Цитировать Сообщить модератору
 Re: cursor: pin S wait on X и блокировки в памяти  [new]
comphead
Member

Откуда: Киев
Сообщений: 3390
OEBSовед,

show parameter sga_target
show parameter memory_target

а так, до 11.2.0.3 у орацела в линейке 11g, куча проблем с мутексами

кста, покурите ка
http://andreynikolaev.wordpress.com/2011/07/09/mutex-waits-part-1-%E2%80%9Ccursor-pin-s%E2%80%9D-in-oracle-10-2-11-1-invisible-and-aggressive/
27 июн 12, 13:19    [12782333]     Ответить | Цитировать Сообщить модератору
 Re: cursor: pin S wait on X и блокировки в памяти  [new]
Dimitry Sibiryakov
Member

Откуда:
Сообщений: 51780

Пиннер Библиотечный
Пока не распарсит - остальные желающие выполнить тот же запрос будут ждать.

А зачем им при этом эксклюзивный доступ?..

Posted via ActualForum NNTP Server 1.5

27 июн 12, 13:27    [12782375]     Ответить | Цитировать Сообщить модератору
 Re: cursor: pin S wait on X и блокировки в памяти  [new]
OEBSовед
Member

Откуда:
Сообщений: 7
comphead
OEBSовед,
show parameter sga_target
show parameter memory_target

memory_target с самого начала не ставили

comphead
Пиннер Библиотечный

_use_kks_mutex_pin пробовал ставить - никакого эффекта, в металинке где то находил, что в 11 версии этот параметр игнорится.
27 июн 12, 13:33    [12782421]     Ответить | Цитировать Сообщить модератору
 Re: cursor: pin S wait on X и блокировки в памяти  [new]
Alexander Anokhin
Member

Откуда: Хабаровск
Сообщений: 500
Dimitry Sibiryakov
Пиннер Библиотечный
Пока не распарсит - остальные желающие выполнить тот же запрос будут ждать.

А зачем им при этом эксклюзивный доступ?..
Они запрашивают shared pin.

Автор, сколько времени они ждут то?
Добавь к своему запросу поля STATE & SECONDS_IN_WAIT и покажи.
27 июн 12, 13:46    [12782525]     Ответить | Цитировать Сообщить модератору
 Re: cursor: pin S wait on X и блокировки в памяти  [new]
wurdu
Member

Откуда: Владивосток
Сообщений: 4441
Сталкивался недавно с такой ситуацией. Проблема была в dynamic sampling. В 11g Oracle может поднимать уровень сэмплирования и иногда поднимает до полного сканирование какой-нить огромной таблицы. Обычно связано с секционированием или параллелизмом. Сбор статистики обычно помогает. Из trace 10046 парсинга также видно.
27 июн 12, 13:52    [12782577]     Ответить | Цитировать Сообщить модератору
 Re: cursor: pin S wait on X и блокировки в памяти  [new]
Экслюзивный Доступец
Guest
Dimitry Sibiryakov
Пиннер Библиотечный
Пока не распарсит - остальные желающие выполнить тот же запрос будут ждать.

А зачем им при этом эксклюзивный доступ?..

Куда доступ?
Мутекс это.
Зачем?
Чтобы параллельно не парсить один и тот же запрос.
Это ожидание аналогично library cache pin
27 июн 12, 14:02    [12782678]     Ответить | Цитировать Сообщить модератору
 Re: cursor: pin S wait on X и блокировки в памяти  [new]
OEBSовед
Member

Откуда:
Сообщений: 7
В общем докладываю.

Выяснено, что запросы все 10 раз (а на продакшне у нас их тысячи...) проходят хард парсинг.
Почему - полагаю, что несмотря на использование биндов, по каким-то причинам запросы (по мнению оптимизатора) отличаются.

Повторный запуск запроса с одними и теми же биндами (т.е. запуск повторной пачки из 10 шт сразу) не плодит приведенные мной ожидания и все выполняется шустро (без очередей и блокировок).

Подумываю над CURSOR_SHARING=SIMILAR, но система, где все крутится - OEBS, там для базы строго рекомендовано EXACT.

Пока думаем что сделать с запросом.... О результатах доложусь.
27 июн 12, 14:02    [12782681]     Ответить | Цитировать Сообщить модератору
 Re: cursor: pin S wait on X и блокировки в памяти  [new]
pravednik
Member

Откуда: Jacksonville, FL
Сообщений: 16268
OEBSовед
Выяснено, что запросы все 10 раз (а на продакшне у нас их тысячи...) проходят хард парсинг.
Почему - полагаю, что несмотря на использование биндов, по каким-то причинам запросы (по мнению оптимизатора) отличаются.

Troubleshooting: High Version Count Issues [ID 296377.1]
27 июн 12, 14:15    [12782752]     Ответить | Цитировать Сообщить модератору
 Re: cursor: pin S wait on X и блокировки в памяти  [new]
OEBSовед
Member

Откуда:
Сообщений: 7
pravednik
Troubleshooting: High Version Count Issues [ID 296377.1]

Да это я тоже знаю... сапорт просил также.

Короче cursor_sharing='SIMILAR'/'FORCE' не помогли. Полюбас перепарсивает заново при изменении биндов.

Никто не сталкивался, запросы такого типа вообще не являются ли каким-либо исключением при парсинге, мож они так и должны всегда по новой распарсиваться?

SELECT XMLSERIALIZE(DOCUMENT(
         XMLELEMENT("DOCUMENT",
         XMLELEMENT("SERVER_DATE"...

     AS BLOB ENCODING 'WINDOWS-1251' indent) INTO v_xmlBlob FROM DUAL;
27 июн 12, 14:23    [12782807]     Ответить | Цитировать Сообщить модератору
 Re: cursor: pin S wait on X и блокировки в памяти  [new]
wurdu
Member

Откуда: Владивосток
Сообщений: 4441
OEBSовед
pravednik
Troubleshooting: High Version Count Issues [ID 296377.1]

Да это я тоже знаю... сапорт просил также.

Короче cursor_sharing='SIMILAR'/'FORCE' не помогли. Полюбас перепарсивает заново при изменении биндов.

Никто не сталкивался, запросы такого типа вообще не являются ли каким-либо исключением при парсинге, мож они так и должны всегда по новой распарсиваться?

SELECT XMLSERIALIZE(DOCUMENT(
         XMLELEMENT("DOCUMENT",
         XMLELEMENT("SERVER_DATE"...

     AS BLOB ENCODING 'WINDOWS-1251' indent) INTO v_xmlBlob FROM DUAL;
Может это банальный BIND_MISMATCH. Из v$sql_shared_cursor будет видно.
27 июн 12, 14:55    [12783072]     Ответить | Цитировать Сообщить модератору
 Re: cursor: pin S wait on X и блокировки в памяти  [new]
Nikolay Savvinov
Member

Откуда: Россия
Сообщений: 98
Симптомы указывают на то, что долго идет парсинг стейтмента. Парсящая сессия удерживает пин в экслюзивном режиме, остальные реквестят курсор в shared mode. Для того, чтобы справится с проблемой нужны следующие вещи:

1) минимизировать хард парсинг
2) разобраться, на чем висит парсящая сессия. варианты:
а) library cache lock или аналогичное ожидание, удерживаемое еще какой-то сессией (вариант -- сбор статистики с последующей инвалидацией курсоров)
б) какое-то другое ожидание
в) долго тупит на CPU из-за какого-то бага оптимизатора (например, попадался случай когда запрос с длинным IN LIST вешал чуть ли не всю базу -- оказалось, на MOS есть соотв. баг)
г) не может влезть на CPU из-за того, что тот чем-то занят. в 10-ке мутексы вообще жутко жрали процессор и могли приводить к инверсии приоритетов (например так: http://blog.tanelpoder.com/2010/04/21/cursor-pin-s-waits-sporadic-cpu-spikes-and-systematic-troubleshooting/), в 11-й вроде бы это пофиксили (у Андрея Николаева в блоге где-то было, точной ссылки под рукой нет)
27 июн 12, 15:51    [12783588]     Ответить | Цитировать Сообщить модератору
 Re: cursor: pin S wait on X и блокировки в памяти  [new]
OEBSовед
Member

Откуда:
Сообщений: 7
Значится докладываю.

Причину в итоге нашли!
Мешалась VPD-политика на одну из вьюх этого мегазапроса, в которой использовалась конкатенация в условии. В конечном запросе, например, в том же v$sqlarea условие политики не светилось, т.к. политика на вьюхе, которая в свою очередь уже в запросе, в итоге думали что виновата база, оказалось что как обычно...)
Светилось условие где-то в предикатах v$sql_plan, но не обратили внимание, подумали что константа...

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

Всем спасибо!
Проблема решена.
27 июн 12, 16:09    [12783732]     Ответить | Цитировать Сообщить модератору
Между сообщениями интервал более 1 года.
 Re: cursor: pin S wait on X и блокировки в памяти  [new]
dbms_photoshop
Member

Откуда: sqlmdx.net
Сообщений: 5151
wurdu
Сталкивался недавно с такой ситуацией. Проблема была в dynamic sampling. В 11g Oracle может поднимать уровень сэмплирования и иногда поднимает до полного сканирование какой-нить огромной таблицы. Обычно связано с секционированием или параллелизмом. Сбор статистики обычно помогает. Из trace 10046 парсинга также видно.
Тоже столкнулся. Может кому пригодится (This is due to an unpublished bug that is fixed in 12g Release 2)
High Waits for 'cursor: pin S wait on X' due to Dynamic Sampling Against Parallel Queries (Doc ID 2006145.1)

Помимо этого есть два класса запросов, генерируемых внешними системами и написанные альтернативно одаренными личностями, которые
1) используют хинт parallel без указания степени параллельности. благодаря чему она более 100
2) используют хинт precompute_subquery
В этих случаях очевидно, что надо фикстить.

Но есть еще ряд запросов, которые долго парсятся, но не попадают ни в одну категорию выше.
В часности, некоторые просто висят на CPU, так что надо ковырять глубже.
11 ноя 15, 22:44    [18403135]     Ответить | Цитировать Сообщить модератору
 Re: cursor: pin S wait on X и блокировки в памяти  [new]
wurdu
Member

Откуда: Владивосток
Сообщений: 4441
Да с долгим парсингом прямо беда. В 12c все совсем грустно из-за "улучшений" в dynamic statistics. Также всякие баги, когда он baseline может долго искать. Также недавно буквально боролись когда автогенерилка запросов пользовала десятки тысяч биндов или сотни джойнов. Проблема еще случается когда по таблице из запроса с таким долгим парсингом пытается статистика собраться. По какой-то причине сбор статистики берет эксклюзивный library cache lock на объект. Поэтому когда запрос парсится долго + сбор статистики - статистика ждет свой library cache lock, а все остальные запросы выстраиваются за этим library cache lock.
12 ноя 15, 02:44    [18403795]     Ответить | Цитировать Сообщить модератору
 Re: cursor: pin S wait on X и блокировки в памяти  [new]
SY
Member

Откуда: Middlebury, CT USA
Сообщений: 10050
wurdu
Да с долгим парсингом прямо беда. В 12c все совсем грустно из-за "улучшений" в dynamic statistics.


И не говори. Достал этот adaptive sursor sharing. Имею кучу SQL где пересбор статистики идет около минуты а улучшение времени выполнения секунд на 5. Ну и bloom filters совсем сырые. Пару дней назад добавили с сложному view еще одно невинное услоивие и решил оптимайзер использовать bloom filter. Результат - время полной выборки (около 5,000,000 строк) с 7 минут выросло до 14 часов. А убить этот цветущий вовсю сорняк не так и просто. Выставлять _bloom_filter_enabled на уровне сессии неохота ибо connection pooling. Так что приходится методом наткнулся - создаем baseline и до следующего сюрприза. Я и на OOW15 у Lewis'a спрашивал - пока у него никакого анализа на bloom filters нет - как он скaзал "чересчур умная штуковина".

SY.
12 ноя 15, 03:08    [18403799]     Ответить | Цитировать Сообщить модератору
 Re: cursor: pin S wait on X и блокировки в памяти  [new]
SQL*Plus
Member

Откуда: Россия, Москва
Сообщений: 8131
dbms_photoshop,

Мы в свое время заводили SR по этой же теме.
Рекомендовали существенно увеличить значение параметра session_cached_cursors и снизить параллелизм.
12 ноя 15, 12:56    [18405706]     Ответить | Цитировать Сообщить модератору
 Re: cursor: pin S wait on X и блокировки в памяти  [new]
SQL*Plus
Member

Откуда: Россия, Москва
Сообщений: 8131
SY
wurdu
Да с долгим парсингом прямо беда. В 12c все совсем грустно из-за "улучшений" в dynamic statistics.

И не говори. Достал этот adaptive sursor sharing. Имею кучу SQL где пересбор статистики идет около минуты а улучшение времени выполнения секунд на 5. Ну и bloom filters совсем сырые. Пару дней назад добавили с сложному view еще одно невинное услоивие и решил оптимайзер использовать bloom filter. Результат - время полной выборки (около 5,000,000 строк) с 7 минут выросло до 14 часов. А убить этот цветущий вовсю сорняк не так и просто. Выставлять _bloom_filter_enabled на уровне сессии неохота ибо connection pooling. Так что приходится методом наткнулся - создаем baseline и до следующего сюрприза. Я и на OOW15 у Lewis'a спрашивал - пока у него никакого анализа на bloom filters нет - как он скaзал "чересчур умная штуковина".

Если для всех таблиц запроса всегда имеется актуальная статистика, можно попробовать установить:

optimizer_dynamic_sampling = 0
optmizer_adaptive_features = false
12 ноя 15, 13:01    [18405741]     Ответить | Цитировать Сообщить модератору
 Re: cursor: pin S wait on X и блокировки в памяти  [new]
Вячеслав Любомудров
Member

Откуда: Владивосток
Сообщений: 18484
[quot wurduПо какой-то причине сбор статистики берет эксклюзивный library cache lock на объект. Поэтому когда запрос парсится долго + сбор статистики - статистика ждет свой library cache lock, а все остальные запросы выстраиваются за этим library cache lock.[/quot]Вот у меня такое подозрение, что это началось с 11g(R2?)
По крайней мере, именно после обновления 10.2.0.4 => 11.2.0.3 при сборе статистики (ручном, по расписанию) начали прилетать алерты об очереди блокированных сессий. Благо это происходит ночером в выходные и не сильно накаляет
12 ноя 15, 13:05    [18405775]     Ответить | Цитировать Сообщить модератору
 Re: cursor: pin S wait on X и блокировки в памяти  [new]
dbms_photoshop
Member

Откуда: sqlmdx.net
Сообщений: 5151
SQL*Plus
Если для всех таблиц запроса всегда имеется актуальная статистика, можно попробовать установить:

optimizer_dynamic_sampling = 0
optmizer_adaptive_features = false
Проблема в том, что для всей системы такое делать не хочется.
Даже на уровне сессии такое делать нежелательно, т. к. используется connection pooling и надо после выполнения проблемного запроса откатить обратно.
А здесь надо учитывать, что может возникнуть исключение и откат надо также вставлать в блок exception во всех затронутых хранимках.
Учитывая вышесказанное было желание сделать для проблемных запросов через хинт opt_param, но так оно похоже не работает.
12 ноя 15, 13:59    [18406228]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Oracle Ответить