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

Владимир Пржиялковский,
преподаватель УКЦ 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);

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 объектов с наибольшей длиной списка свободных блоков.