пятница, 22 ноября 2013 г.

Оптимизация одного запроса с использованием _fix_control

После миграции OEBS на Oracle 11.2.0.4 выполнение Posting (а это одна из ключевых операций в GL) стало занимать от 5 минут до получаса.

Методом пристольного взгляда в окно сессий в PL/SQL Developer был выведен зловредный запрос:
UPDATE /*+ ORDERED INDEX (b, GL_BALANCES_N1) USE_NL (VW_NSO_1, b) */ GL_BALANCES B SET (PERIOD_NET_DR, PERIOD_NET_CR, QUARTER_TO_DATE_DR, QUARTER_TO_DATE_CR, PROJECT_TO_DATE_DR, PROJECT_TO_DATE_CR, BEGIN_BALANCE_DR, BEGIN_BALANCE_CR, PERIOD_NET_DR_BEQ, PERIOD_NET_CR_BEQ, BEGIN_BALANCE_DR_BEQ, BEGIN_BALANCE_CR_BEQ, TRANSLATED_FLAG, LAST_UPDATE_DATE, LAST_UPDATED_BY) = (SELECT /*+ INDEX(pi1, gl_posting_interim_162343_N1) */ NVL(B.PERIOD_NET_DR, 0) + PI1.PERIOD_NET_DR, NVL(B.PERIOD_NET_CR, 0) + PI1.PERIOD_NET_CR, NVL(B.QUARTER_TO_DATE_DR, 0) + PI1.QUARTER_TO_DATE_DR, NVL(B.QUARTER_TO_DATE_CR, 0) + PI1.QUARTER_TO_DATE_CR, NVL(B.PROJECT_TO_DATE_DR, 0) + PI1.PROJECT_TO_DATE_DR, NVL(B.PROJECT_TO_DATE_CR, 0) + PI1.PROJECT_TO_DATE_CR, NVL(B.BEGIN_BALANCE_DR, 0) + PI1.BEGIN_BALANCE_DR, NVL(B.BEGIN_BALANCE_CR, 0) + PI1.BEGIN_BALANCE_CR, NVL(B.PERIOD_NET_DR_BEQ, 0) + PI1.PERIOD_NET_DR_BEQ, NVL(B.PERIOD_NET_CR_BEQ, 0) + PI1.PERIOD_NET_CR_BEQ, NVL(B.BEGIN_BALANCE_DR_BEQ, 0) + PI1.BEGIN_BALANCE_DR_BEQ, NVL(B.BEGIN_BALANCE_CR_BEQ, 0) + PI1.BEGIN_BALANCE_CR_BEQ, PI1.TRANSLATED_FLAG, SYSDATE, :USER_ID FROM GL_POSTING_INTERIM_162343 PI1 WHERE B.SET_OF_BOOKS_ID = PI1.SET_OF_BOOKS_ID AND B.CODE_COMBINATION_ID = PI1.CODE_COMBINATION_ID AND B.ACTUAL_FLAG = PI1.ACTUAL_FLAG AND NVL(B.ENCUMBRANCE_TYPE_ID, -1) = NVL(PI1.ENCUMBRANCE_TYPE_ID, -1) AND NVL(B.BUDGET_VERSION_ID, -1) = NVL(PI1.BUDGET_VERSION_ID, -1) AND B.PERIOD_NAME = PI1.PERIOD_NAME AND B.CURRENCY_CODE = PI1.CURRENCY_CODE AND NVL(B.TEMPLATE_ID, -1) = NVL(PI1.TEMPLATE_ID, -1) AND DECODE(B.TRANSLATED_FLAG, '', -1, 'Y', 0, 'N', 0, 'R', 1, B.TRANSLATED_FLAG) = DECODE(PI1.TRANSLATED_FLAG, '', -1, 'Y', 0, 'N', 0, 'R', 1, PI1.TRANSLATED_FLAG)) WHERE (B.CODE_COMBINATION_ID, B.PERIOD_NAME, B.SET_OF_BOOKS_ID, B.CURRENCY_CODE, B.ACTUAL_FLAG, NVL(B.ENCUMBRANCE_TYPE_ID, -1), NVL(B.BUDGET_VERSION_ID, -1), NVL(B.TEMPLATE_ID, -1), DECODE(B.TRANSLATED_FLAG, '', -1, 'Y', 0, 'N', 0, 'R', 1, B.TRANSLATED_FLAG)) IN (SELECT /*+ FULL(pi2) */ PI2.CODE_COMBINATION_ID, PI2.PERIOD_NAME, PI2.SET_OF_BOOKS_ID, PI2.CURRENCY_CODE, PI2.ACTUAL_FLAG, NVL(PI2.ENCUMBRANCE_TYPE_ID, -1), NVL(PI2.BUDGET_VERSION_ID, -1), NVL(PI2.TEMPLATE_ID, -1), DECODE(PI2.TRANSLATED_FLAG, '', -1, 'Y', 0, 'N', 0, 'R', 1, PI2.TRANSLATED_FLAG) FROM GL_POSTING_INTERIM_162343 PI2)
В плане запроса появился нехороший full scan по индексу GL_BALANCES_N1.
Так же было установлено, хотя это и не важно, что full scan появлялся из-за
DECODE(B.TRANSLATED_FLAG, '', -1, 'Y', 0, 'N', 0, 'R', 1, B.TRANSLATED_FLAG) в условии IN.
На этом этапе у нас есть описание проблемы (FULL SCAN) и один запрос, поэтому тестированиие изменений при помощи EXPLAIN PLAN не занимает много времени.

Первое, что попробовал сделать -- убедился, что в прошлой версиии оптимизатора все работало нормально. Добавляем хинт /*+ optimizer_features_enable('9.2.0.8') */ и убеждаемся, что все нормально.

2. Смотрим, в какой версии оптимизатора Oracle поломал свой запрос. Допустимые значения для параметра optimizer_features_enable можно получить запросом
SELECT * FROM v$parameter_valid_values WHERE NAME LIKE '%features%' Используя хинт, убеждаемся, что план портится при переходе с версиии 11.2.0.3 на 11.2.0.4.

Тут можно остановиться, сделав
ALTER SYSTEM SET optimizer_features_enable='11.2.0.3'; Но можно пойти дальше. Найдем, что менялось в версии 11.2.0.4
SELECT * FROM v$system_fix_control WHERE optimizer_feature_enable = '11.2.0.4'; Сгенерируем список хинтов SELECT 'OPT_PARAM(''_fix_control'' ''' || bugno ||':OFF'')' FROM v$system_fix_control WHERE optimizer_feature_enable = '11.2.0.4'; и добавим эти хинты в оптимизируемый запрос. При этом у нас появится хороший план.
Удаляя хинты (можно сразу кучками) и перестраивая план, найдем номер, включение которого портит план.
У меня это Bug 13704562  Suboptimal plan for a query with an =ANY predicate

Теперь можно отключить этот фикс на уровне системы
alter system set "_fix_control"='13704562:OFF'; Проверяем постинг -- все работает

Примечание: изменение скрытого параметра _fix_control не рекомендуется без прямого указания ораклового саппорта. Но для тестовой среды вполне сойдет

Комментариев нет: