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


В версии 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.
Рассмотренные ниже решения можно применить и в других СУБД, не поддерживающих автоматическое добавление секций
UPD 06.12.2010: Еще можно посмотреть новую статью Арупа Нанды


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

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

Как правильно подсказал товарищ 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
Полный код пакета для этого случая можно взять в следующей заметке


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

Осталось только настроить автоматическое выполнение. Сделаем это с помощью 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;

Comments

Отправка комментария