Materialized view refresh - проблема обновления в параллельном режиме

Vladimir vlad451v
Дата: 23.11.2008 02:37:39
Здравствуйте!

Версия Oracle
Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Prod

Проблема
При попытке обновления материализованного представления не наблюдается выигрыша в производительности при обновлении в параллельном режиме по сравнению с обновлением в обычном (не параллельном) режиме. В приложенном файле вы найдете параметры сервера и результаты выборки из v$px_session

С данной проблемой я столкнулся решая реальную задачу в своей компании, но т.к. не могу представить здесь даже структуру таблицы, с которой собственно работает материализованное представление, я создал тестовый пример, который показывает, что выигрыша не наблюдается.

Тестовый пример
Была создана таблица:

create table SYSTEM_OBJECTS
(
  OWNER          VARCHAR2(30),
  OBJECT_NAME    VARCHAR2(30),
  SUBOBJECT_NAME VARCHAR2(30),
  OBJECT_ID      NUMBER,
  DATA_OBJECT_ID NUMBER,
  OBJECT_TYPE    VARCHAR2(19),
  CREATED        DATE,
  LAST_DDL_TIME  DATE,
  TIMESTAMP      VARCHAR2(19),
  STATUS         VARCHAR2(7),
  TEMPORARY      VARCHAR2(1),
  GENERATED      VARCHAR2(1),
  SECONDARY      VARCHAR2(1),
  ID             INTEGER not null,
  VERSION        INTEGER
)
tablespace USERS
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  );

alter table SYSTEM_OBJECTS
  add constraint PK_SYSTEM_OBJECTS primary key (ID)
  using index 
  tablespace USERS
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  );

Данная таблица была заполнена данными из ALL_OBJECTS таким образом, что в таблице SYSTEM_OBJECTS оказалось примерно 400 тысяч записей. Далее был создан лог для материализованного представления:
CREATE MATERIALIZED VIEW LOG ON system_objects
WITH PRIMARY KEY

Также было создано само материализованное представление:
CREATE MATERIALIZED VIEW mv_system_objects
PARALLEL (DEGREE 4)
BUILD IMMEDIATE
REFRESH FAST ON DEMAND
AS
SELECT /*+ PARALLEL(system_objects, 4) */ * FROM system_objects

Именно вот этот хинт /*+ PARALLEL(system_objects, 4) */ должен указывать на то, что мат. представление будет обновляться в параллельном режиме (предложение PARALLEL (DEGREE 4) вобщем на параллельность обновления не влияет, оно влияет только на параллельность при создании).
Итак для тестирования был выполнен запрос, который обновляет всю таблицу:
UPDATE system_objects
SET version = version + 1

И наконец обновление представления происходит так:
BEGIN
    DBMS_MVIEW.REFRESH(LIST => 'MV_SYSTEM_OBJECTS',
        method => 'F',
        atomic_refresh => FALSE,
        refresh_after_errors => TRUE
    );
END;

Замечу, что параметр parallelism, который здесь может быть установлен, роли не играет - как его не устанавливай, на время обновления он не влияет.
Во время выполнения обновления проверка v$px_session дает информацию, что действительно, обновление выполняется в параллельном режиме (см. приложенный файл, там же параметры сервера), однако время обновления почти идентично со временем обновления в не параллельном режиме (на всякий случай - в параллельном режиме время 170 секунд, в обычном 173 секунды).
Эти действия выполнялись согласно [url=]www.doug.org/newsletter/march/MV_Refresh_Parallel.pdf[/url]

Вопрос
Сталкивался ли кто-нибудь с подобной проблемой? Можете ли вы предложить какие-либо пути решения проблемы ускорения обновления мат.представления?
Тынц.
Дата: 23.11.2008 03:26:42
1. alter table system_objects parallel (degree 4) ?
2. В рабочем режиме в базовой таблице тоже изменяются все строки? Если да, то зачем вам fast refresh ? complete может быть быстрее, особенно для вашей версии (truncate/insert).
3. Вы уверены, что parallel в вашем случае может дыть выигрыш?
4. Включайте трассировку и сравнивайте...
5. 10.1.0.2.0 - обновиться есть возможность?
wurdu
Дата: 23.11.2008 08:15:00
Vladimir vlad451v
При попытке обновления материализованного представления не наблюдается выигрыша в производительности при обновлении в параллельном режиме по сравнению с обновлением в обычном (не параллельном) режиме.
...
(предложение PARALLEL (DEGREE 4) вобщем на параллельность обновления не влияет, оно влияет только на параллельность при создании).
Для начала, несколько избитых фраз про то, что использование параллельного выполнения не обязательно приводит к повышению производительности. Должно быть достаточно свободных ресурсов (CPU, память, дисковая подсистема). Судя по приведенному файлу параметров, cpu_count=1. Судя по выборке из v$px_session выполнялось 6 слэйвов (было запрошено 8, но система урезала). Не уверен что на подобной конфигурации можно получить выигрыш от параллелизма (разве что за счет direct path read).
Дальше про сам термин "обновлении в параллельном режиме". Мне кажется, что он искусственно придуман и не отражает реально используемых механизмов. Обновление материализованного представления состоит из выборки изменившихся данных (при fast refresh), либо всех данных (complete refresh), а затем "накатке" этих данных на таблицу. Что при "обновлении в параллельном режиме" мы хотим распараллелить? Запросы, DML или и то и другое? Приведенный пример использует fast refresh. В этом случае распараллелить можно только запрос к изменившимся данным. Обновления будут идти в Serial, по одной строке, параметр atomic_refresh => FALSE смысла не имеет. Но и полученное распараллеливание неудачно, т.к. запрос обращается как к таблице, так и к mview log. На таблицу параллелизм задан хинтом, на лог нет, поэтому в плане будет крайне неэффективный serial to parallel. Можно в принципе задать пареллелизм для mvie log, но в любом случае для fast refresh от параллеллизма толку нет (возможно поэтому в статье, на которую ссылается автор, указано In this article, we will consider the ON DEMAND COMPLETE refresh of a materialized view).
При использовании complete refresh с параметром atomic_refresh => FALSE, Oracle выполнит insert /*+ append ... */ into ... select ... from... Выборка уже распараллелена хинтом, вставка может быть распараллелена за счет параметра PARALLEL (DEGREE 4) при создании mat. view, который автор счел как не влияющий на параллельное выполнение. Достаточно выдать alter session enable parallel dml и мы получим полный параллелизм как на select, так и на insert. Но выгоду наверное можно будет увидеть на другом железе.
Vladimir vlad451v
Дата: 23.11.2008 12:25:26
Спасибо за ваши суждения по данной проблеме.

