Материализованные представления в Oralce TimesTen 11g

В Oracle TimesTen существует возможность создания материализованных представлений. Именно про данную возможность и пойдет речь далее. Согласно документации, в TimesTen существует два типа материализованных представлений: синхронные (Synchronous materialized view) и асинхронные (Asynchronous materialized view).

Синхронные материализованные представления

Синхронные материализованные представления существовали еще в версии 7.0.5. Они находятся постоянно в синхронном состоянии со всеми таблицами, на которые они ссылаются (detail tables). Это может сказываться на производительности, т.к. транзакция, меняющая таблицу, меняет и результат материализованного представления. Рассмотрим пример создания синхронного материализованного представления.



Пример сосздания синхронного материализованного представления

Создадим пользователя и предоставим ему необходимые привилегии.

oracle@tt matview]$ ttisql db_bi

Copyright (c) 1996-2011, Oracle.  All rights reserved.
Type ? or "help" for help, type "exit" to quit ttIsql.

connect "DSN=db_bi";
Connection successful: DSN=db_bi;UID=oracle;DataStore=/u01/app/oracle/datastore/db_bi;DatabaseCharacterSet=AL32UTF8;ConnectionCharacterSet=AL32UTF8;NLS_LENGTH_SEMANTICS=CHAR;DRIVER=/u01/app/oracle/product/11.2.1/TimesTen/tt1121/lib/libtten.so;PermSize=3000;TempSize=1500;TypeMode=0;PLSQL_TIMEOUT=1000;CacheGridEnable=0;
(Default setting AutoCommit=1)
Command> CREATE USER oratt IDENTIFIED BY oracle;

User created.

Command> grant create session,create table to oratt;
Command> grant CREATE MATERIALIZED VIEW to oratt;
Command>

Создадим таблицу и наполним ее данными. Таблица строится на основе представления ALL_SOURCE (т.к. строк в представлении ALL_SOURCE мало, то я увеличиваю количество строк в 1000 раз).

[oracle@tt matview]$ ttisql "DSN=db_bi;UID=oratt;PWD=oracle;"

Copyright (c) 1996-2011, Oracle.  All rights reserved.
Type ? or "help" for help, type "exit" to quit ttIsql.


connect "DSN=db_bi;UID=oratt;PWD=oracle;";
Connection successful: DSN=db_bi;UID=oratt;DataStore=/u01/app/oracle/datastore/db_bi;DatabaseCharacterSet=AL32UTF8;ConnectionCharacterSet=AL32UTF8;NLS_LENGTH_SEMANTICS=CHAR;DRIVER=/u01/app/oracle/product/11.2.1/TimesTen/tt1121/lib/libtten.so;PermSize=3000;TempSize=1500;TypeMode=0;PLSQL_TIMEOUT=1000;CacheGridEnable=0;
(Default setting AutoCommit=1)
Command> create table my_all_source as select * from all_source;
7566 rows inserted.
Command> tables;
  ORATT.MY_ALL_SOURCE
1 table found.
Command> set autocommit 0;
Command> begin
       >   for i in 1 .. 1000 loop
       >     insert into my_all_source select * from all_source;
       >     commit;
       >   end loop;
       > end;
       > /

PL/SQL procedure successfully completed.

Command> select count(*) from my_all_source;
< 7573566 >
1 row found.
Command>

Теперь выполним запрос к данной таблице, показывающий количество системных объектов и количество строк в данном объекте.

Command> set autocommit 0;
Command> set timing 1;
Command> select name, count(*) from my_all_source group by name;
< DBMS_LOCK, 172172 >
< DBMS_OUTPUT, 158158 >
< DBMS_PREPROCESSOR, 226226 >
< DBMS_RANDOM, 87087 >
< DBMS_SQL, 1675674 >
< DBMS_STANDARD, 31031 >
< DBMS_UTILITY, 816816 >
< PLITBLM, 115115 >
< STANDARD, 3310307 >
< TT_DB_VERSION, 36036 >
< UTL_IDENT, 17017 >
< UTL_RAW, 927927 >
12 rows found.
Execution time (SQLExecute + Fetch Loop) = 1.623664 seconds.

