Материализованные представления и subquery unnesting

В ходе одного из обновлений CRM системы выяснилось, что для ускорения ответа форм статистики было создано материализованное представление на основе большого и сложного запроса. Представление планировалось обновлять раз в час, однако выполнение запроса занимало больше часа. В процессе оптимизации самого запроса удалось снизить время его выполнения до нескольких минут. Однако неожиданно при создании материализованного представления проблема вернулась. Достаточно быстро удалось выяснить, что план выполнения отличается от того, с которым тестировались. Дальше, собственно, началась настройка.

Попытка достичь желаемый план выполнения.

Запрос достаточно объемный, содержит в своем теле ссылки на view, которые используют другие view и т.д., в плане выполнения 78 шагов и масса предикатов. Анализ данных SQL Monitor показал, что основное время тратится на чтение/запись блоков в многопроходных hash join, а также чтения блоков при полных сканированиях. В то время как в желаемом плане выполнения преобладал индексный доступ. Были испробованы:

  1. Отдельные хинты типа LEADING и INDEX
  2. Комбинации хинтов INDEX
  3. Полный набор хинтов из желаемого плана выполнения
  4. Хинты типа CARDINALIRY
  5. SQL Baseline

Какие-то операции срабатывали, какие-то (например, Baseline), нет. Но ожидаемого эффекта это не давало: план выполнения отличался от желаемого, а время выполнения нисколько не уменьшалось. При этом в принципе не было проблем заменить какую-либо отдельную операцию или группу операций на другую (например, HASH JOIN на NESTED LOOP).

Причина «неправильного» плана выполнения

После 100500-й итерации настройки я обратил внимание, что в проблемном плане выполнения я ни разу не встретил объекта с названием VW_SQ_N, в то время как в желаемом плане их было несколько. Такого рода имена даются блокам запроса после выполнения стоимостных преобразований, дискуссия в блоге jonathan lewis

Основной гипотезой стало предположение о том, что не выполняется преобразование subquery unnesting при обновлении матвью. Это достаточно точно описывает все симптомы: преобразования запроса выполняются для того, чтобы расширить множество способов выполнения операций. Если же преобразованеи по какой-либо причине не может быть выполнено, то никакими хинтами и другими подсказками получить желаемый план выполнения невозможно. Явная подсказка /*+ UNNEST */ в соответствующих блоках запроса проблему не решила. Трассировка 10053 можно показала:


Subquery Unnest (SU)
********************
SU:  Checking validity of unnesting subquery SEL$3 (#0)
SU:    SU bypassed: Failed basic validity checks.
SU:  Validity checks failed.
SU:  Checking validity of unnesting subquery SEL$4 (#0)
SU:    SU bypassed: Anti-join not feasible.
SU:  Validity checks failed.
SU:  Checking validity of unnesting subquery SEL$8 (#0)
SU:    SU bypassed: Failed basic validity checks.
SU:  Validity checks failed.
SU:  Checking validity of unnesting subquery SEL$9 (#0)
SU:    SU bypassed: Anti-join not feasible.
SU:  Validity checks failed.
SU:  Checking validity of unnesting subquery SEL$10 (#0)
SU:    SU bypassed: Anti-join not feasible.
SU:  Validity checks failed.
SU:  Checking validity of unnesting subquery SEL$14 (#0)
SU:    SU bypassed: Failed basic validity checks.
SU:  Validity checks failed.
SU:  Checking validity of unnesting subquery SEL$15 (#0)
SU:    SU bypassed: Anti-join not feasible.

Поиск по ключевым словам вывел на баг:

Bug 10004943 : SUBQUERY UNNESTING FAILS CHECKS FOR CTAS BUT PASSES FOR IDENTICAL SELECT

Связан он с тем, что при create as select в group by операциях не выполняется subquery unnesting.
Действительно, в одном из view нашлась замечательная конструкция:

SELECT *
    FROM "AccountManagerLog" aml1,
         "AccountManagerLog" aml2
    WHERE aml2."EventTypeId" = :Type
          AND aml2."EventDate" >= :date
          AND aml2."ManagerSpecialRoleId" IN (:Role1,:Role2)
          AND aml2."SourceId" = :Source          
          AND aml1."Id" =
                (SELECT MAX (aml3."Id")
                        KEEP (DENSE_RANK FIRST ORDER BY aml3."EventDate" DESC)
                    FROM "AccountManagerLog" aml3
                  WHERE aml3."EventDate" >= :date
                        AND aml3."AccountId" = aml2."AccountId"
                        AND aml3."EventTypeId" = :Type
                        AND aml3."ManagerSpecialRoleId" IN (:Role1,:Role2)
                        AND aml3."SourceId" = :Source
                        AND aml3."EventDate" < aml2."EventDate")

Здесь KEEP DENSE RANK без фразы OVER означает (и выполняется как) GROUP BY.

Ручное выполнение преобразования subquery unnesting

Судя по Note, баг обещают исправить только в 12c. Так что единственный доступный способ настройки — самостоятельно выполнить преобразование. Тем более, что в этом конкретном случае оно должно быть безусловно выполнено, т.к. без него не работает проталкивание предикатов, а сам запрос выполняется десятки минут. Сам запрос тоже хорош: в нем 2 таблицы соединяются через 3-ю таблицу в секции in. За пару часов мне удалось привести его к такому вот виду:

select *
from
(SELECT  aml1.*, row_number() over (partition by aml2."Id" order by aml3."EventDate" desc, aml3."Id" desc) as max_1_ID
    FROM  "AccountManagerLog" aml1,
          "AccountManagerLog" aml2,
          "AccountManagerLog" aml3
    WHERE aml2."EventTypeId" =:Type
          AND aml2."EventDate" >= :date
          AND aml2."ManagerSpecialRoleId" IN (:Role1,:Role2)
          AND aml2."SourceId" = :Source
          AND aml1."Id" = aml3."Id"
          AND aml3."EventDate" >= :date
                        AND aml3."AccountId" = aml2."AccountId"
                        AND aml3."EventTypeId" = :Type
                        AND aml3."ManagerSpecialRoleId" IN (:Role1,:Role2)
                        AND aml3."SourceId" = :Source
                        AND aml3."EventDate" < aml2."EventDate"
) b
where max_1_ID = 1

В результате время построения матвью снизилось с более чем часа до 10 минут. А спустя полгода этот функционал и вовсе был переписан (после весьма настойчивых требований 😉 ), так что необходимость в подобного рода материализации отпала вовсе.

P.S. По поводу причин отказа в выполнении преобразования у нас внутри разгорелась небольшая дискуссия. Игорь Усольцев справедливо заметил, что

Указанный Bug 10004943 : SUBQUERY UNNESTING FAILS CHECKS FOR CTAS BUT PASSES FOR IDENTICAL SELECT больше касается CTAS, но не совсем подходит для операции создания MV, отличной от CTAS и выполняемой по моим наблюдениям где-то на уровне OPTIMIZER_FEATURES_ENABLE(’10.1.0′)

Соответствующий документ How to Troubleshoot Slow CREATE MATERIALIZED VIEW Issues (Doc ID 1313218.1), в частности, говорит, что не только Subquery Unnesting, но и все остальные CBQT трансформации не используются для запросов создания/обновления MV — «небольшие ограничения оптимизации» 🙂

…there are some optimization restrictions during mview creation/refresh e.g. there is no Cost Based Query Transformation (refer to Bug 6840494, which is not feasible to fix, where it explained that CBQT disabled for mview operation)

Однако, вот что странно:

  1. В трассировке 10053 четко видно, что какие-то трансформации всё-же выполняются. Возможно, это преобразования уровня эвристических, но, судя по их количеству, CBQT там тоже должны были происходить
  2. Хинты также не работают, а для них CBQT не нужен. Похоже, отключается не только механизм CBQT, а и целые классы преобразований.

Вобщем, есть еще поле для исследований 🙂

Реклама

4 ответ. на "Материализованные представления и subquery unnesting"

  1. Лёня, небольшое уточнение, указанный баг исправлен согласно Bugs fixed in the 11.2.0.3 Patch Set, и исправление действует по умолчанию:

    11.2.0.3@ SQL> select * from v$system_fix_control where bugno = 10004943;
     
       BUGNO VALUE SQL_FEATURE                    DESCRIPTION                                              OPTIMIZER_FEATURE_ENABLE  EVENT IS_DEFAULT
    -------- ----- ------------------------------ -------------------------------------------------------- ------------------------- ----- ----------
    10004943     1 QKSFM_TRANSFORMATION_10004943  enable removal of group by in subquery for create table  11.2.0.3                      0          1
    • Да, скорее всего это не он, на MOS появился другой, более интересный документ

      Create Materialized View Or Complete Refresh Taking Longer Than CTAS Or Insert Select (Doc ID 763718.1)

      Плюс в 12c есть такой вот Fix control

      9852856 Enable CBQT for MV refresh

      Так что CBQT, действительно, был отключен как класс. но в 12c настанет счастье 🙂

      • отлично, очень интересный фикс! будем тестировать

  2. Уведомление: CBQT при создании/обновлении матвью в 12.1.0.1 | Oracle mechanics

Добавить комментарий

Заполните поля или щелкните по значку, чтобы оставить свой комментарий:

Логотип WordPress.com

Для комментария используется ваша учётная запись WordPress.com. Выход /  Изменить )

Google photo

Для комментария используется ваша учётная запись Google. Выход /  Изменить )

Фотография Twitter

Для комментария используется ваша учётная запись Twitter. Выход /  Изменить )

Фотография Facebook

Для комментария используется ваша учётная запись Facebook. Выход /  Изменить )

Connecting to %s