Pull to refresh

Секционирование. Автоматическое добавление секций

Reading time 15 min
Views 32K
В версии 11g в Oracle появилась несколько новых замечательных схем секционирования — например, удобная функциональность интервального секционирования — автоматического создания секций по мере выхода range из заданных границ.
В версиях до 11g необходимо периодически вручную либо заранее добавлять секции, либо разбивать секцию по умолчанию. То есть постоянно необходимо отслеживать состояние таких таблиц. В данной статье я поделюсь своими решениями для автоматизации таких задач секционирования.
Сначала приведу пример для 11g:
  1. create table res (
  2.   res_id     number not null,
  3.   res_date  date,
  4.   hotel_id  number(3),
  5.   guest_id  number
  6. )
  7. partition by range (res_id)
  8. interval (100) store in (users)
  9. (
  10.   partition p1 values less than (101)
  11. );

Этот скрипт создает секцию p1 для записей, значение столбца res_id которых находится в диапазоне 1-100. Когда вставляются записи со значением столбца res_id меньшим 101, они помещаются в секцию p1, а когда в новой записи значение этого столбца равно или больше 101, сервер Oracle Database 11g создает новую секцию, имя которой генерируется системой. Подробнее с этим примером и прочими новыми схемами секционирования вы можете познакомиться в переводе статьи Арупа Нанды в русском издании Oracle Magazine.
Рассмотренные ниже решения можно применить и в других СУБД, не поддерживающих автоматическое добавление секций

Решение для равномерно увеличивающегося ключа секционирования без пропусков


Создадим тестовую таблицу:
  1. create table test_part(
  2. id number not null,
  3. name varchar2(100) not null,
  4. owner varchar2(100) not null,
  5. type varchar2(100) not null,
  6. created date not null,
  7. constraint test_part_pk
  8. primary key(id)
  9. )
  10. partition by range (id) (partition p1 values less than (10000));

Логично, что если в такой таблице не будет пропусков, то новые секции было бы желательно создавать до того как ключ секционирования приблизится к границе максимальной секции. Сколько у нас осталось значений ключа до границы, мы легко можем определить согласно простой формуле: partition_size — (key-start_key_in_partition), где key — текущий ключ секционирования, start_key_in_partition — первый ключ, который попадает в эту секцию, partition_size — количество ключей в секции, а % — операция целочисленного деления(div). Обычно такое секционирование производится на равные секции, и с учетом этого мы можем упростить эту формулу до такой: partition_size — key%partition_size.
Что нам это дает: зная момент мы можем создать триггер, который будет добавлять секции при наступлении данного события.
Создадим данный триггер:
  1. create or replace trigger tr_test_part
  2.  before insert on test_part 
  3.  for each row
  4.  when (mod(NEW.id,10000) = 6000)
  5. declare
  6.  l_part_name number;
  7.  l_maxvalue number;
  8.  l_exist     number;
  9.  l_partition_exists exception;
  10.  PRAGMA EXCEPTION_INIT(l_partition_exists, -14074);
  11. PRAGMA AUTONOMOUS_TRANSACTION;
  12. begin
  13.  l_part_name:=ceil(:NEW.ID/10000)+1;
  14.  BEGIN
  15.       execute immediate 'alter table xtender.test_part add partition p'||l_part_name||' values less than('||l_maxvalue||')';
  16.  EXCEPTION
  17.     when l_partition_exists then null;
  18.  END;
  19. end tr_test_part;


Данный триггер с использованием автономных транзакций автоматически создает новую секцию с именем 'P'+номер секции размером в 10000, когда ID — наш ключ секционирования — остается 4000 значений до границы секции(10000-4000 = 6000, т.е. ID=6000,16000,26000, и тд.), но сначала проверяется не существует ли уже данная секция(такое может произойти, например, при повторном добавлении 6000-й записи, или ручном добавлении секции). Параметры секционирования — 10000 и 4000, вы должны подбирать исходя из вашей конкретной ситуации, но следует учесть, что граница(4000 в примере) должна быть больше максимального количества одномоментно добавляемых записей, т.к. иначе на момент транзакции вставки данных, транзакция не будет «знать» о новой секции, т.к. на начало транзакции ее не существовало, поэтому данные вставлены не будут с жалобой об отсутствии сопоставления секции данному ключу. Этого бы можно было избежать с использованием alter table split default_partition, который я рассмотрю далее, но это скажется на времени выполнения.
Проверим наш триггер, заполнив секцию:
insert into xtender.test_part
select rownum, o.OBJECT_NAME, o.OWNER, o.OBJECT_TYPE, o.CREATED
from all_objects o
where rownum<1000;


