В версиях до 11g необходимо периодически вручную либо заранее добавлять секции, либо разбивать секцию по умолчанию. То есть постоянно необходимо отслеживать состояние таких таблиц. В данной статье я поделюсь своими решениями для автоматизации таких задач секционирования.
Сначала приведу пример для 11g:
- create table res (
- res_id number not null,
- res_date date,
- hotel_id number(3),
- guest_id number
- )
- partition by range (res_id)
- interval (100) store in (users)
- (
- partition p1 values less than (101)
- );
Рассмотренные ниже решения можно применить и в других СУБД, не поддерживающих автоматическое добавление секций
UPD 06.12.2010: Еще можно посмотреть новую статью Арупа Нанды
Решение для равномерно увеличивающегося ключа секционирования без пропусков
Создадим тестовую таблицу:- create table test_part(
- id number not null,
- name varchar2(100) not null,
- owner varchar2(100) not null,
- type varchar2(100) not null,
- created date not null,
- constraint test_part_pk
- primary key(id)
- )
- partition by range (id) (partition p1 values less than (10000));
Что нам это дает: зная момент мы можем создать триггер, который будет добавлять секции при наступлении данного события.
Создадим данный триггер:
- create or replace trigger tr_test_part
- before insert on test_part
- for each row
- when (mod(NEW.id,10000) = 6000)
- declare
- l_part_name number;
- l_maxvalue number;
- l_exist number;
- l_partition_exists exception;
- PRAGMA EXCEPTION_INIT(l_partition_exists, -14074);
- PRAGMA AUTONOMOUS_TRANSACTION;
- begin
- l_part_name:=ceil(:NEW.ID/10000)+1;
- BEGIN
- execute immediate 'alter table xtender.test_part add partition p'||l_part_name||' values less than('||l_maxvalue||')';
- EXCEPTION
- when l_partition_exists then null;
- END;
- 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;
заменим условие
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;
Функция возвращающая названия таблиц и их владельцев, у которых начала заполняться последняя секция.
Пример использования:
- select
- p.*,
- sys.pkg_partitions.get_penultimate_maxvalue(p.table_owner,p.table_name) pre_maxvalue
- from
- 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);
Процедура отправки отчета с таблицами, у которых начала заполняться последняя секция. Единственный параметр - адрес, кому отсылать.
Код пакета:
- create or replace package body pkg_partitions is
- /** Функция возвращающая параметр для предпоследней секции
- * @param i_table_name Имя таблицы
- * @return varchar2
- */
- function get_penultimate_maxvalue(p_table_owner varchar2,p_table_name varchar2) return varchar2 is
- l_cursor integer default dbms_sql.open_cursor;
- l_ignore number;
- l_long_val varchar2(4000);
- l_long_len number;
- l_buflen number := 4000;
- l_curpos number := 0;
- begin
- dbms_sql.parse( l_cursor,
- '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
- )'
,
dbms_sql.native );
dbms_sql.bind_variable( l_cursor, ':x', p_table_name );
dbms_sql.bind_variable( l_cursor, ':o', p_table_owner );
dbms_sql.define_column_long(l_cursor, 1);
l_ignore := dbms_sql.execute(l_cursor);
if (dbms_sql.fetch_rows(l_cursor)>0)
then
dbms_sql.column_value_long(l_cursor, 1, l_buflen, l_curpos ,
l_long_val, l_long_len );
end if;
dbms_sql.close_cursor(l_cursor);
return l_long_val;
end;
/** Функция возвращающая названия таблиц и их владельцев, у которых начала заполняться последняя секция
* @return
* table_name varchar2(4000),
* table_owner varchar2(4000),
* partitions_count number,
* partition_name varchar2(4000));
*/
function get_maxvalued_partitions return tables_props_array pipelined is
l_cursor integer default dbms_sql.open_cursor;
l_count number;
l_ignore integer;
l_data table_props;
cursor l_partitions is
select
pl.table_owner,
pl.table_name,
count(1) cnt,
max(pl.partition_name) keep(dense_rank last order by (pl.partition_position)) partition_name
from dba_tab_partitions pl
where pl.table_name not like 'BIN$%'
group by pl.table_owner,pl.table_name
having count(1)>1;
BEGIN
FOR part in l_partitions
LOOP
dbms_sql.parse( l_cursor,
'select count(1)
from '||part.table_owner||'.'||part.table_name
||' partition('||part.partition_name||')'
||' where rownum<2'
,
dbms_sql.native );
dbms_sql.define_column(l_cursor,1,l_count);
l_ignore:=dbms_sql.execute_and_fetch(l_cursor);
dbms_sql.column_value(l_cursor,1,l_count);
if (l_count>0) then
l_data.table_name:=part.table_name;
l_data.table_owner:=part.table_owner;
l_data.partitions_count:=part.cnt;
l_data.partition_name:=part.partition_name;
pipe row(l_data);
end if;
END LOOP;
END;
/** Функция возвращающая названия таблиц и их владельцев в виде html, у которых начала заполняться последняя секция
* @return
* table_name varchar2(4000),
* table_owner varchar2(4000),
* partitions_count number,
* partition_name varchar2(4000));
*/
function get_maxvalued_partitions_html return varchar2 is
l_cursor integer default dbms_sql.open_cursor;
l_count number;
l_ignore integer;
l_data varchar2(4000);
cursor l_partitions is
select
pl.table_owner,
pl.table_name,
count(1) cnt,
max(pl.partition_name) keep(dense_rank last order by (pl.partition_position)) partition_name
from dba_tab_partitions pl
where pl.table_name not like 'BIN$%'
group by pl.table_owner,pl.table_name
having count(1)>1;
BEGIN
l_data:='<html><body><table border=1>'
||'<tr><th>Table name</th>'
||'<th>Table owner</th>'
||'<th>Partitions count</th>'
||'<th>Partition name</th>'
||'<th>Pre maxvalue</th>';
FOR part in l_partitions
LOOP
dbms_sql.parse( l_cursor,
'select count(1)
from '||part.table_owner||'.'||part.table_name
||' partition('||part.partition_name||')'
||' where rownum<2'
,
dbms_sql.native );
dbms_sql.define_column(l_cursor,1,l_count);
l_ignore:=dbms_sql.execute_and_fetch(l_cursor);
dbms_sql.column_value(l_cursor,1,l_count);
if (l_count>0) then
l_data:=l_data||'<tr><td>'
||part.table_name
||'</td><td>'
||part.table_owner
||'</td><td>'
||part.cnt
||'</td><td>'
||part.partition_name
||'</td></tr>';
end if;
END LOOP;
l_data:=l_data||'</table></body></html>';
return l_data;
END;
/**
* Процедура отправки отчета с таблицами, у которых начала заполняться последняя секция
*/
procedure send_partitions_report(mail varchar2)
is
msg_body varchar2(4000);
BEGIN
select pkg_partitions.get_maxvalued_partitions_html into msg_body from dual;
--EXECUTE IMMEDIATE 'ALTER SESSION SET smtp_out_server = ''our_mailserver''';
utl_mail.send(
sender => 'oracleDBA@dbdomain.com',
recipients => mail,
subject => 'Maxvalued partitions Report',
message => msg_body,
mime_type => 'text/html');
END;
end pkg_partitions;
Как правильно подсказал товарищ zhekappp, еще можно использовать num_rows при включении автоматического сбора статистики. Сбор статистики можно будет включить добавлением задания с помощью dbms_job с dbms_stats.gather_table_stats.
Тогда нужно будет убрать запрос количества записей в секции и изменить запрос на:
- select
- pl.table_owner,
- pl.table_name,
- count(1) cnt,
- max(pl.num_rows) keep(dense_rank last order by (pl.partition_position)) partition_rows,
- max(pl.partition_name) keep(dense_rank last order by (pl.partition_position)) partition_name
- from dba_tab_partitions pl
- where pl.table_name not like 'BIN$%'
- group by pl.table_owner,pl.table_name
Автоматическое выполнение
Осталось только настроить автоматическое выполнение. Сделаем это с помощью dbms_job.
Например, ежедневное автоматическое выполнение скрипта получения данных:
- declare
- job binary_integer;
- begin
- dbms_job.submit(
- job,
- 'pkg_partitions.send_partitions_report(''dba@domain.ru'');',
- sysdate,
- 'trunc(sysdate)+1');
- dbms_output.put_line(job);
- end;
Сообщения
Just another Oracle developers
Comments
Отправить комментарий