Некоторое время назад сообщили о возникшей в OeBS проблеме: один из concurrent процессов завершился с ошибкой, в логах
ERROR:ORA-01555: слишком старый снимок: сегмент отката номер 147 по имени “_SYSSMU147_1401594481$” слишком мал
Процесс лечения оказался нетривиален, решил поделиться его деталями
1. В принципе понятно, что ORA-01555 обычно связана с долгим выполнением запроса. Но вот какого? Тикет завели в понедельник, а процесс упал в восресенье. В out-файле только название программы и сам текст ошибки, online-представления типа ASH и SQL Monitor уже убежали далеко вперёд. Спас alert.log, в нём нашлось точное время возникновения ошибки и sql_id запроса:
ORA-01555 caused by SQL statement below (SQL ID: 1543xmpqvggbv, Query Duration=149280 sec, SCN: 0x07cd.96f198bf)
2. Важный момент – запускался ли запрос ранее и сколько он вообще должен работать?
Идём в таблицу fnd_concurrent_requests, по request_id вычитываем значение CONCURRENT_PROGRAM_ID – это название запускаемой программы.
Выводим все запросы для этой программы и смотрим на параметры Argument1 … , сравниваем их со значениями проблемного запроса.
Собственно, находится 2 записи о выполнениия запроса:
успешное от 1 ноября длительностью 17 минут
неуспешное от 1 декабря длительностью 2-е суток
Здесь я должен сказать, что, зная sql_id, способов посмотреть его историю выполнения множество. Использование fnd_concurrent_requests для определения времени выполнения всего concurrent – неплохой вариант.
3. Следы запроса должны были остаться в awr, sql_id и дата выполнения у нас есть, идём на сервер БД и выполняем awrsqlrprt, имеем:
Плохой план выполнения 1,5 млрд. физических чтений:
Plan 1(PHV: 247063600)
Stat Name Statement Total Per Execution % Snap Total
Elapsed Time (ms) 149,276,959 149,276,959.29 31.57
CPU Time (ms) 43,535,251 43,535,250.64 15.83
Executions 1
Buffer Gets 1,696,540,316 1,696,540,316.00 10.14
Disk Reads 1,476,923,738 1,476,923,738.00 85.69
Хороший план план выполнения 105 тыс. физических чтений:
Plan 1(PHV: 1044082452)
Stat Name Statement Total Per Execution % Snap Total
Elapsed Time (ms) 718,000 718,000.36 4.22
CPU Time (ms) 616,601 616,601.26 4.96
Executions 1
Buffer Gets 153,209,612 153,209,612.00 15.52
Disk Reads 105,426 105,426.00 0.82
Если внимательно анализировать планы выполнения, то в обоих случаях используются временные таблицы и Dynamic Sampling, и при неудачном стечении обстоятельств выбирается полное сканирование одной из самых объемных таблиц OeBS.
4. Обычно в таких случаях мы использовали Baseline, однако нельзя утверждать, что они срабатывают в 100% случаев. Старые добрые хинты по-прежнему надёжнее.
Игорь Усольцев раскопал интересный документ – Whitepaper от октября 2014 года под названием Best Practices for Minimizing Oracle EBusiness Suite Release 12.1.3 Upgrade Downtime. В нём предлагается для закрепления плана выполнения использовать SQL Profile и хинты из “правильного” плана выполнения (более подробно об этом механизме можно почитать в этом посте Игоря ).
Итак, у нас есть sql_id и plan_hash_value, выполняем:
А. Вытаскиваем список хинтов плана выполнения:
SELECT * FROM table(DBMS_XPLAN.DISPLAY_AWR('1543xmpqvggbv',1044082452,null,'+OUTLINE'));
Б. На основе списка хинтов пишем небольшую PL/SQL программу по примеру из Whitepaper:
DECLARE
l_sql_fulltext clob := NULL;
lv_hint SYS.SQLPROF_ATTR := SYS.SQLPROF_ATTR ();
BEGIN
select sql_text into l_sql_fulltext from dba_hist_sqltext where sql_id = '1543xmpqvggbv';
lv_hint.EXTEND; lv_hint(1) := 'BEGIN_OUTLINE_DATA';
...
lv_hint.EXTEND; lv_hint(208) := 'END_OUTLINE_DATA';
dbms_sqltune.import_sql_profile(
sql_text => l_sql_fulltext
,category => 'DEFAULT'
,name => 'PAYSUP-169829'
,profile => lv_hint
,description => 'fixed plan for sql_id 1543xmpqvggbv'
,force_match => TRUE
);
end;
Всего секция outline содержала 206 хинтов 🙂
В. Сохраняем SQL Profile, запускаем программу, в gv$sql наблидаем за проблемным sql_id, мониторим значение поля SQL_PROFILE.
В нашем случае SQL Profile для запроса подхватился сразу же, время выполнения concurrent вернулось к 17 минутам. Работает! 🙂
P.S. Профиль, скорее всего, обладает той же степенью надёжности, что и Baseline, ибо оба способа берут данные из одного места – SYS.SQLOBJ$DATA 🙂 Но, в отличие от Baseline профиль гибче, его можно создавать как копируя полностью список хинтов из секции OUTLINE целевого плана, так и из одной-двух принципиальных подсказок.
leborchuk 