In-Memory@Oracle

Part 1 Introduction: http://exadata-dba.blogspot.com/2021/11/in-memoryoracle.html
Part 2 IM Scan:   http://exadata-dba.blogspot.com/2021/11/in-memoryoracle_22.html
Part 3 IM Join:    http://exadata-dba.blogspot.com/2021/11/in-memoryoracle-part-3-im-join.html
Part 4: IM Aggregation http://exadata-dba.blogspot.com/2021/11/in-memporyoracle-part-4-im-aggregation.html

1.   ВВЕДЕНИЕ

Oracle Database In-Memory — это технология, поколоночного представления данных, которая появилась в СУБД Oracle начиная с версии 12.1 (в 2013 году). Рядом с буферным кэшем, который хранит строки таблиц и блоки индексов, в оперативной памяти (в SGA) появилась новая область для поколоночного хранения данных. Строковый формат данных при этом сохраняется и работает также как и в предыдущих версиях.

 Oracle Database In-Memory на порядки уменьшает время выполнения аналитических запросов, которые выполняют сканирование больших объемов данных, соединение таблиц и агрегацию данных. Oracle Database In-Memory позволяет отказаться либо значительно сократить количество индексов и материализованных представлений в приложении. С помощью Oracle Database In-Memory пользователи получают результаты бизнес-запросов за несколько секунд, если раньше это занимало несколько часов.

 

Строковый формат обеспечивает высокую производительность для транзакционных нагрузок. Поколоночние хранение обеспечивает высокую производительность для аналитических нагрузок. СУБД Oracle автоматически обеспечивает синхронизацию между этими способами представления данных. Оптимизатор СУБД Oracle автоматически направляет аналитические запросы к данным в колоночном формате, а запросы OLTP — к данным в строчном формате, автоматически обеспечивая наилучшую производительность SQL-запросов:

 Говоря о длительно выполняющихся запросах можно выделить ТРИ ГЛАВНЫХ ОБЛАСТИ где обычно тратится наибольший объем времени и ресурсов. Это:

•        Доступ к данным, т.е. сканирование большого количества строк и блоков базы данных, большое количество логических и физических чтений

•        Это соединения таблиц, JOIN – IM-представление позволяет оптимизировать скорость выполнения соединений

•        Это агрегация данных, т.е. вычисление значений sum,min,max,avg с группировой, например по часам, по месяцам, по годам и т.д.

В цикле статей, посвящённых этой опции будет показано как In-Memory опция СУБД Oracle позволяет ускорить выполение каждой из этих операций:

• ·        В статье IM Table Scan рассмотрим как IM влияет на доступ к данным.
• ·        В статье IM Join рассмотрим как приналичии IM выполняется соединение таблиц.
• ·        В статье IM Aggregation продемонстрируем что такое IM aggregation (vector group by).

  2.   Подготовка данных и настройка инстанса БД

Установите СУБД Oracle версии 19с и накатите самый свежий квартальный пачсет. Минимальный объем датафайлов около 10 ГБ, минимальный объем SGA около 6ГБ. Установите следующие параметры инстанса:
*.db_cache_size=3600m

*.inmemory_size=2048m

*.shared_pool_size=768m

В нашем случае для генерации данных используется Swingbench. Вы можете взять свои таблицы и выполнять SQL-запросы над своими таблицами. Помните, что в IM не загружаются таблицы размером 64К и менее.

Создайте в БД табличное пространство и пользователя SOE:

create tablespace SOE extent management local segment space management auto autoallocate datafile '/data/IM/SOE01.dbf' size 1g autoextend on next 16m;
create user soe identified by soe default tablespace soe;

grant dba to SOE;

grant execute on DBMS_LOCK to SOE;

Схема SOE с коэффициентом 1 - это чуть менее 3ГБ датафайлов. Используйте коэффициент масштабирования 3, чтобы получить достаточный объем. В моем случае при коэффициенте 3 получилось 6,2 ГБ сегментов ( select round(sum(bytes)/power(2,30),1) from dba_segments where owner='SOE'; ).

 

 Для генерации данных в пакете Swingbench найдите команду oewizard. Эту программу можно запустить как в графическом режиме, так и в командной строке (измените соответствующие параметры при необходимости): 
$ ./oewizard -create -scale 3 -cs //localhost/IM -dba system -dbap welcome1 -tc 2 -nopart -u soe -p soe -cl -ts SOE -df /data/IM/SOE01.dbf

 

Расшифровка параметров:

-scale 3  - коэффициент масштабирования

-cs   - connect string

-dba  – пользователь с правами DBA

-dbap – пароль пользователя с правами DBA

-tc   – threads count, количество потоков

-part/nopart – создавать партиции или не создавать

-u  – user, название схемы в которой создавать таблицы

-p  – пароль пользователя/схемы с таблицами

-cl - command line, работать в режиме командной строки

-ts – tablespace, табличное пространство для схемы –u user

-df – datafile, датафайл для схемы с таблицами

В результате должна получиться схема SOE с объёмами как в таблице :

- таблица ORDER_ITEMS около 1472 МБ ≈ 22 млн. строк
- таблица ORDERS около 512 МБ ≈ 4,6 млн. строк

select segment_type, segment_name, round(sum(bytes)/power(2,20))  MB
from dba_segments
where owner='SOE'
group by rollup(segment_type,segment_name)
order by sum(bytes) desc; 

Чтобы уменьшить влияние В/В на время выполнения SQL-запросов включим кеширование для таблиц:

alter table soe.order_items CACHE;

alter table soe.orders CACHE;

alter table soe.PRODUCT_INFORMATION CACHE;

Соберём статистику по схеме SOE в максимальном объёме, чтобы оптимизатор мог строить самые лучшие планы для всех SQL-запросов :

exec DBMS_STATS.GATHER_TABLE_STATS ('SOE','ORDER_ITEMS', estimate_percent=>100,method_opt=>'for all columns size 2048');

exec DBMS_STATS.GATHER_TABLE_STATS ('SOE','ORDERS', estimate_percent=>100,method_opt=>'for all columns size 2048');

exec DBMS_STATS.GATHER_TABLE_STATS ('SOE','PRODUCT_INFORMATION', estimate_percent=>100,method_opt=>'for all columns size 2048');

 

3.   3.Настройка IM-атрибутов таблиц

Демо-запросы выполняются над двумя самыми большими таблицами, поэтому включим IM для всех столбцов этих таблиц, чтобы иметь возможность запускать разнообразные запросы :

alter table ORDER_ITEMS INMEMORY memcompress FOR QUERY HIGH PRIORITY CRITICAL;

alter table ORDERS      INMEMORY memcompress FOR QUERY HIGH PRIORITY CRITICAL;

 В промышленных системах для экономии оперативной памяти рекомендуется включить IM на отдельные столбцы (нет смысла включать IM на столбцы, которые никогда не участвуют в запросах, например столбец ”Примечания”). Также для экономии памяти в промышленных системах рекомендуется включать IM для исторических партций. В нашем случае для демонстрациии возможностей IM, чтобы иметь возможность запускать разнообразные SQL-запросы мы включаем IM на все столбцы.

Отменить IM (для информации, не выполнять):

alter table soe.ORDER_ITEMS NO INMEMORY;

alter table soe.ORDERS      NO INMEMORY;

Проверим, что атрибуты таблиц действительно применились:

select table_name, num_rows, cache,
       inmemory, inmemory_compression, inmemory_priority
from   dba_tables
where  owner='SOE'
order by num_rows desc ;

 

Представление V$IM_SEGMENTS показывает статус процесса трансформации из row store в column store . Дождитесь POPULATE_STATUS = “COMPLETE” и оцените объем данных в IM: 

select segment_name, populate_status,
      sum(round(bytes/1048576)) DISK_MB, sum(round(inmemory_size/1048576)) IM_MB
from v$im_segments
group by rollup((segment_name,populate_status));

Представление V$INMEMORY_AREA тоже показывает статус и объемы даннных в IM. Область In-Memory area состоит из двух под-областей: первый пул размером по 1MB предназначен для хранения IM-данных, а пул размером 64K предназначен для хранения метаданных об объектах находящихся в 1M-IM пуле:

