17 окт. 2014 г.

Oracle 12c: grant roles to PL/SQL units

В Oracle Database 12c появилась интересная новая возможность: назначать роли непосредственно PL/SQL-объектам.
Для чего это нужно? Это дает возможность давать доступ к таблицам (вообще к любым объектам БД) только через вызовы PL/SQL-объектов созданных с правами вызывающего.
Предположим, у нас есть очень секретная таблица с остатками по счетам:
[oracle@rac1 ~]$  sqlplus rscott/rtiger

SQL*Plus: Release 12.1.0.2.0 Production on Sat Oct 18 05:57:08 2014

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Last Successful login time: Thu Oct 16 2014 08:26:35 +04:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options

SQL> CREATE TABLE acc_amounts
  (
    Acc_Id   NUMBER(9),
    Acc_Date DATE,
    Amount   NUMBER(22,2)
  );
Table created.

SQL> INSERT INTO acc_amounts VALUES(1,trunc(sysdate),100);

1 row created.

SQL> INSERT INTO acc_amounts VALUES(2,trunc(sysdate),200);

1 row created.

SQL> COMMIT;
И есть функция, которая возвращает остаток по счету на определенную дату. Для того, чтобы не давать делать вызов с потенциально более высокими правами создателя (definer right) - владельца объектов БД, функция создана с правами вызывающего (invoker rights):
SQL> CREATE OR REPLACE FUNCTION getAccAmount(v_pAccId IN NUMBER,
  2                                          v_pAccDate IN DATE) RETURN NUMBER
  3   AUTHID CURRENT_USER IS
  4        v_xRes acc_amounts.Amount%type;
  5      BEGIN
  6        SELECT
  7          Amount
  8        INTO
  9          v_xRes
 10        FROM
 11          rscott.acc_amounts
 12        WHERE
 13          Acc_Date = trunc(v_pAccDate);
 14
 15       RETURN v_xRes;
 16     END;
 17  /

Function created.

SQL>
Для того, чтобы прикладной пользователь мог вызывать эту функцию, необходимо дать ему права на чтение этой таблицы, иначе возникает ошибка:
 
SQL> GRANT EXECUTE ON getAccAmount TO user1;

Grant succeeded.

SQL> conn user1/oracle
Connected.

SQL> SELECT rscott.getAccAmount(1,sysdate) FROM dual;
select rscott.getAccAmount(1,sysdate) from dual
       *
ERROR at line 1:
ORA-00942: table or view does not exist
ORA-06512: at "RSCOTT.GETACCAMOUNT", line 6

SQL>
Казалось бы нет проблем: нужно дать права пользователю на чтение этой таблицы. После этого все будет работать:
 
SQL> conn rscott/rtiger
Connected.
SQL> GRANT SELECT ON acc_amounts TO user1;

Grant succeeded.

SQL> conn user1/oracle
Connected.
SQL> SELECT rscott.getAccAmount(1,sysdate) FROM dual;

RSCOTT.GETACCAMOUNT(1,SYSDATE)
------------------------------
                           100
SQL>
Но теперь пользователь имеет полный доступ к таблице, и может посмотреть остаток по любому счету!
 
SQL> SELECT * FROM rscott.acc_amounts;

    ACC_ID ACC_DATE      AMOUNT
---------- --------- ----------
         1 17-OCT-14        100
         2 17-OCT-14        200
SQL>
В Oracle Database 12c мы можем дать права на чтение таблицы непосредственно самой функции. Теперь пользователь не будет иметь непосредственного доступа к таблице:
 
SQL> conn rscott/rtiger
Connected.
SQL> REVOKE SELECT ON acc_amounts FROM user1;

Revoke succeeded.

SQL> GRANT SELECT ON acc_amounts TO FUNCTION getaccamount;
 GRANT SELECT ON acc_amounts TO FUNCTION getaccamount
*
ERROR at line 1:
ORA-28700: Only roles can be attached to or detached from program units.

REM Дать права PL/SQL-объекту можно только через роль!
SQL> CREATE ROLE getAccAmount_role;

Role created.

SQL> GRANT SELECT ON acc_amounts TO getAccAmount_role;

Grant succeeded.

SQL> GRANT getAccAmount_role TO FUNCTION getaccamount;

Grant succeeded.
SQL>
После этого пользователь успешно может вызывать функцию получения остатка, доступ к которому мы регламентируем через параметры (передавая в вызов функции на стороне приложения текущий идентификатор пользователя). При этом пользователь не имеет прямого доступа к таблице и вынужден использовать наш PL/SQL API:
 
SQL> conn user1/oracle
Connected.
SQL> SELECT * FROM rscott.acc_amounts;
SELECT * FROM rscott.acc_amounts
                     *
