Секционирование и сжатие в хранилищах данных Oracle. Профессионалу-разработчику


Екатерина Кондратьева
ekondratieva@bk.ru

СОДЕРЖАНИЕ


Введение Сегодня на многих предприятиях идет активное внедрение информационно-аналитических хранилищ данных. В результате обозначались и сформировались подходы и принципы, позволяющие оптимизировать построение систем подобного рода на платформе Oracle Database. Один из таких подходов и будет рассмотрен в статье.

Важной характеристикой хранилища данных является использование такого специализированного измерения как "Время". В абсолюном большинсве случаев значения показателей равномерно распределяются по этому измерению.

Учитывая вышеописанное свойство хранилища данных, в качестве метода для поддержки больших объмов данных информации, чаще всего применяют опцию Partitioning СУБД Oracle. При этом происходит разбиение (секционирование) больших таблиц на относительно небольшие разделы (секции). Каждая секция может храниться в отдельном табличном пространстве, что позволяет повысить управляемость, доступность и производительность системы.

Описание проблемы

К основным проблемным местам хранилища относят следующие:

  • Постоянно растущий объем данных по мере эксплуатации системы, и как следствие, высокие требования к объему дискового пространства;
  • Трудно прогнозируемое время выполнения запросов, и длительное время их выполнения, как следствие, большая нагрузка на дисковую подсистему.


Предлагаемое решение

Для "борьбы" со вышеперечисленными недостатками хранилищ данных предлагается разбивать факторные таблицы на секции и периодически сжимать архивные секции. Необходимо отметить, что в хранилищах данных основной объем информации приходится именно на факторные таблицы.

Далее будет рассмотрен законченный пример с помощью которого можно будет реально оценить эффективность предлагаемого подхода.

На схеме приведена структура факторной таблицы sample, которая содержит данные по продажам.


Рис.1 Структура факторной таблицы "Sample"

Данные по продажам формируются по четырем аналитикам: – "Время", "Продукт", "Клиент", "Отдел продаж". Минимальный уровень по измерению "Время" – год. Данные факторной таблицы можно разбить на логические диапазоны по годам. Т.е. в каждой секции таблицы sample будут содержаться данные за определенный год. Наибольшая производительность при работе с секционированными таблицами достигается при равномерном распределении данных по секциям. На схеме представлена таблица sample, секционированная по годам. Каждый раздел хранится в отдельном табличном пространстве. Секция year_current является загрузочной, то есть в текущий период времени (на рисунке - за 2007 год) данные из информационных источников поступают только в эту секцию; в остальных секциях хранятся архивные данные.


Рис.2 Секционирование факторной таблицы "Sample"

Перед созданием таблицы необходимо сформировать табличные пространства для ее секций.

Пример:
  create tablespace wh_fact_2001_data datafile 'с:oracleoradatadwawwh_fact_2001_data01.dbf'
  size 50M autoextend on next 10M maxsize 2048M
  extent management local
  segment space management auto;
  ... ... ...

В прилагаемом файле приведен полный cкрипт по созданию табличных пространств.

Далее создаем факторную таблицу sample.