Кроме того, в случае использования сиквенсов, которые из-за кэширования «шагают» не последовательно можно изменить триггер, чтобы он выполнялся для набора значений с 4000 до 3900 записи с конца секции:
заменим условие
when (mod(NEW.id,10000) = 6000)
на
when (mod(NEW.id,10000) between 6000 and 6100)


Решение для прочих случаев


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

В Data dictionary мы можем получить информацию о всех секциях секционированных таблиц, сделав выборку из dba_tab_partitions, в которой partition_position указывает порядок секции в таблице, а high_value — параметры секции. Следовательно, мы можем получить имя последней секции в таблице и сделать выборку из нее для получения количества записей в ней.

После получения информации о таблицах, в которых началась запись в дефолтные секции, мы должны отправить уведомление. Для этого мы можем использовать варианты:
  • если у вас настроена автоматическая отправка алертов на почту, то просто записать событие в alert.log;
  • просто написать процедуру для отправки писем с уведомлением.


Первый вариант реализуется с помощью процедуры dbms_system.ksdwrt, которая имеет два параметра:
  • первый(BINARY_INTEGER) — куда записывать, с возможными значениями:1 — в стандартный трейс-файл, 2 — в alert.log, 3 — в оба;
  • и второй(varchar2) — собственно сама строка, которую пишем.

Пример:
exec dbms_system.ksdwrt(2, 'Test Alert Message');


Второй вариант — использовать пакет utl_mail или более низкоуровневые — utl_smtp или utl_tcp.
utl_mail — это более удобная обертка для utl_smtp, но для ее использования обязательно необходимо установить параметр smtp_out_server. Вы можете сделать это как только для сессии — «ALTER SESSION SET smtp_out_server = ...» так и для системы «ALTER SYSTEM SET smtp_out_server = ...».
Не удивляйтесь, если вы не можете найти этот пакет у себя — изначально он не включен и для его создания вы должны выполнить два скрипта:
sqlplus sys/<pwd>
SQL> @$ORACLE_HOME/rdbms/admin/utlmail.sql
SQL> @$ORACLE_HOME/rdbms/admin/prvtmail.plb


Сведем все воедино в пакет pkg_partitions
Методы пакета:
  • function get_penultimate_maxvalue(p_table_owner varchar2, p_table_name varchar2) return varchar2;
    Функция принимает в качестве параметров владельца и имя таблицы и возвращает значение условия(high_value) предпоследней секции. Данная информация может быть нужна, например, в случаях, где последняя секция — секция c maxvalue параметром, и, соответственно, параметр предпоследней секции может быть нужен для определения параметра для новой секции.
  • function get_maxvalued_partitions return tables_props_array pipelined;
    Функция возвращающая названия таблиц и их владельцев, у которых начала заполняться последняя секция.
    Пример использования:
    1. select
    2.  p.*,
    3.  sys.pkg_partitions.get_penultimate_maxvalue(p.table_owner,p.table_name) pre_maxvalue
    4. from
    5.  table(sys.pkg_partitions.get_maxvalued_partitions) p

  • function get_maxvalued_partitions_html return varchar2;
    Функция возвращает тоже самое, что и get_maxvalued_partitions, но в виде html-таблицы
  • procedure send_partitions_report(mail varchar2);
    Процедура отправки отчета с таблицами, у которых начала заполняться последняя секция. Единственный параметр — адрес, кому отсылать.


