“Невидимые миру дыры” в табличных пространствах Oracle

Источник: Interface

Владимир Пржиялковский, преподаватель УКЦ Interface Ltd.

Известно, что память в табличных пространствах выдается объектам, хранимым в БД, экстентами. Место, которое занимает объект (в типичном случае - таблица или индекс) на диске, можно определить из системной таблицы USER_EXTENTS, содержащей перечень всех экстентов всех сегментов пользователя. Однако картина, выдаваемая подобным запросом, может оказаться слишком общей. За списком экстентов таблицы может скрываться:

недозаполненность последнего экстента
отсутствие полных блоков в экстенте

Маленький эксперимент

Заполненность экстентов данными способна вызвать в памяти швейцарский сыр, объемный снаружи, но полный пустот внутри. Проведем эксперимент. Он рассчитан на типичный размер блока 4096 байт и параметры табличного пространства, по умолчанию используемые в версии Oracle 8.1.5. Наберем

SQL>CREATE TABLE emp1 AS SELECT * FROM emp; Table created. SQL>INSERT INTO emp1 SELECT * FROM emp1; 14 rows created. SQL>

После этого семь раз подряд нажмем / и возврат каретки, а затем

SQL> INSERT INTO emp1 (SELECT * FROM emp1 where rownum<1600); 1599 rows created. SQL> SELECT COUNT(*) FROM emp1; COUNT(*) --------- 5183

Проверим число занятых таблицей EMP1 экстентов:

SQL> SELECT tablespace_name,extent_id,bytes,blocks 2 FROM user_extents 3 WHERE segment_name='EMP1' AND segment_type='TABLE'; TABLESPACE_NAME EXTENT_ID BYTES BLOCKS ------------------------------ --------- --------- --------- USERS 1 131072 32 USERS 0 131072 32

Занято два экстента.

Теперь добавим 50 еще строк в таблицу:

SQL> INSERT INTO emp1 (SELECT * FROM emp1 where rownum<50); 49 rows created.

Повторим запрос к USER_EXTENTS и увидим, что добавился новый экстент:

TABLESPACE_NAME EXTENT_ID BYTES BLOCKS ------------------------------ --------- --------- --------- USERS 1 131072 32 USERS 0 131072 32 USERS 2 131072 32

Очевидно, что экстент с номером 2 в сегменте таблицы EMP1 практически пуст, и реально таблица занимает не 32*3 блока, а немногим более 64. Более того, выдадим

SQL> DELETE FROM emp1 WHERE ROWID IN 2 (SELECT ROWID FROM emp1 WHERE ROWNUM < 5232); 5231 rows deleted. SQL> commit; Commit complete.

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

"Пустой хаост" "справа" от high watermark и "дыры" слева могут образовывать вместе значительные скрытые резервы табличного пространства (представьте себе, что рост таблицы EMP1 надолго прекратился; на это время 27 блоков останутся "замороженными"). Кроме того, большое число "дырок" может снижать эффективность работы с таблицей, о чем говорилось в статье " Возвращаем таблице вторую молодость ". И в том и в другом случае следует подумать: а не стоит ли таблицу пересоздать, возможно с новыми параметрами хранения (см. указанную статью).

Схема использования хранимым объектом блоков

Общая картинка распределения памяти в табличном пространстве выглядит примерно так:

Верхняя граница заполнения в Oracle может только расти - к сожалению для разработчика. По рисунку видно, что справа и слева от нее может образоваться много (как в примере выше) свободного места, которое по словарю-справочнику не увидишь. Прикинуть размер пропадающего пространства можно с помощью команды ANALYZE, однако это будет (а) примерная оценка и (б) анализ больших таблиц или большого их числа может потребовать у СУБД много ресурсов.

Тем не менее в Oracle есть выход из создавшейся ситуации. "Заглянуть" внутрь экстентов быстро и эффективно можно с помощью двух процедур из системного пакета DBMS_SPACE.