ERROR at line 1:
ORA-00942: table or view does not exist


SQL> SELECT rscott.getAccAmount(1,sysdate) FROM dual;

RSCOTT.GETACCAMOUNT(1,SYSDATE)
------------------------------
                           100
SQL>
Выдача прав на объекты непосредственно PL/SQL-объектам позволяет повысить защищенность вашего приложения от несанкционированного доступа к конфиденциальным данным. Прекрасно сочетаясь с другой возможностью Oracle 12c - ограничением места вызова PL/SQL-объектов, эта технология позволяет четко регламентировать доступ к данным через прикладной программный интерфейс вашего приложения!

5 окт. 2014 г.

Oracle ILM 12 and custom business rules

В Oracle Database 12c появилась новая и очень мощная технология автоматизации жизненного цикла информации - Automatic Data Optimization. С помощью политик ILM (атрибуты сегмента, задаваемые через команду ALTER TABLE ADD ILM POLICY), мы устанавливаем политики для сжатия и перемещения данных между разными уровнями системы хранения, например: c SSD-дисков на SAS-накопители, и далее на SATA-диски.

Вообще, базовые технологии для реализации такого автоматического ILM, уже давно были в СУБД Oracle еще до версии 12с: Partitioning, сжатие Advanced Compression и Hybrid Columnar Compression. Не было самого механизма отслеживания и автоматического выполнения политик ILM - приходилось их реализовывать в коде приложения, и в виде собственных скриптов и заданий (job-ов СУБД).
Наконец, в Oracle Database 12c появилась технология Automatic Data Optimization.

Уже пара заказчиков задали мне вопрос: а чем, собственно, это отличается от того, что уже давно есть внутри систем хранения?

Действительно: подобная функциональность уже давно есть в многих массивах. Система хранения, отслеживая статистику обращения к данным, может производить автоматическое их перемещение между разными типами дисковых полок.
Например, в массивах EMC Symmetrix V-Max есть технология Virtual LUN. Отдельного обсуждения заслуживает тема негибкости и сложности такой реализации ILM: поскольку массив ничего не "знает" про таблицы БД, миграция данных происходит на основе виртуальных разделов, - на каждый такой раздел нужно создавать свою ASM-группу.

Рис 1. Перещение виртуальных разделов на массивах EMC Symmetrix V-Max

Есть два важных отличия технологии Automatic Data Optimization от реализации ILM на системе хранения.
Первое: подобная функциональность есть как правило только в СХД Hi-End класса, и стоит отдельных, довольно значительных денег.
Второе, и самое важное отличие: в случае автоматизации ILM средствами системы хранения, отсутствует связь с бизнес-правилами, что может привести к ложному срабатыванию политик на СХД.

Рассмотрим следующий простой случай.
Мы определили на уровне системы хранения, что для таблицы с заказами, если к данным не было обращения на запись более 5 дней (то есть заказ оплачен, доставлен и закрыт), то к нему не нужен быстрый доступ - нужно переносить их с SSD-полки на другой уровень системы хранения на основе SATA-дисков.
Все замечательно будет работать до тех пор, пока не появятся заказы, по которым на срок более 5-ти дней была задержана оплата или поставка. Система хранения честно переместит эти данные на SATA-диски. Когда же потребуется произвести изменение заказов при поступлении оплаты, мы получим большое время доступа, поскольку данные уже лежат на медленных дисках!

В случае использовании технологии Automatic Data Optimization мы можем задать не просто временные рамки, но собственную логику контроля перемещения данных, которая основывается на бизнес-логике приложения. Подобное, никакая система хранения, конечно сделать не сможет.

Рассмотрим пример.
Пусть у нас есть таблица заказов, секционированная по годам:
[oracle@localhost Desktop]$ sqlplus rscott/rtiger

SQL*Plus: Release 12.1.0.2.0 Production on Sun Oct 5 21:45:32 2014

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> create table orders
(
  Id          number(9) generated as identity,
  Customer_id number(9),
  pay_date    date,
  amount      number,
  status      varchar2(15) invisible default on null 'ACTIVE' 
)
partition by range (pay_date)
 (
  partition year_old     values less than (to_date('01.01.2001','dd.mm.yyyy')) nocompress,
  partition year_2001    values less than (to_date('01.01.2002','dd.mm.yyyy')) nocompress,
  partition year_2002    values less than (to_date('01.01.2003','dd.mm.yyyy')) nocompress,
  partition year_current values less than (maxvalue)                           nocompress
 )
tablespace rscott_data;
 
Table created.