Пример:
 create table  sample
 (
  time_id       number(9),
  product_id    number(9),
  customer_id   number(9),
  department_id number(9),
  sale          number 
 )
  nologging
  partition by range(time_id)
  (
   partition year_2001values less than   (-3995) nocompress tablespace wh_fact_2001_data,
  ... ... ...

В целях эксперимента, индексы на столбцы таблицы отсутствуют.
Законченный скрипт создания факторной таблицы приведен в файле.

Следующим шагом заполняем факторную таблицу sample случайными значениями.
Приведенный ниже код служит лишь для заполнения данных в таблицу Sales; в реальной производственной системе загрузка данных будет происходить с помощью специализированных ETL-процедур.

Код для загрузки данных в факторную таблицу:
  ... ... ...
   for v_xTimeId in 1..1005
   loop
     for v_xVersionId in 1..10
     loop
       for v_xProductId in 1..10
       loop
         for v_xCustomerId in 1..10
         loop
           for v_xDepartmentId in 1..20
           loop
            v_xCount := v_xCount + 1;
            v_xFactValue := to_number(to_char(sysdate,'ss'));

            v_xTimeIds(v_xCount)       := v_xTimeId;            
            v_xProductIds(v_xCount)    := v_xProductId;      
            v_xCustomerIds(v_xCount)   := v_xCustomerId;    
            v_xDepartmentIds(v_xCount) := v_xDepartmentId;
            v_xFactValues(v_xCount)    := v_xFactValue;

             if v_xCount = v_xCountLimit  then
              v_xCount := 0;

               forall v_xIndex in 1..v_xCountLimit
                 insert into sample  values(v_xTimeIds(v_xIndex),
                                           v_xProductIds(v_xIndex),
                                           v_xCustomerIds(v_xIndex),
                                           v_xDepartmentIds(v_xIndex),
                                           v_xFactValues(v_xIndex));
             end if;
           end loop;
         end loop;
       end loop;
     end loop;
  end loop;
  ... ... ...

Законченный скрипт приведен в файле по следующей ссылке.

Далее проверяем: какое количество записей содержится в секции year_current.

  select count(*) from sample partition (year_current);

Итак, сейчас в таблице samples содержится 20 миллионов записей.

После вставки записей собираем статистику для таблицы sample. Для этого необходимо вызвать процедуру GATHER_TABLE_STATS из пакета DBMS_STATS.

Код для сбора статистики:
begin
   DBMS_STATS.GATHER_TABLE_STATS (ownname => 'DEMO', tabname => 'sample');
end;
/

Скрипт сбора статистики приведен в файле по следующей ссылке.

Повторно запускаем sql-запрос на подсчет количества записей в секции year_current и sql-запрос на поиск записи с кодом клиента для полного сканирования всей таблицы и анализируем время выполнения этих запросов.

select count(*) from sample partition (year_current);

select * from sample partition (year_2007) where customer_id = 999;

Скрипты представлены в файле.

Время выполнения запроса А – 7,77 секунд (время сканирования секции year_current), время выполнения запроса B – 21,20 секунд (время полного сканирования таблицы).

Следующим шагом, с помощью оператора split partition секцию year_current разбиваем на две – на секцию year_2007, куда относятся значения показателя для 2007 года, и на секцию year_current, готовую для следующей порции загружаемых данных. Предварительно создаем табличное пространство для данных 2007 года.

create tablespace wh_fact_2007_data
  datafile 'c:oracleoradatadwawwh_fact_2007_data01.dbf'
       size 50M autoextend on next 10M maxsize 2048M
       extent management local
       segment space management auto;

Скрипт по созданию табличного пространства приведен в файле.

Расщепляем партицию year_current на две, использую диапазон значений поля time_id, принадлежащих 2007 году.

alter table sample split partition year_current at (1005)
into  
    (
      partition year_2007 tablespace wh_fact_2007_data, 
      partition year_current
    );

Скрипт приведен в файле.

Следующим шагом иследования будет сжатие (compress) вновь полуенной архивной секции ( в нашем случае - year_2007) и выяснение: насколько это повысит быстродействие системы. Выясняем: сколько места на диске занимает секция year_2007, - для этого используем представление dba_segments.

select * from dba_segments where owner  = 'DEMO';

Размер секции year_2007 составляет 464 Мб.

Далее, с помощью оператора, move partition производим сжатие секции year_2007.

alter table sample move partition year_2007 tablespace wh_fact_2007_data compress;

Повторно выясняем размер секции year_2007. Теперь, после сжатия, размер секции year_2007 составляет 240 МБ. Таким образом, коэффициент сжатия равен 1,93.

После того, как поступит уведомление о том, что данные 2007 года больше модифицироваться не будут (т.е. этот период в учетных системах закрыт), для ускорения времени выполнения запросов на чтение, и контроля на изменение данных, переведем табличное пространство wh_fact_2007_data в режим "только для чтения" (read only).

alter tablespace wh_fact_2007_data read only;

Проверяем как изменилось время выполнения тестовых запросов (файл).

Время выполнения запроса А – 3,11 секунд, время выполнения запроса B – 9,06 секунд.

Время выполнения запросов существенно сократилось - примерно в 2,5 раза !

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

alter database datafile 'C:oracleoradatadwawwh_fact_current_data01.dbf' resize 245M;
Заключение

Были получены следующие положительные результаты:

  • Достаточно большой коэффициент сжатия секции, что позволяетт уменьшить размер табличного пространства;
  • Увеличение скорости выполнения запросов;
  • Контроль на DML-операции после завершения формирования загрузки.

Законченные скрипты, использованные в статье, можно загрузить по следующей ссылке.

.



Опубликовал admin
8 Июл, Воскресенье 2007г.
По информации: http://www.furniterra.ru/members/337/.


Программирование для чайников.