Пакет DBMS_SPACE

Посмотреть длину списка свободных блоков (дальше будем считать, что для таблиц заведено по одному такому списку, что делается системой по умолчанию и представляет собой самый распространенный случай; а вообще-то, их можно заводить и больше) можно процедурой DBMS_SPACE.FREE_BLOCKS:

SQL> SET SERVEROUTPUT ON SQL> DECLARE 2 free_blks NUMBER; 3 BEGIN 4 DBMS_SPACE.FREE_BLOCKS ( 5 segment_owner => USER, 6 segment_name => 'EMP1', 7 segment_type => 'TABLE', 8 freelist_group_id => 0, 9 free_blks => free_blks); 10 DBMS_OUTPUT.PUT_LINE('free_blks: '//free_blks); 11 END; 12 / free_blks: 68 PL/SQL procedure successfully completed.

68 блоков - это большой список. Большие списки свободных блоков сами по себе не криминал. Но это индикатор того, что "слева" от high watermark у вас могло образоваться много свободного места. Дальнейшее исследование можно проводить с помощью ANALYZE.

Узнать расположение самой верхней границы high watermark можно с помощью процедуры DBMS_SPACE.UNUSED_SPACE:

SQL> DECLARE 2 total_blocks NUMBER; 3 total_bytes NUMBER; 4 unused_blocks NUMBER; 5 unused_bytes NUMBER; 6 last_used_extent_file_id NUMBER; 7 last_used_extent_block_id NUMBER; 8 last_used_block NUMBER; 9 BEGIN 10 DBMS_SPACE.UNUSED_SPACE( 11 segment_owner => USER, 12 segment_name => 'EMP1', 13 segment_type => 'TABLE', 14 total_blocks => total_blocks, 15 total_bytes => total_bytes, 16 unused_blocks => unused_blocks, 17 unused_bytes => unused_bytes, 18 last_used_extent_file_id => last_used_extent_file_id, 19 last_used_extent_block_id => last_used_extent_block_id, 20 last_used_block => last_used_block); 21 DBMS_OUTPUT.PUT_LINE('total_blocks '//total_blocks); 22 DBMS_OUTPUT.PUT_LINE('total_bytes '//total_bytes); 23 DBMS_OUTPUT.PUT_LINE('unused_blocks '//unused_blocks); 24 DBMS_OUTPUT.PUT_LINE('unused_bytes '//unused_bytes);

DBMS_OUTPUT.PUT_LINE
('last_used_extent_file_id '//last_used_extent_file_id);
DBMS_OUTPUT.PUT_LINE
('last_used_extent_block_id '//last_used_extent_block_id);
29 DBMS_OUTPUT.PUT_LINE('last_used_block '//last_used_block); 30 END; 31 / total_blocks 96 total_bytes 393216 unused_blocks 27 unused_bytes 110592 last_used_extent_file_id 3 last_used_extent_block_id 386 last_used_block 5 PL/SQL procedure successfully completed.

Видно, что справа от high watermark имеется 27 неиспользованных блоков. В нашем случае это чуть меньше трети размера всего сегмента с таблицей (96 блоков), то есть очень много. Сама отметка high watermark находится на пятом блоке последнего экстента в нашем сегменте, причем сам экстент находится в файле номер 3.

Как этим можно пользоваться

Основная ценность процедур пакета DBMS_SPACE во-первых, в том, что они, как было сказано, дают информацию о заполнении табличного пространства, отсутствующую в словаре-справочнике, и, во-вторых, делают это быстро. Анализ заполненности экстентов, типа приведенного выше, разумно сделать регулярным, составив для этого специальный сценарий и организовав регулярный запуск этого сценария (например, с помощью DBMS_JOB).

Ниже приводится сценарий, который можно взять за основу. Он выдает справку для таблиц и индексов пользователя.

DECLARE CURSOR object_cur (obj_type IN VARCHAR2) IS SELECT * FROM user_objects WHERE object_type = obj_type; obj_rec user_objects%ROWTYPE; free_blks NUMBER; total_blocks NUMBER; total_bytes NUMBER; unused_blocks NUMBER; unused_bytes NUMBER; last_used_extent_file_id NUMBER; last_used_extent_block_id NUMBER; last_used_block NUMBER; PROCEDURE show_object_type(obj_type_in IN VARCHAR2) IS BEGIN DBMS_OUTPUT.PUT_LINE (RPAD(obj_type_in,30)// RPAD('Free blocks',15)// RPAD('Total blocks',15)// RPAD('Unused blocks',15)// RPAD('Unused bytes',15) ); DBMS_OUTPUT.PUT_LINE (RPAD('-',30,'-')// RPAD('-',15,'-')// RPAD('-',15,'-')// RPAD('-',15,'-')// RPAD('-',15,'-') ); OPEN object_cur(obj_type_in); LOOP FETCH object_cur INTO obj_rec; EXIT WHEN object_cur%NOTFOUND; IF obj_rec.temporary = 'N' THEN DBMS_SPACE.FREE_BLOCKS ( segment_owner => USER, segment_name => obj_rec.object_name, segment_type => obj_type_in, freelist_group_id => 0, free_blks => free_blks); DBMS_SPACE.UNUSED_SPACE( segment_owner => USER, segment_name => obj_rec.object_name, segment_type => obj_type_in, total_blocks => total_blocks, total_bytes => total_bytes, unused_blocks => unused_blocks, unused_bytes => unused_bytes, last_used_extent_file_id => last_used_extent_file_id, last_used_extent_block_id => last_used_extent_block_id, last_used_block => last_used_block); DBMS_OUTPUT.PUT_LINE (RPAD(obj_rec.object_name,30)// RPAD(free_blks,15)// RPAD(total_blocks,15)// RPAD(unused_blocks,15)// RPAD(unused_bytes,15) ); END IF; END LOOP; CLOSE object_cur; END show_object_type; BEGIN show_object_type('TABLE'); DBMS_OUTPUT.PUT_LINE('-'); DBMS_OUTPUT.PUT_LINE('-'); show_object_type('INDEX'); END; / Результатом такого сценария может быть что-нибудь, вроде: TABLE Free blocks Total blocks Unused blocks Unused bytes -------------------------------------------------------------------- BONUS 0 32 31 126976 CODETEST 0 32 31 126976 DEPT 1 32 30 122880 EMP 1 32 30 122880 EMP1 68 96 27 110592 EMP2 0 32 31 126976 MLOG$_DEPT 1 32 30 122880 NCODETEST 1 32 30 122880 SALGRADE 1 32 30 122880 - - INDEX Free blocks Total blocks Unused blocks Unused bytes -------------------------------------------------------------------- PK_DEPT 0 32 30 122880 PK_EMP 0 32 30 122880 SYS_C001151 0 32 30 122880 SYS_C001152 0 32 30 122880 PL/SQL procedure successfully completed.

В этой тренировочной схеме SCOTT виден большой расход памяти впустую. Нужно помнить, что решение о реорганизации таблиц следует принимать с осторожностью: выбор разных размеров экстентов для разных объектов чреват фрагментацией табличного пространства. (Возникнет ли оно реально, зависит еще от характера использования таблиц). Увы, но это Сцилла и Харибда, которые предлагает Oracle.

Надеюсь, что приведенный сценарий послужит отправной точкой для вашего творчества. Например, он не выдает полезную в этом случае информацию о табличных пространствах, где происходят потери памяти. Кроме того, при большом числе объектов разумно составить сценарий, который бы выдавал, к примеру, 10 объектов с наиболее "пустым" "хвостом" и 10 объектов с наибольшей длиной списка свободных блоков. Возможны и другие усовершенствования.