Фрагментация в табличных пространствах Oracle и как с этим бороться

Известно, что память в табличных пространствах выдается объектам, хранимым в БД, экстентами. Место, которое занимает объект (в типичном случае - таблица или индекс) на диске, можно определить из системной таблицы 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?

Верхняя граница заполнения в 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); 
  1. DBMS_OUTPUT.PUT_LINE
  2. ('last_used_extent_file_id '||last_used_extent_file_id);
  3. DBMS_OUTPUT.PUT_LINE
  4. ('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_SPACE во-первых, в том, что они, как было сказано, дают информацию о заполнении табличного пространства, отсутствующую в словаре-справочнике, и, во-вторых, делают это быстро. Анализ заполненности экстентов, типа приведенного выше, разумно сделать регулярным, составив для этого специальный сценарий и организовав регулярный запуск этого скрипта (например, с помощью DBMS_JOB). Ниже приводится скрипт для DBMS_SPACE, который можно взять за основу. Он выдает справку для таблиц и индексов пользователя:

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

Заметка не моя. Автор Владимир Пржиялковский, преподаватель.

Вас заинтересует / Intresting for you:

Как увеличить табличное простр...
Как увеличить табличное простр... 8315 просмотров Александров Попков Tue, 21 Nov 2017, 13:18:46
Как удалить табличное простран...
Как удалить табличное простран... 4223 просмотров Александров Попков Tue, 21 Nov 2017, 13:18:46
Табличное пространство Oracle ...
Табличное пространство Oracle ... 5275 просмотров Antoniy Tue, 21 Nov 2017, 13:18:46
Табличные пространства Oracle:...
Табличные пространства Oracle:... 9370 просмотров Александров Попков Fri, 06 Jul 2018, 06:29:19
Войдите чтобы комментировать