Сперва по ответам пользователя Тынц ;-):
1. alter table system_objects parallel (degree 4) - пробовал, не сыграло роли;
2. На рабочей базе обновляются не все строки таблицы, но несмотря на это параллельный fast refresh не дает выигрыша по сравнению с непараллельным;
3. Начсет того, уверен ли я, что parallel может дыть выигрыш - это тяжелый вопрос: практика показывает, что все далеко неоднозначно. Поэтому собственно я и обсуждаю эту проблему на форуме. До обращения сюда я потратил немало времени на поиски по форумам и эксперименты, но нормального решения пока нет;
4. Трассировка пока человесекого ответа тоже не дала к сожалению;
5. Ну вобщем реальные примеры выполняются на 10.2.0.2 и другом железе, сильно мощнее, но время в параллельном режиме и не параллельном практически идентично.

Тепер по ответу wurdu: тесты выполняются на другом железе, но результат собственно то же: выигрыша нет.

Дополнение
На самом деле проблема немного шире: необходимо каждую ночь обновлять примерно 20 мат.представлений. Это занимает достаточно длительное время и стоит задача сократить это время, потому что это "держит" другие задачи, которые выполняются уже после обновления мат.представлений и зависят от этих обновлений. Вот поэтому я и начал смотреть в сторону параллельного обновления. Но пока ничего не получается.

Промежуточное решение
Пока имеется только одно решение: при помощи DBMS_JOB.SUBMIT добавить задания на обновление каждого мат.представления. Таким образом мы получаем максимальное время обновления, равное времени обновления одного мат.представления, у которого было больше всего изменений. Однако это решение мне не очень нравится в техническом плане.

Вопрос
Возможно, вы сможете предложить оптимальный вариант обновления группы мат.представлений.
wurdu
Дата: 23.11.2008 12:39:10
Просто для приведенного примера COMPLETE REFRESH принципиально эффективнее чем FAST. Может стоит посмотреть в сторону него? Сразу появится возможность распараллелить вставку, нормально распараллелить выборку, возможность применения того же nologging...
Тынц.
Дата: 23.11.2008 12:42:38
Vladimir vlad451v

1. alter table system_objects parallel (degree 4) - пробовал, не сыграло роли;

alter table mlog$_system_objects parallel (degree 4) ?
Vladimir vlad451v

2. На рабочей базе обновляются не все строки таблицы, но несмотря на это параллельный fast refresh не дает выигрыша по сравнению с непараллельным;

Вы сначала fast с complete сравните, а потом уже параллельность прикручивайте по необходимости.
Vladimir vlad451v

4. Трассировка пока человесекого ответа тоже не дала к сожалению;

Трэйсы с ожиданиями в студию.
serg2048
Дата: 24.11.2008 16:04:23
Вам не кажется что таблица SYSTEM_OBJECTS и материализованное представление mv_system_objects полностью дублируют друг друга. В чем тогда смысл использования
материализованного представления?
Vladimir vlad451v
Дата: 25.11.2008 14:16:35
serg2048
Вам не кажется что таблица SYSTEM_OBJECTS и материализованное представление mv_system_objects полностью дублируют друг друга. В чем тогда смысл использования
материализованного представления?


Да, действительно, они полностью дублируют друг друга, но когда на следующий день мне нужно создать отчет, то отчет как раз создается по материализованному представлению, которая хранит точное состояние вплоть до вчерашнего дня. Логика обновления таблиц достаточно сложна (и эта сложность оправдана), поэтому чтобы иметь точный слепок именно до вчерашнего дня, используются мат. представления.