select pool, sum(round(used_bytes/1048576))USED_MB, populate_status from v$inmemory_area group by rollup ((pool,populate_status));

Какие столбцы загружены в IM пул:

select distinct o.object_name,
                column_number,
                column_name 
from  v$IM_COL_CU i,
      dba_objects o,
      dba_tab_columns c
where i.objd=o.data_object_id
  and o.object_name=c.table_name
  and i.column_number=c.column_id
  and o.owner='SOE'
  and o.object_name like 'ORDER%'
order by o.object_name, column_number;

 

4. Сравним объемы IM c объемом индексов

Итак, на предыдущем этапе мы включили IM на все столбцы таблицы ORDER_ITEMS и поместили её в IM pool.

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

Некоторые столбцы таблицы ORDER_ITEMS необходимые для выполнения OLTP-запросов уже были проиндексированы самим Swingbench (выделены жирным). Поэтому индексированные столбцы второй раз индексировать не будем, а создадим индексы только на оставшиеся столбцы:

ORDER_ID
LINE_ITEM_ID
PRODUCT_ID

create index OI_UNIT_PRICE on order_items (UNIT_PRICE);

create index OI_QUANTITY   on order_items (QUANTITY);
create index OI_DISP_DAT   on order_items (DISPATCH_DATE);
create index OI_RET_DAT    on order_items (RETURN_DATE);
create index OI_GIFT_WRAP  on order_items (GIFT_WRAP);
create index OI_CONDITION  on order_items (CONDITION);
create index OI_SUPP_ID    on order_items (SUPPLIER_ID);
create index OI_EST_DELVR  on order_items (ESTIMATED_DELIVERY);

 Посчитаем объем:

select  sum(bytes)/power(2,20) MB from dba_segments where owner='SOE' and segment_name in ('ITEM_ORDER_IX', 'ORDER_ITEMS_PK', 'ITEM_PRODUCT_IX', 'OI_UNIT_PRICE', 'OI_QUANTITY', 'OI_DISP_DAT', 'OI_RET_DAT', 'OI_GIFT_WRAP', 'OI_CONDITION', 'OI_SUPP_ID', 'OI_EST_DELVR');

 

Только что построенные индексы являются новыми и плотными, т.е. занимают минимально возможный объем. В промышленных системах индексы не бывают такими плотными, как вновь построенные. Т.е. в промышленных системах индексы будут занимать на 25-30% больший объем на диске и в оперативной памяти. 

Эти же аргументы применимы и к таблицам: поскольку схема Swingbench создана с нуля и ещё не подвергалась значительным DML, то таблицы на данный момент также очень плотные и имеют минимальный объем. В промышленных системах таблицы также будут более разреженными, т.е. будут занимать больше дискового пространства.

Объем данных в IM:
select segment_name, inmemory_compression, populate_status, round(bytes/1048576) DISK_MB, round(inmemory_size/1048576) IM_MB from v$im_segments;

select round(sum(row_len)/power(2,20)) SOURCE_ROW_LENGTH, round(sum(transformed_len)/power(2,20)) IM_LENGTH, round(sum(row_len)/sum(transformed_len),2) COMPRESS_RATIO
from v$IM_COL_CU;

Результаты сведём в таблицу:

Объем таблицы	Объем индексов	Объем IM	Отношение объема индексов к объему IM	Отношение объёма таблицы к объёму IM
1476	4369	273	16 раз	5,4 раз

 

Сравнить объем данных в таблице (сумма длин строк) и IM можно запросом:

select sum(row_len) ROW_LENGTH,
       sum(transformed_len) IM_LENGTH,
       round(sum(row_len)/sum(transformed_len),2) COMPRESS_RATIO
from v$IM_COL_CU;

 

Удалим лишние индексы:

drop index OI_UNIT_PRICE;
drop index OI_QUANTITY  ;

drop index OI_DISP_DAT  ;
drop index OI_RET_DAT   ;
drop index OI_GIFT_WRAP ;
drop index OI_CONDITION ;
drop index OI_SUPP_ID   ;
drop index OI_EST_DELVR ;