Как видно, запрос выполнился за 1.6 секунды.
Посмотрим план выполнения.

Command> set timing 0;
Command> set showplan 1;
Command> select name, count(*) from my_all_source group by name;

Query Optimizer Plan:

  STEP:                1
  LEVEL:               2
  OPERATION:           TblLkSerialScan
  TBLNAME:             MY_ALL_SOURCE
  IXNAME:              
  INDEXED CONDITION:   
  NOT INDEXED:         


  STEP:                2
  LEVEL:               1
  OPERATION:           GroupBy
  TBLNAME:             
  IXNAME:              
  INDEXED CONDITION:   
  NOT INDEXED:         

< DBMS_LOCK, 172172 >
< DBMS_OUTPUT, 158158 >
< DBMS_PREPROCESSOR, 226226 >
< DBMS_RANDOM, 87087 >
< DBMS_SQL, 1675674 >
< DBMS_STANDARD, 31031 >
< DBMS_UTILITY, 816816 >
< PLITBLM, 115115 >
< STANDARD, 3310307 >
< TT_DB_VERSION, 36036 >
< UTL_IDENT, 17017 >
< UTL_RAW, 927927 >
12 rows found.

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

Command> CREATE MATERIALIZED VIEW my_all_source_aggr AS select name, count(*) count from my_all_source group by name;
12 rows materialized.
Command>

Теперь выполним запрос к данному представлению.

Command> set timing 1;
Command> select * from my_all_source_aggr;
< DBMS_LOCK, 172172 >
< DBMS_OUTPUT, 158158 >
< DBMS_PREPROCESSOR, 226226 >
< DBMS_RANDOM, 87087 >
< DBMS_SQL, 1675674 >
< DBMS_STANDARD, 31031 >
< DBMS_UTILITY, 816816 >
< PLITBLM, 115115 >
< STANDARD, 3310307 >
< TT_DB_VERSION, 36036 >
< UTL_IDENT, 17017 >
< UTL_RAW, 927927 >
12 rows found.
Execution time (SQLExecute + Fetch Loop) = 0.000108 seconds.

Следовательно, получили существенное увеличение производительности данного запроса.

Теперь попробуем вставить строку в таблицу my_all_source и посмотрим на результат в материализованном представлении.

Command> insert into my_all_source (OWNER, NAME, TYPE,LINE,TEXT) values ('GENA', 'TEST', 'TEST', 1 ,'TEST');
Command> commit;
Command> select * from my_all_source_aggr;
< DBMS_LOCK, 172172 >
< DBMS_OUTPUT, 158158 >
< DBMS_PREPROCESSOR, 226226 >
< DBMS_RANDOM, 87087 >
< DBMS_SQL, 1675674 >
< DBMS_STANDARD, 31031 >
< DBMS_UTILITY, 816816 >
< PLITBLM, 115115 >
< STANDARD, 3310307 >
< TT_DB_VERSION, 36036 >
< UTL_IDENT, 17017 >
< UTL_RAW, 927927 >
< TEST, 1 >
13 rows found.
Command>

Видно, что при изменении таблицы my_all_source сразу произошло изменение синхронного материализованного представления.

К сожалению, механизма, аналигичного QUERY_REWRITE в Oracle Database, в TimesTen нет, поэтому, для улучшения производительности необходимо не только создать материализованное представление, но и на уровне приложения изменить доступ к данным.




Супер блокировки (super locks) при использовании синхронных материализованных представлений

При работе с данным видом материализованных представлений, могут возникнуть супер блокировки. Рассмотрим пример.

Создадим таблицу и наполним ее данными.

Command> create table tab1 (id number not null, name varchar2(50));
Command> desc tab1;

Table ORATT.TAB1:
  Columns:
    ID                              NUMBER NOT NULL
    NAME                            VARCHAR2 (50) NOT INLINE