Заполним секцию за 2001 год - вставим 1млн записей:
SQL> begin
  for i in 1..1000000
  loop
    insert into orders(Customer_id,pay_date,amount)
    values(1,to_date('02.01.2001','dd.mm.yyyy'),100);
  end loop;

  commit;
end;
/

PL/SQL procedure successfully completed.
Секция за 2001 год занимает 40 мегабайт и расположена в табличном пространстве RSCOTT_DATA, которое находится на SAS-дисках:
SQL> SELECT bytes,tablespace_name FROM user_segments WHERE PARTITION_NAME ='YEAR_2001';

     BYTES TABLESPACE_NAME
---------- ------------------------------
  41943040 RSCOTT_DATA
Мы хотим определить правило, согласно которому, если в секции все заказы проплачены (поле status = 'PAID'): включать для нее сжатие на уровне строк (OLTP compression). Поскольку мы знаем, что после этого данные в заказе не будут часто меняться.
После того как все заказы в секции будут закрыты (поле status = 'CLOSED'): переносить секцию в другое табличное пространства RSCOTT_ARCHIVE, расположенное на дисковых полках с SATA-дисками.

Функция, которую мы будем использовать для проверки ILM-политики, должна иметь строго определенную сигнатуру: входной параметр object_number-номер объекта для которого нужно проверить правило, возвращаемое значение - типа boolean.

Определим две вспомогательные функции:
SQL> /**
 * Функция возвращает TRUE, если ВСЕ заказы в секции находятся в определенном состоянии 
 *
 * @param v_pPartitionName - название секции
 * @param v_pStatus        - состояние заказа
 * @return TRUE, если ВСЕ заказы в секции находятся в  состоянии определенном в переменной v_pStatus
 */
create or replace function getOrdersStatusInPart(v_pPartitionName in varchar2,
                                                 v_pStatus        in varchar2) return boolean is
  v_xCount         number(9);
begin
  execute immediate
    'select count(rowid) from orders partition (' || v_pPartitionName || ') where status != :v_pStatus'
  into v_xCount
  using
    in v_pStatus;

  return (v_xCount=0);
end;
/

Function created.

SQL> /**
 * Функция возвращает название секции таблицы orders по Object_Id этой секции
 *
 * @param v_pPartitionObjectId - Object Id секции
 * @return название секции
 */
create or replace function getPartitionNameById(v_pPartitionObjectId in number) return varchar2 is
  v_xResult user_tab_partitions.partition_name%type;
begin
  select
    subobject_name
  into
    v_xResult
  from
    user_objects
  where 
    object_id = v_pPartitionObjectId;

  return v_xResult;
end;
/
Function created.
Определяем функцию для проверки ILM-политики, которая возвращает TRUE, если все заказы в секции проплачены:
SQL> create or replace function orders_is_paid(object_number in number) return boolean is
begin
  return getOrdersStatusInPart(getPartitionNameById(object_number),'PAID');
end;
/

Function created.
И, наконец, определим функцию для проверки ILM-политики, которая возвращает TRUE, если все заказы в секции закрыты:
SQL> create or replace function orders_is_closed(object_number in number) return boolean is
begin
  return getOrdersStatusInPart(getPartitionNameById(object_number),'CLOSED');
end;
/

Function created.
Определяем ILM-политику для сжатия строк, когда все заказы в секции оплачены. За проверку срабатывания ILM-правила отвечает наша функция orders_is_paid:
SQL> ALTER TABLE orders ILM ADD POLICY
  ROW STORE COMPRESS ADVANCED 
  SEGMENT
  ON orders_is_paid;

Table altered.
Имитируем ситуацию оплаты всех заказов в секции за 2001 год:
SQL>update orders partition(year_2001) set status='PAID';

1000000 rows updated.

SQL> commit;

Commit complete.
Автоматическое задание для выполнения ILM-политик выполняется в окне сопровождения (MAINTANCE_WINDOW).
Для проверки, вызовем его вручную:
SQL> declare
  v_xExecId number;
begin
  dbms_ilm.execute_ilm (ilm_scope      => dbms_ilm.scope_schema,
                        execution_mode => dbms_ilm.ilm_execution_offline,
                        task_id        => v_xExecId);
end;
/

PL/SQL procedure successfully completed.
Проверим атрибут сжатия у секций таблицы orders:
SQL> select compression,compress_for,partition_name,tablespace_name from user_tab_partitions where TABLE_NAME ='ORDERS';

