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

    В версии 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;
    Share post
    AdBlock has stolen the banner, but banners are not teeth — they will be back

    More
    Ads

    Comments 11

      –3
      fyi. в литературе чаще используется термин «партицирование»
        +6
        В литературе? Например, в каких книгах? Все книги, которые я читал содержат секционирование. Транслитированное «партицирование» используют крайне редко и только на форумах.
        fyi:
        «партицирование таблиц» — Результатов: примерно 1 470
        «секционирование таблиц» — Результатов: примерно 19 700

        Кроме того:
        ru.wikipedia.org/wiki/Секционирование а не партицирование
        oracle.com — используют только «Секционирование»
        msdn.microsoft.com — используют только «Секционирование»
        Postgresql.org — используют только «Секционирование»
        +1
        Интересно было бы посмотреть бенчмарки без и с использованием партицирования.
          0
          when (mod(NEW.id,10000) = 6000)


          Надо помнить, что в sequnce, по которому формируется id легко могут быть пропуски.
          Например, из-за rollback-ов или, чаще, cache > 0.

          Вообще большой незачет oracle за то, что не придумали механизм формирования нормального имени новой автоматически добавляемой партиции.
            +1
            Совершенно верно, если используются сиквенсы пропуски будут, т.к. используется шаг для кэширования. В таких случаях придется устанавливать when (mod(NEW.id,10000) between 6000 and 6100) — соответственно триггер будет вызываться н-ое количество «лишних» раз, но выполняться будет быстро.

            Вообще большой незачет oracle за то, что не придумали механизм формирования нормального имени новой автоматически добавляемой партиции.

            Ну мне кажется это не особо важно, все равно при автоматизации брать данные из data dictionary.
              +1
              В основном работаю с историческими таблицами, секционированными по дате.
              Очень удобно, когда в имени секции соответствует хранимым в ней данным. Как для написания запросов с использованием from ...partiotion (...), так и для оценки размеров сегментов по вьюхам *_segments, так и для операций truncate, drop, split, exchange partition…

              Есть еще одна мысль по поводу select count(1) из максимальной секции — на моей системе (vldb) это будет работать, возможно, более суток для всех таблиц и неприятно скажется на содержимом db_cache. Добавление же хинта parallel решит проблему, но создаст заметную нагрузку на систему.
              Мне кажется более интересным вариант — обеспечить актуальную статистику и брать количество строк из поля dba_tab_partitions.num_rows :)
                0
                Спасибо за напоминание, забыл обновить скрипт — я позже его изменял на select count(1) where rownum<2, т.к. проверка нужна существования хоть одной записи. И добавил пункт про сбор статистики, хотя это гораздо более дорогостоящая операция, но в случае если статистика нужна не только для этого(для CBO, например), то использование um_rows действительно все упрощает :)
            0
            вставлять разбивку на партиции в триггер на нагруженной инстертами таблице очень опасно. Во первых партиция может не создаться из-за наличия блокировки на таблице, и тогда следующий инсерт опять вызовет разбивку. А если это пиковый период нагрузки для таблицы? Пойдет лавинообразный процесс. Время выполнения инсертов увеличится в разы
              0
              На нагруженной инсертами таблице вызывать блокировку на уровне таблицы — вообще грешно, в остальных случаях никакая блокировка не мешает.
                0
                alter table add partition накладывает EXCLUSIVE блокировку. insert/update в свою очередь накладывает SHARED блокировку. Поэтому, если на таблице идет интенсивный insert/update, то add partition не сможет получить EXCLUSIVE блокировку и вынужден будет отвалиться с ORA-00054: resource busy and acquire with NOWAIT specified, или ждать освобождение блокировки, что увеличит время транзакции.

                Вообще задача разбивки партиций это не задача приложения, а задача DBA и по собственному опыту добавление партиций лучше выносить в отдельный джоб и запускать его во время падения нагрузки на таблицу.
                  0
                  Собственно, я про это и сказал в предыдущем комментарии, но раз уж настаиваете, то давайте разберемся для данного конкретного случая:
                  • Если вы все-таки хотите использовать триггер и получаете ora-54, у вас два варианта:
                    1. увеличить ddl_lock_timeout
                    2. повторно выполнять ddl до выполнения

                    Пояснения: DDL будет выполнен, как только закончатся текущие блокирующие транзакции. Сама же процедура создания новой пустой секции очень быстрая, она гораздо быстрее разбиения maxvalued или default секции. Те же транзакции, которые будут начаты во время ddl, будут выполнены сразу после этой процедуры.
                  • Следующий вариант — автоматизированное создание секций в определенное время. Например, если ночью у вас значительное снижение нагрузки, то вы можете создать задание, в котором пройтись циклом по результату функции get_maxvalued_partitions, создавая секции для каждой из возвращенных таблиц.
                  • Если же и этот вариант вас не устраивает, то вы можете как я и описал, просто настроить джоб с уведомлением ДБА с помощью send_partitions_report

                  Все эти варианты я описал в статье.

            Only users with full accounts can post comments. Log in, please.