(495) 925-0049, ITShop интернет-магазин 229-0436, Учебный Центр 925-0049
  Главная страница Карта сайта Контакты
Поиск
Вход
Регистрация
Рассылки сайта
 
 
 
 
 

“Невидимые миру дыры” в табличных пространствах 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);
  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_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 объектов с наибольшей длиной списка свободных блоков. Возможны и другие усовершенствования.



 Распечатать »
 Правила публикации »
  Обсудить материал в конференции Oracle »
Написать редактору 
 Рекомендовать » Дата публикации: 22.01.2001 
 

Магазин программного обеспечения   WWW.ITSHOP.RU
Oracle Database Personal Edition Named User Plus License
Oracle Database Standard Edition 2 Processor License
Oracle Database Standard Edition 2 Named User Plus License
Oracle Database Personal Edition Named User Plus Software Update License & Support
IBM DOMINO COLLABORATION EXPRESS AUTHORIZED USER ANNUAL SW SUBSCRIPTION & SUPPORT RENEWAL
 
Другие предложения...
 
Курсы обучения   WWW.ITSHOP.RU
 
Другие предложения...
 
Магазин сертификационных экзаменов   WWW.ITSHOP.RU
 
Другие предложения...
 
3D Принтеры | 3D Печать   WWW.ITSHOP.RU
 
Другие предложения...
 
Новости по теме
 
Рассылки Subscribe.ru
Информационные технологии: CASE, RAD, ERP, OLAP
Новости ITShop.ru - ПО, книги, документация, курсы обучения
Программирование на Microsoft Access
CASE-технологии
OS Linux для начинающих. Новости + статьи + обзоры + ссылки
СУБД Oracle "с нуля"
Все о PHP и даже больше
 
Статьи по теме
 
Новинки каталога Download
 
Исходники
 
Документация
 
Обсуждения в форумах
Сайт инструмент (1)
Я бывший программист пользовался 1 сайтом проверенным он мне действительно помог я блогодоря...
 
Где взять лицензионный ключ для AllFusion Process Modeler (BPwin) 7? (5)
Выручайте!!! где найти ключ, ужасно срочно нужна программа. заранее спасибо!
 
работа на дому! (5)
Доброго времени суток дорогие друзья. Многоуровневый маркетинг окончательно признан...
 
Регистрация на Oracle.com (4)
Сразу прошу прощения за тупой вопрос, но вчера зарегался на oracle.com (чтоб 9i слить себе...
 
Ищу кодера (2)
Добрый день! Ищу кодера который сможет сделать копии сайтов. Сколько будет стоить скопировать...
 
 
 



    
rambler's top100 Rambler's Top100