1 table found.
(primary key columns are indicated with *)
Command> begin
       >   for i in 1 .. 1000 loop
       >     insert into tab1 select 1, 'test'||i from dual;
       >   end loop;
       >   for i in 1001 .. 2000 loop
       >     insert into tab1 select 2, 'test'||i from dual;
       >   end loop;
       > end;
       > /

PL/SQL procedure successfully completed.

Command> select count(*) from tab1;
< 2000 >
1 row found.
Command>

Создадим синхронное материализованное представление, агрегирующее записи таблицы tab1.

Command> CREATE MATERIALIZED VIEW tab1_mv as select id, count(*) count from tab1 group by id;
2 rows materialized.
Command> select * from  tab1_mv;
< 1, 1000 >
< 2, 1000 >
2 rows found.
Command>

Проверим отсутствие блокировок а базе данных.

[oracle@tt ~]$ ttXactAdmin db_bi
2011-07-26 05:23:47.996
/u01/app/oracle/datastore/db_bi
TimesTen Release 11.2.1.8.0

0 outstanding transactions found

Теперь в двух сессиях попробуем изменить данные в таблице tab1.

Сессия 1.

Command> set autocommit 0;
Command> update tab1 set id=1 where id=1 and name='test1000';
1 row updated.
Command>

Проверим наличие блокировок.

[oracle@tt ~]$ ttXactAdmin db_bi
2011-07-26 05:26:01.672
/u01/app/oracle/datastore/db_bi
TimesTen Release 11.2.1.8.0

Outstanding locks

PID     Context            TransID     TransStatus Resource  ResourceID           Mode  SqlCmdID             Name

Program File Name: ttIsqlCmd

30285   0x1f4618b0            1.78     Active      Database  0x01312d0001312d00   IX    0
                                                   EndScan   BMUFVUAAAAKAAAAED1   U     3152599496           ORATT.TAB1_MV
                                                   Table     718144               IXn   3152599496           ORATT.TAB1_MV
                                                   Row       BMUFVUAAACNYwAAFD8   Xn    3152599496           ORATT.TAB1
                                                   Table     718128               IXn   3152599496           ORATT.TAB1

1 outstanding transaction found


Наблюдаем эксклюзивную блокировку на строку в таблице tab1 (718128). Но, кроме этой блокировки, наблюдаем также блокировку на материализованное представление TAB1_MV (718144).

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

Сессия 2.

Command> set autocommit 0;
Command> update tab1 set id=1 where id=1 and name='test900';

После этого Сессия 2 подвисла. Смотрим блокировки.

[oracle@tt ~]$ ttXactAdmin db_bi
2011-07-26 05:33:08.354
/u01/app/oracle/datastore/db_bi
TimesTen Release 11.2.1.8.0

Outstanding locks

PID     Context            TransID     TransStatus Resource  ResourceID           Mode  SqlCmdID             Name

Program File Name: ttIsqlCmd

30285   0x1f4618b0            1.78     Active      Database  0x01312d0001312d00   IX    0
                                                   EndScan   BMUFVUAAAAKAAAAED1   U     3152599496           ORATT.TAB1_MV
                                                   Table     718144               IXn   3152599496           ORATT.TAB1_MV
                                                   Row       BMUFVUAAACNYwAAFD8   Xn    3152599496           ORATT.TAB1
                                                   Table     718128               IXn   3152599496           ORATT.TAB1

Program File Name: ttIsqlCmd

30364   0x11a938b0            2.1      Active      Database  0x01312d0001312d00   IX    0
                                                   Row       BMUFVUAAACPYwAAEAS   S     3151419904           ORATT.TAB1_MV
                                                   Row       BMUFVUAAACPYwAADAS   S     3151419904           ORATT.TAB1_MV
                                                   Command   3151419904           S     3151419904
                                                   Table     718144               IXn   3151419904           ORATT.TAB1_MV
                                                   Row       BMUFVUAAACNYwAABD2   Xn    3151419904           ORATT.TAB1
                                                   Table     718128               IXn   3151419904           ORATT.TAB1

Awaiting locks