Код пакета:
  1. create or replace package body pkg_partitions is
  2. /** Функция возвращающая параметр для предпоследней секции
  3. * @param i_table_name Имя таблицы
  4. * @return varchar2
  5. */
  6.  function get_penultimate_maxvalue(p_table_owner varchar2,p_table_name varchar2) return varchar2 is
  7.      l_cursor    integer default dbms_sql.open_cursor;
  8.      l_ignore    number;
  9.      l_long_val varchar2(4000);
  10.      l_long_len number;
  11.      l_buflen    number := 4000;
  12.      l_curpos    number := 0;
  13.  begin
  14.      dbms_sql.parse( l_cursor,
  15.                      'select p.high_value                     from all_tab_partitions p                     where                      p.table_owner like :o                     and p.table_name like :x                     and p.partition_position=                         (                         select max(p1.partition_position)-1                          from all_tab_partitions p1                          where                          p.table_owner like :o                         and p1.table_name like :x                         )'
  16.                      ,
  17.                      dbms_sql.native );
  18.      dbms_sql.bind_variable( l_cursor, ':x', p_table_name );
  19.      dbms_sql.bind_variable( l_cursor, ':o', p_table_owner );
  20.  
  21.      dbms_sql.define_column_long(l_cursor, 1);
  22.      l_ignore := dbms_sql.execute(l_cursor);
  23.      if (dbms_sql.fetch_rows(l_cursor)>0)
  24.      then
  25.         dbms_sql.column_value_long(l_cursor, 1, l_buflen, l_curpos ,
  26.                                   l_long_val, l_long_len );
  27.      end if;
  28.      dbms_sql.close_cursor(l_cursor);
  29.      return l_long_val;
  30.  end;
  31.  
  32. /** Функция возвращающая названия таблиц и их владельцев, у которых начала заполняться последняя секция
  33. * @return
  34. *     table_name varchar2(4000),
  35. *     table_owner varchar2(4000),
  36. *     partitions_count number,
  37. *     partition_name varchar2(4000));
  38. */
  39.  function get_maxvalued_partitions return tables_props_array pipelined is
  40.      l_cursor    integer default dbms_sql.open_cursor;
  41.      l_count     number;
  42.      l_ignore    integer;
  43.      l_data     table_props;
  44.      cursor l_partitions is
  45.         select
  46.          pl.table_owner,
  47.          pl.table_name,
  48.          count(1) cnt,
  49.          max(pl.partition_name) keep(dense_rank last order by (pl.partition_position)) partition_name
  50.         from dba_tab_partitions pl
  51.         where pl.table_name not like 'BIN$%'
  52.         group by pl.table_owner,pl.table_name
  53.         having count(1)>1;
  54.  BEGIN
  55.      FOR part in l_partitions
  56.         LOOP
  57.          dbms_sql.parse( l_cursor,
  58.                      'select count(1)                     from '||part.table_owner||'.'||part.table_name
  59.                      ||' partition('||part.partition_name||')'
  60.                      ||' where rownum<2'
  61.                      ,
  62.                      dbms_sql.native );
  63.          dbms_sql.define_column(l_cursor,1,l_count);
  64.          l_ignore:=dbms_sql.execute_and_fetch(l_cursor);
  65.          dbms_sql.column_value(l_cursor,1,l_count);
  66.          if (l_count>0) then
  67.             l_data.table_name:=part.table_name;
  68.             l_data.table_owner:=part.table_owner;
  69.             l_data.partitions_count:=part.cnt;
  70.             l_data.partition_name:=part.partition_name;
  71.             pipe row(l_data);
  72.          end if;
  73.         END LOOP;
  74.  END;
  75.  
  76. /** Функция возвращающая названия таблиц и их владельцев в виде html, у которых начала заполняться последняя секция
  77. * @return
  78. *     table_name varchar2(4000),
  79. *     table_owner varchar2(4000),
  80. *     partitions_count number,
  81. *     partition_name varchar2(4000));
  82. */
  83.  function get_maxvalued_partitions_html return varchar2 is
  84.      l_cursor    integer default dbms_sql.open_cursor;
  85.      l_count     number;
  86.      l_ignore    integer;
  87.      l_data     varchar2(4000);
  88.      cursor l_partitions is
  89.         select
  90.          pl.table_owner,
  91.          pl.table_name,
  92.          count(1) cnt,
  93.          max(pl.partition_name) keep(dense_rank last order by (pl.partition_position)) partition_name
  94.         from dba_tab_partitions pl
  95.         where pl.table_name not like 'BIN$%'
  96.         group by pl.table_owner,pl.table_name
  97.         having count(1)>1;
  98.  BEGIN
  99.      l_data:='<html><body><table border=1>'
  100.             ||'<tr><th>Table name</th>'
  101.             ||'<th>Table owner</th>'
  102.             ||'<th>Partitions count</th>'
  103.             ||'<th>Partition name</th>'
  104.             ||'<th>Pre maxvalue</th>';
  105.     
  106.      FOR part in l_partitions
  107.         LOOP
  108.          dbms_sql.parse( l_cursor,
  109.                      'select count(1)                     from '||part.table_owner||'.'||part.table_name
  110.                      ||' partition('||part.partition_name||')'
  111.                      ||' where rownum<2'
  112.                      ,
  113.                      dbms_sql.native );
  114.          dbms_sql.define_column(l_cursor,1,l_count);
  115.          l_ignore:=dbms_sql.execute_and_fetch(l_cursor);
  116.          dbms_sql.column_value(l_cursor,1,l_count);
  117.          if (l_count>0) then
  118.             l_data:=l_data||'<tr><td>'
  119.                          ||part.table_name
  120.                          ||'</td><td>'
  121.                          ||part.table_owner
  122.                          ||'</td><td>'
  123.                          ||part.cnt
  124.                          ||'</td><td>'
  125.                          ||part.partition_name
  126.                          ||'</td></tr>';
  127.          end if;
  128.         END LOOP;
  129.      l_data:=l_data||'</table></body></html>';
  130.      return l_data;
  131.  END;
  132. /**
  133. * Процедура отправки отчета с таблицами, у которых начала заполняться последняя секция
  134. */
  135.  procedure send_partitions_report(mail varchar2)
  136.     is
  137.      msg_body varchar2(4000);
  138.     BEGIN
  139.      select pkg_partitions.get_maxvalued_partitions_html into msg_body from dual;
  140.      --EXECUTE IMMEDIATE 'ALTER SESSION SET smtp_out_server = ''our_mailserver''';
  141.      utl_mail.send(
  142.             sender => 'oracleDBA@dbdomain.com',
  143.             recipients => mail,
  144.             subject => 'Maxvalued partitions Report',
  145.             message => msg_body,
  146.             mime_type => 'text/html');
  147.     END;
  148. end pkg_partitions;
