Источник: статья предоставлена авторами для публикации в интернет-журнале FORS Magazine
|
|
Секционирование таблиц в Oracle Database дает новые возможности повышения производительности работы с ними. Секционирование таблиц позволяет:
От версии к версии Oracle расширяются возможности секционирования таблиц. Например, в Oracle 11g появилась возможность автоматического создания новой секции, расширен перечень комбинаций секций и подсекций, использование в качестве ключа секционирования виртуального, а не только реального столбца и др. Последняя новая возможность была использована нами при секционировании по циклу.
Под секционированием таблиц по циклу понимаются секционирование, выполненное в соответствии с двумя правилами. Первое правило – таблица должна содержать фиксированное количество секций, равное числу дней в месяце (31 секция) или в году (366 секций), либо числу месяцев (12 секций). Второе правило - данные в одну и ту же секцию попадают с определенной периодичностью (цикличностью). Периодичность зависит от метода секционирования по циклу и может быть равна 31, 366 или 12. Например, данные попадают в секцию определенного дня года, независимо от номера года.
Идея секционирования по циклу возникла из следующей практической задачи. В одну из таблиц с именем DOCIMAGE записывается большой поток данных, основной объем которого составляют BLOB'ы, помещаемые в столбец OLEOBJECT таблицы DOCIMAGE. Вероятность обращения к этим данных со временем быстро снижается, однако, сохраняется требование их непрерывной доступности, т.е. в любой момент времени эти BLOB'ы могут быть востребованы. Для уменьшения стоимости хранения большого объема данных было решено переместить в архив основную по объему часть данных (а, именно, столбцы с BLOB-данными) и расположить их на более дешевом и более медленном устройстве хранения EMC Centera.
Для этого было проведено секционирование таблицы DOCIMAGE (с секциями на каждый день года) по столбцу PARTID, в котором номер текущего дня формируется из столбца CREATED соответствующим триггером. При создании таблицы было задано, что сама таблица размещается в одном табличном пространстве, а ее BLOB-данные в другом табличном пространстве IMAGETBS. Секции ежедневно заполняются, при этом раз в сутки запускается процесс, который очищает BLOB-столбцы более ранних секций (двух месячной давности и не имеющих запрета на очистку) со Shrink-сжатием табличного пространства этих секций. Для первой секции команда Shrink, например, имеет вид:
ALTER TABLE DOCIMAGE MODIFY PARTITION DOCIMAGE_1 LOB(OLEOBJECT) (SHRINK SPACE);
В случае, если пользовательский запрос запрашивает BLOB-данные, которых нет в оперативной таблице DOCIMAGE, система извлекает данные из архива с устройства EMC Centera и помещает их обратно в соответствующую секцию таблицы DOCIMAGE. После этого данные обрабатываются и остаются в оперативной таблице DOCIMAGE, пока не попадут в очередной цикл очистки. Таким образом, в оперативной таблице DOCIMAGE содержатся свежие данные за последние два месяца плюс более старые данные, которые недавно были извлечены из архива. Все остальные BLOB-данные располагается на более медленном архивном устройстве. Данная секционированная таблица успешно функционирует 1,5 года и показала эффективность принятого решения.
Команда создания таблицы DOCIMAGE с размещением BLOB'ов в другом табличном пространстве приведена ниже:
CREATE TABLE ADM.DOCIMAGE
( ISN NUMBER NOT NULL,
CREATED DATE,
…
OLEOBJEC BLOB,
PARTID NUMBER
)
TABLESPACE ADMDATA
LOB (OLEOBJECT) STORE AS
( TABLESPACE IMAGETBS
ENABLE STORAGE IN ROW
CACHE
STORAGE ( BUFFER_POOL DEFAULT )
)
PARTITION BY RANGE (PARTID)
(
PARTITION DOCIMAGE_1 VALUES LESS THAN (2),
...
PARTITION DOCIMAGE_366 VALUES LESS THAN (367)
) ENABLE ROW MOVEMENT;
Конструкция
LOB (OLEOBJECT) STORE AS
( TABLESPACE IMAGETBS
ENABLE STORAGE IN ROW
CACHE )
позволяет не только обеспечивать ввод данных LOB-столбца OLEOBJECT в другое табличное пространство IMAGETBS (сама таблица находиться в табличном пространстве ADMDATA), но и задавать режимы ввода LOB данных. Так, при наличии фразы ENABLE STORAGE IN ROW маленькие LOB'ы размером менее 4000 байт будут храниться в самой таблице, а при размере LOB'ов более 4000 байт они будут размещаться в LOB-сегменте (при замене фразы ENABLE STORAGE IN ROW на DISABLE STORAGE IN ROW все LOB'ы будут храниться в LOB-сегменте). Фраза CACHE обеспечивает кэширование данных LOB.
При создании таблицы с LOB-столбцом для него формируются LOB-сегмент и LOB-индексный сегмент, даже если потом они окажутся пустыми вследствие того, что все LOB'ы останутся в строках таблицы.
Создаваемые LOB-сегменты можно увидеть по запросу:
SELECT OWNER, TABLE_NAME, COLUMN_NAME, SEGMENT_NAME SEGMENT_LOB,
TABLESPACE_NAME, INDEX_NAME, IN_ROW, CHUNK, PCTVERSION, CACHE, PARTITIONED
FROM DBA_LOBS WHERE OWNER='ADM' AND TABLE_NAME='DOCIMAGE';
TABLE_NAME COLUMN_NAME SEGMENT_LOB INDEX_NAME ---------- ----------- ------------------------- -------------------- DOCIMAGEOLE OBJECT SYS_LOB0000488329C00006$$ SYS_IL0000488329C00006$$
В случае секционирования таблицы с LOB-столбцами каждой секции таблицы будет соответствовать две новых секции: секция LOB-сегмента и LOB-индексный сегмент. Результаты секционирования таблицы с LOB-столбцами можно увидеть запросом:
SELECT D.TABLESPACE_NAME ,L.TABLE_OWNER,L.TABLE_NAME,
L.COLUMN_NAME ,L.PARTITION_POSITION POS,L.PARTITION_NAME,
L.LOB_PARTITION_NAME,LOB_INDPART_NAME,
L.SEGMENT_CREATED,LOB_NAME,INDEX_NAME
FROM ALL_LOB_PARTITIONS L, ALL_LOBS D
WHERE L.TABLE_OWNER=D.OWNER AND L.TABLE_NAME=D.TABLE_NAME
AND L.COLUMN_NAME=D.COLUMN_NAME
AND L.TABLE_OWNER='ADM' AND L.TABLE_NAME='DOCIMAGE'
ORDER BY L.PARTITION_POSITION;
В дальнейшем подход секционирования по циклу был развит и реализован в ряде других таблиц, в которых данные хранятся ограниченное время (несколько дней или месяцев). Такие таблицы были секционированы по циклу с быстрой Truncate-очисткой устаревших секций.
Опыт показал, что эффективно секционируются по циклу (помимо указанной выше таблицы типа DOCIMAGE) таблицы трех видов:
В этих таблицах преимуществом секционирования по циклу является то, что не надо создавать новые и уничтожать старые секции, а главное преимущество в том, что очистка секции по truncate происходит быстро и, что еще более существенно, очитка при Truncate идет с освобождением табличного пространства для ввода новых данных.
Идея секционирования по циклу была реализована в таблице PAGES с BLOB-столбцом PAGE_DATA. В этих BLOB-элементах содержится отсканированные документы по страхованию. Объем данных, вводимых ежемесячно в таблицу PAGES, более 500 Гб, и он продолжает расти. В силу этого, хранение информации в течение нескольких месяцев потребовало бы терабайты пространства. В связи с этим, было принято решение разбить таблицу PAGES на 12 месяцев и хранить данные только за текущий и предыдущий месяц (за 2 месяца), а более старые секции очищать по Truncate с освобождением табличного пространства для ввода новых данных. В результате объем хранимых данных стал более ограничен и предсказуем, а очистка по Truncate (вместо обычного Delete, требующего использования команды Shrink для освобождения табличного пространства) происходит быстро с минимальной загрузкой процессора. Секционирование по циклу таблицы PAGES проведено по методу RANGE, где ключем секционирования выступил вновь созданный в таблице столбец PARTID, принимающий значения от 1 до 12 (по числу месяцев). Столбец PARTID заполняется триггером при вводе новой строки в таблицу. В триггере выполняется функция to_number(to_char(CREATED,'MM')), которая обрабатывает столбец CREATED таблицы (дата создания записи), выделяя значение месяца.
Структура таблицы PAGES в схеме ADM приведена ниже.
CREATE TABLE ADM.PAGES ( ID NUMBER, PAGE_DATA BLOB, CREATED DATE, … PARTID NUMBER ) TABLESPACE ADMDATA PARTITION BY RANGE (PARTID) ( PARTITION PAGES_1 VALUES LESS THAN (2) , PARTITION PAGES_2 VALUES LESS THAN (3) , PARTITION PAGES_3 VALUES LESS THAN (4) , PARTITION PAGES_4 VALUES LESS THAN (5) , PARTITION PAGES_5 VALUES LESS THAN (6) , PARTITION PAGES_6 VALUES LESS THAN (7) , PARTITION PAGES_7 VALUES LESS THAN (8) , PARTITION PAGES_8 VALUES LESS THAN (9) , PARTITION PAGES_9 VALUES LESS THAN (10), PARTITION PAGES_10 VALUES LESS THAN (11), PARTITION PAGES_11 VALUES LESS THAN (12), PARTITION PAGES_12 VALUES LESS THAN (13) ) ENABLE ROW MOVEMENT; --возможность перемещения данных между секциями
Результаты секционирования могут быть просмотрены запросом:
Select * From ALL_TAB_PARTITIONS where table_owner='ADM' and table_name ='PAGES';
Вид триггера, заполняющего столбец PARTID, приведен ниже:
CREATE OR REPLACE TRIGGER ADM.PAGES_BIU
BEFORE INSERT ON PAGES REFERENCING
FOR EACH ROW
BEGIN
:NEW.PARTID:=to_number(to_char(:NEW.Created,'MM'));
END;
Очистка по Truncate, например, второй секции выполняется по команде:
ALTER TABLE ADM.PAGES TRUNCATE PARTITION(PAGES_2) UPDATE GLOBAL INDEXES;
где фраза UPDATE GLOBAL INDEXES позволяет обеспечить после Truncate доступность глобальных индексов таблицы.
Контроль над освобождением табличного пространства (в байтах) после выполнения Truncate можно осуществлять по запросу:
SELECT TABLESPACE_NAME, SUM(BYTES) FREE_BYTES FROM DBA_FREE_SPACE GROUP BY TABLESPACE_NAME ORDER BY 1;
Секционирование может быть не только по методу секционирования RANGE, но и по LIST. В этом случае командные строки секционирования таблицы примут вид
PARTITION BY LIST (PARTID) ( PARTITION PAGES_1 VALUES (1) , … PARTITION PAGES_12 VALUES (12) )
Приведенный выше вариант был разработан для версии Oracle 10g. С переходом на Oracle 11g другие таблицы, например, таблица SESS_LOCK (будет рассмотрена ниже) была секционирована с использованием виртуального столбца вместо реального столбца PARTID по методу секционирования LIST. Для таблицы PAGES виртуальный столбец имел бы вид:
PARTID NUMBER generated always as ( to_number(to_char(CREATED ,'MM')) ) virtual
где строки generated always as и virtual - типовые команды для виртуального столбца. Строка to_number(to_char(CREATED ,'MM')) отражает функцию автоматического заполнения виртуального столбца PARTID данными из столбца Created в процессе ввода данных в таблицу.
Преимуществом такого решения является то, что триггер записи данных в столбец PARTID создавать не надо, столбец автоматически будет заполняться числами от 1 до 12 из столбца CREATED.
Команда создания таблицы с виртуальным столбцом выглядит следующим образом:
CREATE TABLE ADM.PAGES (ID NUMBER, PAGE_DATA BLOB, CREATED DATE, PARTID NUMBER generated always as ( to_number(to_char(CREATED ,'MM')) ) virtual ) TABLESPACE ADMDATA ( PARTITION PAGES_1 VALUES LESS THAN (2) , … PARTITION PAGES_12 VALUES LESS THAN (13) )
Замечание. Нужно отметить, что преимуществом циклического секционирования является также наличие фиксированного названия секций, что позволяет использовать это при просмотре и анализа секций. Например, для указанной выше таблицы, ниже приведенный запрос позволяет следить за числом записей в таблице по секциям и процессом очистки секций.
select 'pages_1' part, count(*) num_rows from ADM.PAGES partition(PAGES_1) union all select 'pages _2', count(*) from ADM.PAGES partition(PAGES_2) … union all select 'pages _12', count(*) from ADM.PAGES partition(PAGES_12)
В таблицу SESS_LOCK в течение дня вводиться информация о блокировках между сессиями. В таблице имеется столбец CLOB. Данная информация храниться менее месяца (за 14 дней). Секционирование по циклу было организовано с 31-й секцией (максимальное число дней в месяце). Ежедневно запускает JOB, который вызывает процедуру очистки старой секции, отстоящей на 15 дней от текущей, а также очищает секцию, следующей после текущей, подготавливая ее для ввода данных на следующий день.
Таблица работает в Oracle 11g, поэтому используется виртуальный столбец PARTDD взамен реального столбца (что позволило обойтись без триггера для заполнения столбца данными).
Виртуальный столбец автоматически заполняется значением из столбца Monitortime, содержащего текущую дату ввода строки. Таблица была секционирована по методу секционирования LIST. Команда создание таблицы выглядит следующим образом:
CREATE TABLE ADM.SESS_LOCK (SID NUMBER, SERIAL# NUMBER, WHO_SID NUMBER, WHO_SERIAL# NUMBER, SQL_ID VARCHAR2(13), SQL_FULLTEXT CLOB, … EVENT VARCHAR2(64), MONITORTIME DATE, PARTDD NUMBER generated always as (to_number(to_char(MONITORTIME,'DD')) ) virtual ) TABLESPACE ADMDATA PARTITION BY LIST (PARTDD) ( PARTITION SESS_LOCK_01 VALUES(1) COMPRESS FOR ALL OPERATIONS, PARTITION SESS_LOCK_02 VALUES(2) COMPRESS FOR ALL OPERATIONS, … PARTITION SESS_LOCK_31 VALUES(31) COMPRESS FOR ALL OPERATIONS ) ENABLE ROW MOVEMENT;
где COMPRESS FOR ALL OPERATIONS - команда на сжатие по DML операциям.
Режим процедуры определяется параметром PARAM_DDMM, задаваемым в JOB. В JOB так же через параметр DELTA_TRUNC задается число месяцев (число дней), в пределах которых подлежит сохранить данные в таблице. Вид JOB Sheduler для таблицы SESS_LOCK, запускающего ежедневно процедуру ADM.P_TRUNC_PART_DDMM очистки секций с сохранением данных за 14 дней, имеет вид:
Для секционирования по месяцам JOB-команда примет вид
BEGIN DBMS_SCHEDULER.CREATE_JOB (job_name => 'ADM.JOB_TRUNCATE_SESS_LOCK', job_type => 'PLSQL_BLOCK', repeat_interval => 'FREQ=DAILY; INTERVAL=1; BYHOUR=09; BYMINUTE=20', job_action => ' DECLARE OWNER VARCHAR2(30):=''ADM''; TABLE_NAME VARCHAR2(30):=''PAGES''; DELTA_TRUNC NUMBER:=14; PARAM_DDMM VARCHAR2(2):=''DD''; BEGIN ADM.P_TRUNC_PART_DDMM (OWNER, TABLE_NAME, DELTA_TRUNC, PARAM_DDMM); END; ', start_date=>SYSDATE, enabled => TRUE, comments => 'Truncate table SESS_LOCK' ); END;
Для секционирования по месяцам в JOB команда
repeat_interval примет вид:
repeat_interval => 'FREQ=MONTHLY; INTERVAL=1',
а PARAM_DDMM примет значение "MM".
Универсальная процедура очистки секций ADM.P_TRUNC_PART_DDMM приводиться ниже.
CREATE OR REPLACE PROCEDURE ADM.P_TRUNC_PART_DDMM (P_OWNER IN VARCHAR2, P_TABLE_NAME IN VARCHAR2, P_DELTA_TRUNC IN NUMBER , P_DDMM IN VARCHAR2 ) IS Part_pos number; Part_pos_curent number; Part_value number; Part_valuev varchar2(30); Part_delta_minus number; Part_delta_plus number; Lastday_minus number:=12; Lastday_plus number:=12; Delta_trunc number:=P_delta_trunc; Sysdat date:=sysdate; --Процедура truncate секции procedure p_alter(p_part_pos in number) as part_name varchar2(100);str varchar2(500); begin Select min(partition_name) into Part_name from ALL_TAB_PARTITIONS where table_owner=P_OWNER and table_name=P_TABLE_NAME and partition_position=p_part_pos; Str:='ALTER TABLE '||P_OWNER||'.'||P_TABLE_NAME||' TRUNCATE PARTITION '|| PART_NAME||' UPDATE GLOBAL INDEXES'; Execute immediate(str); Exception when others then null; end; BEGIN Part_pos_curent:=to_number(to_char(sysdat,''||P_DDMM||'')); if P_DDMM='DD' then Lastday_minus:= to_number (to_char (last_day (trunc(sysdat,'MM')-1),''||P_DDMM||'')); Lastday_plus:= to_number(to_char(last_day(sysdat),''||P_DDMM||'')); end if; if Delta_trunc>Lastday_minus-1 then Delta_trunc:=Lastday_minus-1;end if; --1.Определение секции, очищаемой ранее текущей Part_delta_minus:=Part_pos_curent-Delta_trunc-1; if Part_delta_minus>=1 then Part_pos:=Part_delta_minus; else Part_pos:=Lastday_minus+Part_delta_minus; end if; P_ALTER(PART_POS); --2. Определение секции, очищаемой следующей после текущей if Part_pos_curent<Lastday_plus then Part_pos:=Part_pos_curent+1; else Part_pos:=1; end if; P_ALTER(PART_POS); END;
Циклическое секционирование наиболее эффективно для таблиц, у которых имеется ограниченное время жизни данных в секциях, например, в течение нескольких дней внутри месяца, года или несколько месяцев внутри года (при этом в столбцах таблицы могут как быть, так не быть LOB-столбцы).
Преимуществами циклического секционирования являются:
К дополнительным расходам при секционировании по циклу можно отнести необходимость создания JOB и процедуры очистки секций для освобождения места для ввода новых данных. Поскольку JOB и процедура очистки представлены выше в готовом виде, а время их работы незначительно, то это не является существенным ограничением на использования секционирования по циклу. При этом следует учесть, что очистка секций с целью удалений старых данных (порой с удалением секций) широко используется и в традиционном секционировании, что потребует так же разработки JOB и процедур очистки секций.