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-политик на уровне системы хранения.