* This source code was highlighted with Source Code Highlighter.

UPD
Как правильно подсказал zhekappp можно использовать num_rows при включении автоматического сбора статистики. Сбор статистики можно будет включить добавлением задания с помощью dbms_job с dbms_stats.gather_table_stats.
Тогда нужно будет убрать запрос количества записей в секции и изменить запрос на:
  1. select
  2.          pl.table_owner,
  3.          pl.table_name,
  4.          count(1) cnt,
  5.          max(pl.num_rows) keep(dense_rank last order by (pl.partition_position)) partition_rows,
  6.          max(pl.partition_name) keep(dense_rank last order by (pl.partition_position)) partition_name
  7.         from dba_tab_partitions pl
  8.         where pl.table_name not like 'BIN$%'
  9.         group by pl.table_owner,pl.table_name

Полный код пакета для этого случая можно взять тут: http://www.xt-r.com/2010/10/pkgpartitions.html

Автоматическое выполнение


Осталось только настроить автоматическое выполнение. Сделаем это с помощью dbms_job.
Например, ежедневное автоматическое выполнение скрипта получения данных:
  1. declare
  2.  job binary_integer;
  3. begin
  4.  dbms_job.submit(
  5.      job,
  6.      'pkg_partitions.send_partitions_report(''dba@domain.ru'');',
  7.      sysdate,
  8.      'trunc(sysdate)+1');
  9.  dbms_output.put_line(job);
  10. end;
Tags:
Hubs:
+23
Comments 11
Comments Comments 11

Articles