oracle.notes: Оптимизация одного запроса с использованием _fix

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

oracle.notes

2013-11-22

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

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