Синхронные материализованные представления
Синхронные материализованные представления существовали еще в версии 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 не могут быть использованы:
– FIRST
– HAVING
– ORDER BY
– UNION
– UNION ALL
– MINUS
– INTERSECT
– JOIN
– Функции: USER, CURRENT_USER, SESSION_USER
– Подзапросы
– NEXTVAL и CURRVAL
- Каждое выражение в SelectList должно иметь уникальное имя.
- Возможны самообъединение таблиц (Self joins).
- Агрегированное представление должно содержать условие COUNT(*) в SelectList.
- Можно делать внешнее объединение таблиц (OUTER JOINs)
- Агрегированные функции не поддерживаются.
- Внешние объединения не поддерживаются.
- SELECT должен включать ROWID или Первичный ключ для всех detail tables.
Заключение
Не смотря на все недостатки, материализованные представления в TimesTen являются достаточно мощным инструментом разработчиков, позволяющим решить различные задачи. Например, компания ОСМП (бренд Qiwi), реализовала систему обнаружения мошенничества, как раз, с использованием материализованных представлений в TimesTen.
Комментариев нет:
Отправить комментарий