PID     Context            TransID     Resource  ResourceID           RMode RSqlCmdID            HolderTransID HMode HSqlCmdID            Name
30364   0x11a938b0            2.1      EndScan   BMUFVUAAAAKAAAAED1   U     3151419904              1.78       U     3152599496           ORATT.TAB1_MV

2 outstanding transactions found


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

6003: Lock request denied because of time-out
Details: Tran 2.1 (pid 30364) wants U lock on end-table ORATT.TAB1_MV. But tran 1.78 (pid 30285) has it in U (request was U). Holder SQL (update tab1 set id=1 where id=1 and name='test1000')
The command failed.

Как видно из примера, TimesTen блокирует все данные, от которых зависит результат выполнения запроса, на котором основано материализованное представление. Особенно сильно это проявляется при использовании агрегирования и объединения таблиц (блокируются все сроки таблиц влияющих на результат). Таким образом, при использовании сложных запросов с группировками и объединениями таблиц при построении материализованных представлений, может образоваться огромное количество блокировок, приводящих к катострофическим потерям производительности. Поэтому рекомендуется не строить синхронные материализованные представления с использованием сложных запросов, или, если необходимо это сделать, использовать асинхронные материализованные представления с типом обновления COMPLETE, т.к. они используют блокировки строк только на момент своего обновления.

Асинхронные материализованные представления

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

Существует два метода обновления асинхронного материализованного представления:
• FAST – выполняет инкрементальное обновление представления, с момента последнего изменения.
• COMPLETE – выполняет полное обновление представление.
Для использования FAST обновления, необходимо создать журнал материализованного представления (materialized view log).

Пример создания асинхронного материализованного представления

Воспользуемся уже созданной таблицей my_all_source.

Command> delete my_all_source where OWNER='GENA';
1 row deleted.
Command> commit;
Command> drop materialized view my_all_source_aggr;
Command> CREATE MATERIALIZED VIEW my_all_source_aggr
       > REFRESH
       >   COMPLETE
       > AS select name, count(*) count from my_all_source group by name;
12 rows materialized.
Command>
Command> select * from my_all_source_aggr;
< DBMS_LOCK, 172172 >
< DBMS_OUTPUT, 158158 >
< DBMS_PREPROCESSOR, 226226 >
< DBMS_RANDOM, 87087 >
< DBMS_SQL, 1675674 >
< DBMS_STANDARD, 31031 >
< DBMS_UTILITY, 816816 >
< PLITBLM, 115115 >
< STANDARD, 3310307 >
< TT_DB_VERSION, 36036 >
< UTL_IDENT, 17017 >
< UTL_RAW, 927927 >
12 rows found.
Command>

В данном примере, я создал асинхронное материализованное представление с типом обновлением COMPLETE (полное обновление). В данном случае, обновление материализованного представления необходимо выполнять вручную с помощью фразы REFRESH.

Вставим данные в таблицу my_all_source и проверим результаты материализованного представления.

Command> insert into my_all_source (OWNER, NAME, TYPE,LINE,TEXT) values ('GENA', 'TEST', 'TEST', 1 ,'TEST');
1 row inserted.
Command> select * from my_all_source_aggr;
< DBMS_LOCK, 172172 >
< DBMS_OUTPUT, 158158 >
< DBMS_PREPROCESSOR, 226226 >
< DBMS_RANDOM, 87087 >
< DBMS_SQL, 1675674 >
< DBMS_STANDARD, 31031 >
< DBMS_UTILITY, 816816 >
< PLITBLM, 115115 >
< STANDARD, 3310307 >
< TT_DB_VERSION, 36036 >
< UTL_IDENT, 17017 >
< UTL_RAW, 927927 >
12 rows found.
Command>

Видно, что данные в представлении содержат устаревшую информацию. Обновим представление.

Command> refresh materialized view my_all_source_aggr;
Command> select * from my_all_source_aggr;
< TEST, 1 >
< DBMS_LOCK, 172172 >
< DBMS_OUTPUT, 158158 >
< DBMS_PREPROCESSOR, 226226 >
< DBMS_RANDOM, 87087 >
< DBMS_SQL, 1675674 >
< DBMS_STANDARD, 31031 >
< DBMS_UTILITY, 816816 >
< PLITBLM, 115115 >
< STANDARD, 3310307 >
< TT_DB_VERSION, 36036 >
< UTL_IDENT, 17017 >
< UTL_RAW, 927927 >
13 rows found.
Command>

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