COMPRESS COMPRESS_FOR PARTITION_NAME   TABLESPACE_NAME
-------- ------------ --------------   ---------------
DISABLED              YEAR_OLD         RSCOTT_DATA
ENABLED  ADVANCED     YEAR_2001        RSCOTT_DATA
DISABLED              YEAR_2002        RSCOTT_DATA
DISABLED              YEAR_CURRENT     RSCOTT_DATA
Как видите, наша ILM-политика отработала и для секции заказов за 2001 год включилось OLTP-сжатие! Определяем ILM-политику для переноса секции в табличное пространство расположенное RSCOTT_ARCHIVE на медленном носителе (SATA-диски), если все заказы в секции закрыты. За проверку срабатывания ILM-правила отвечает наша функция orders_is_closed:
SQL> ALTER TABLE orders ILM ADD POLICY 
  TIER TO rscott_archive ON orders_is_closed;

Table altered.
Имитируем ситуацию закрытия всех заказов в секции за 2001 год:
SQL> update orders partition(year_2001) set status='CLOSED';

1000000 rows updated.

SQL> commit;

Commit complete.
Для проверки, снова запускаем задание по выполнению ILM-политик вручную:
SQL> declare
  v_xExecId number;
begin
  dbms_ilm.execute_ilm (ilm_scope      => dbms_ilm.scope_schema,
                        execution_mode => dbms_ilm.ilm_execution_offline,
                        task_id        => v_xExecId);
end;
Выясним размер секции за 2001 год и табличное пространство в котором оно расположено:
SQL> SELECT bytes,tablespace_name FROM user_segments WHERE PARTITION_NAME ='YEAR_2001';

     BYTES TABLESPACE_NAME
---------- ------------------------------
  16777216 RSCOTT_ARCHIVE
Размер секции уменьшился в два с половиной раза (c 40 мегабайт до 16), и теперь она расположена в табличном пространстве RSCOTT_ARCHIVE!

Технология Automatic Data Optimization, которая появилась в Oracle Database 12c, позволяет не просто автоматизировать политики ILM, а интегрировать правила их выполнения c бизнес-логикой приложения.
Это выгодно отличает эту технологию от реализации ILM-политик на уровне системы хранения.

2 окт. 2014 г.

Как Ларри Элиссон искал Путина. Записки in-memory скептика

На OOW очень много говорят об опции database in-memory option. Игорь про нее рассказывал. Она ускоряет работу аналитических приложений без их переписывания. Ускорение достигается за счет многих механизмов - одновременное построчное и поколоночное хранение табличек в памяти, использование векторных команд процессора, новые алгоритмы построения агрегирующих отчетов, прунинг (storage index), преобразование join в набор операций фильтрации колонок, bloom filters и т д.
На всех выступлениях говорят про ускорение аналитических запросов в 100 раз, а потом показывают слайды, где приложения ускоряются в 1000, 2000, 3000 раз. Как такое может быть? Конечно механизмы понятные и работу Select ускоряют, ну в 2-3 раза, ну в 5-10 раз, но как может быть тысяча, непонятно. Я даже подумал, что это достигается за счет неявного result cache - сначала запрос считался час, а потом результат взяли за секунду - вот и тысячи. Но оказалось все не так

Прослушал несколько докладов не от Oracle, ребята детально потестировали опцию в разных режимах и вот что получилось

Взяли таблицы с миллионами записей и написали простой запрос - соединение, условие на выбор диапазона значений и сумма по колонкам
Тестировали на Exadata. Вначале честно отключили все механизмы ячеек (offload) и In-memory. Были только обычные индексы. Запрос работал 4,5 мин. Потом заставили делать чтение вперед и full buffering, стало работать быстрее, но все равно основное время шло на ввод-вывод
Потом включили механизмы Exadata - стал работать 2 секунды
А потом включили еще и In-memory - все выполняется за 0,1 сек. Т е с 4.5 мин до 0.1 сек и без трюков и фокусов (хотя in-memory cache конечно разогрели) Результаты впечатляют

Теперь про Путина. Ларри демонстрировал как быстро работает запрос с In-memory. Закачали в БД кучу данных об упоминании разных людей в прессе. Миллионы записей. И сделали поисковую системку. Вводишь имя и на экране появляется график кол упоминаний в час для каждого дня года.
На экране было 2 области - в верхней показывали результат, полученный с in-memory, а в нижней - тот же запрос работал без нее. Результат поиска всегда был одинаков. В верхнем окне график появлялся мгновенно, в нижнем медленно и лениво полз от даты к дате.
Вначале Ларри поискал инфо по упоминанию Федерера. In-memory отработал мгновенно, потом поискал еще кого-то, результат тот-же. А потом сказал, для тех кто не верит давайте поищем инфо еще о ком-то. И тут из зала ему сказали Путин. Ларри набрал в поисковой строке putin и результат с in-memory опять появился мгновенно, а на нижнем графике он полз, полз, и в конце концов начали искать еще кого-то

Т е In-memory option действительно работает и действительно драматически ускоряет работу существующих аналитических приложений