Настройка OeBS с использованием SQL Profile

Некоторое время назад сообщили о возникшей в 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 целевого плана, так и из одной-двух принципиальных подсказок.

Advertisements
Настройка OeBS с использованием SQL Profile

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s