Command> CREATE MATERIALIZED VIEW my_all_source_aggr
       > REFRESH
       >   COMPLETE
       > NEXT SYSDATE + NUMTODSINTERVAL(1, 'HOUR')
       > AS select name, count(*) count from my_all_source group by name;
13 rows materialized.
Command>

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

Также можно создать асинхронное материализованное представление обновляемое инкрементально (FAST).

Command> drop materialized view my_all_source_aggr;
Command> create materialized view log on my_all_source with rowid (name, owner);
Command> tables;
  ORATT.MVLGT$_718064
  ORATT.MVLOG$_718064
  ORATT.MY_ALL_SOURCE
3 tables found.
Command>
Command> desc my_all_source;

Table ORATT.MY_ALL_SOURCE:
  Columns:
    OWNER                           VARCHAR2 (30 BYTE) INLINE
    NAME                            VARCHAR2 (30 BYTE) INLINE
    TYPE                            VARCHAR2 (12 BYTE) INLINE NOT NULL
    LINE                            TT_INTEGER NOT NULL
    TEXT                            VARCHAR2 (4000 BYTE) NOT INLINE
  Has a materialized view log

1 table found.
(primary key columns are indicated with *)
Command> CREATE MATERIALIZED VIEW my_all_source_aggr
       > REFRESH
       >   FAST
       > NEXT SYSDATE + NUMTODSINTERVAL(1, 'HOUR')
       > AS select rowid id, name from my_all_source where owner='GENA';
1 row materialized.
Command>

Существенным минусом данного вида материализованных представлений являются ограничения SQL при создания представления (см. Ограничения  при создании материализованных представлений).

Также, в данном виде материализованных представлений можно спользовать третий вид обновления. Если при создании мат. представления не указывается NUMTODSINTERVAL после SYSDATE, это означает, что обновление будет происходить после каждой фиксации транзакции (commit) но в отдельной транзакции.
Пример:

Command> CREATE MATERIALIZED VIEW my_all_source_aggr
       > REFRESH
       >   FAST
       > NEXT SYSDATE
       > AS select rowid id, name from my_all_source where owner='GENA';
1 row materialized.
Command>

Ограничения при создании материализованных представлений

При создании материализованных представлений существуют следующие ограничения на SQL:
  • Все колонки в условии GROUP BY должны быть включены в SelectList.
  • SUM и COUNT могут использоваться,  но не в выражениях, включая AVG.
  • Следующие конструкции SELECT не могут быть использованы:
           – DISTINCT
           – FIRST
           – HAVING
           – ORDER BY
           – UNION
           – UNION ALL
           – MINUS
           – INTERSECT
           – JOIN
           – Функции: USER, CURRENT_USER, SESSION_USER
           – Подзапросы
           – NEXTVAL и CURRVAL
  • Каждое выражение в SelectList должно иметь уникальное имя.
  • Возможны самообъединение таблиц (Self joins).
Для синхронных мат. представлений или асинхронных мат. представлений, которые используют полное обновление (COMPLETE) , накладываются следующие ограничения в SELECT выражение:

  • Агрегированное представление должно содержать условие COUNT(*) в SelectList.
  • Можно делать внешнее объединение таблиц (OUTER JOINs)
Для асинхронных представлений, которые используют инкрементальное обновление (FAST) :
  • Агрегированные функции не поддерживаются.
  • Внешние объединения не поддерживаются.
  • SELECT должен включать ROWID или Первичный ключ для всех detail tables.

Заключение

Не смотря на все недостатки, материализованные представления в TimesTen являются достаточно мощным инструментом разработчиков, позволяющим решить различные задачи. Например, компания ОСМП (бренд Qiwi), реализовала систему обнаружения мошенничества, как раз, с использованием материализованных представлений в TimesTen.

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