В прошлый раз я упустил один момент, давайте еще не надолго вернемся к дефрагментации. Это достаточно обширная тема, но думаю последнее, на что стоит обратить внимание это размер блока Oracle. Он содержатся в файле init.ora в секции db_block_size и имеет, как правило, оптимальное значение выбранное по умолчанию. Но эффект от увеличения размера блок просто поражает! В большинстве случаев используют блоки двух размеров 2 и 4 Кбт. (Хотя я почти всегда ставлю 8 Кбт!). Переход на больший размер блока может повысить производительность на 50%! И достигается это без значительных затрат! Учтите, что менять секцию db_block_size просто так нельзя! Для увеличения размера блока БД лучше пересоздать весь экземпляр заново с новым значением! Повышение производительности связано со способом работы сервера Oracle с заголовком блока. Как следствие для данных используется больше места, что улучшает возможность обращения к одному и тому же блоку данных, от нескольких пользователей. Удвоение размера блока Oracle практически не влияет на его заголовок. Это значит, что в процентном отношении для заголовка расходуется меньше места! Но учтите, что, например, удвоение размера блока Oracle так же будет влиять на кэш буфера данных и может вызвать проблемы с управлением памятью на сервере!
Теперь давайте рассмотрим момент, когда табличное пространство необходимо модифицировать в ту или иную сторону. Например, рассмотрим случай когда табличное пространство и связанный с ним файл данных необходимо усечь в размерах! Сделать это можно, например, с помощью команды ALTER DATABASE. Но учтите, что нельзя изменить размер файла данных, если пространство, которое вы пытаетесь освободить, в настоящий момент занято объектами БД. Например, если объекты БД занимают объем 200 Мб, а размер файла данных 300 Мб, то можно отсечь только 100 Мб у файла данных! Сама команда будет выглядеть вот так:
ALTER DATABASE DATAFILE 'C:\Oracle\Oradata\PROBA\PRBONE.DAT' RESIZE 100M /
При этом учтите, если табличное пространство сильно дефрагментировано, то Oracle может выдать ошибку при попытке усечь табличное пространство! Далее давайте посмотрим как можно производить сокращение таблиц и индексов в БД. Но, для начала проделаем следующее. Создадим таблицу SPEED в схеме MILLER:
CREATE TABLE SPEED ( ID NUMBER, DT VARCHAR2(100) ) /
Получаем:
SQL> CREATE TABLE SPEED ( 2 ID NUMBER, 3 DT VARCHAR2(100) 4 ) 5 / Таблица создана.
Теперь запишем вот такой блок для того, чтобы наполнить таблицу данными! Для примера:
SET SERVEROUTPUT ON DECLARE X NUMBER := 0; BEGIN DBMS_OUTPUT.enable; FOR X IN 0..5000 LOOP INSERT INTO SPEED(ID, DT) VALUES(X, 'Hello '||TO_CHAR(X)); END LOOP; DBMS_OUTPUT.put_line('Good Job!'); END; /
Получаем:
SQL> SET SERVEROUTPUT ON SQL> DECLARE 2 3 X NUMBER := 0; 4 5 BEGIN 6 7 DBMS_OUTPUT.enable; 8 9 FOR X IN 0..5000 LOOP 10 11 INSERT INTO SPEED(ID, DT) 12 VALUES(X, 'Hello '||TO_CHAR(X)); 13 14 END LOOP; 15 16 DBMS_OUTPUT.put_line('Good Job!'); 17 18 END; 19 / Good Job!
Время, которое потратил Oracle в моем случае составило 2,5 Сек. (Это оценивает PL/SQL Developer). Когда Oracle записывает данные в сегмент, обновляется так называемая - верхняя отметка (high - water mark - высшая точка) сегмента. Верхняя отметка сегмента - это наибольший номер блока сегмента, в котором вы когда-либо хранили данные. Если вы добавили скажем 5000 строк верхняя отметка будет увеличиваться! Дайте к таблице SPEED вот такой запрос:
SELECT COUNT(*) FROM SPEED /
Время на исполнение у меня было 0.016 сек. Хорошо. Запрос прошел все блоки таблицы до верхней отметки. А теперь удалим записи:
DELETE FROM SPEED / COMMIT /
Время на удаление чуть больше, уже 0.235 сек! А теперь повторите прошлый запрос:
SELECT COUNT(*) FROM SPEED /
Снова 0.016 сек! Но почему? А в следствии того, что при удалении записей из таблицы ее high - water mark не снижается и запрос прошел все блоки снова! Вот как! Если не считать удаление таблицы и ее воссоздание, верхняя отметка сегмента переустанавливается только после команды TRUNCATE TABLE (к ней мы еще вернемся!) Давайте проделаем следующее. Снова наполним таблицу:
SQL> SET SERVEROUTPUT ON SQL> DECLARE 2 3 X NUMBER := 0; 4 5 BEGIN 6 7 DBMS_OUTPUT.enable; 8 9 FOR X IN 0..5000 LOOP 10 11 INSERT INTO SPEED(ID, DT) 12 VALUES(X, 'Hello '||TO_CHAR(X)); 13 14 END LOOP; 15 16 DBMS_OUTPUT.put_line('Good Job!'); 17 18 END; 19 / Good Job!
А теперь дадим команду нашего запроса:
SELECT COUNT(*) FROM SPEED /
Время снова примерно 0.017 сек. Хорошо, даем вот такую команду:
TRUNCATE TABLE SPEED /
Получаем:
SQL> TRUNCATE TABLE SPEED 2 / Таблица усечена.
Снова запрос:
SELECT COUNT(*) FROM SPEED /
Получаем:
SQL> SELECT COUNT(*) FROM SPEED 2 / COUNT(*) ---------- 0
Затраченное время 0 сек! Указатель high - water mark был перемещен! Что и требовалось доказать! Здесь так же кроется некий подводный камень, при работе с таблицами БД и особенно большими таблицами! Знание этого нюанса думаю в дальнейшем поможет вам справляться с распределением табличного пространства под объекты БД. Найти верхнюю отметку для таблицы CUSTOMERS для схемы MILLER нашей учебной БД поможет такой сценарий (для того, чтобы все получилось необходимо зайти в экземпляр пользователем SYS или SYSTEM!):
SET SERVEROUTPUT ON declare VAR1 number; VAR2 number; VAR3 number; VAR4 number; VAR5 number; VAR6 number; VAR7 number; begin DBMS_OUTPUT.enable; SYS.dbms_space.unused_space('MILLER','CUSTOMERS','TABLE', VAR1,VAR2,VAR3,VAR4,VAR5,VAR6,VAR7); dbms_output.put_line('OBJECT_NAME = SPACES'); dbms_output.put_line('---------------------------'); dbms_output.put_line('TOTAL_BLOCKS = '||VAR1); dbms_output.put_line('TOTAL_BYTES = '||VAR2); dbms_output.put_line('UNUSED_BLOCKS = '||VAR3); dbms_output.put_line('UNUSED_BYTES = '||VAR4); dbms_output.put_line('LAST_USED_EXTENT_FILE_ID = '||VAR5); dbms_output.put_line('LAST_USED_EXTENT_BLOCK_ID = '||VAR6); dbms_output.put_line('LAST_USED_BLOCK = '||VAR7); end; /
Здесь используется пакет SYS.dbms_space и его метод unused_space! Получаем:
SQL> SET SERVEROUTPUT ON SQL> SQL> declare 2 3 VAR1 number; 4 VAR2 number; 5 VAR3 number; 6 VAR4 number; 7 VAR5 number; 8 VAR6 number; 9 VAR7 number; 10 begin 11 12 DBMS_OUTPUT.enable; 13 14 SYS.dbms_space.unused_space('MILLER','CUSTOMERS','TABLE', 15 VAR1,VAR2,VAR3,VAR4,VAR5,VAR6,VAR7); 16 dbms_output.put_line('OBJECT_NAME = SPACES'); 17 dbms_output.put_line('---------------------------'); 18 dbms_output.put_line('TOTAL_BLOCKS = '||VAR1); 19 dbms_output.put_line('TOTAL_BYTES = '||VAR2); 20 dbms_output.put_line('UNUSED_BLOCKS = '||VAR3); 21 dbms_output.put_line('UNUSED_BYTES = '||VAR4); 22 dbms_output.put_line('LAST_USED_EXTENT_FILE_ID = '||VAR5); 23 dbms_output.put_line('LAST_USED_EXTENT_BLOCK_ID = '||VAR6); 24 dbms_output.put_line('LAST_USED_BLOCK = '||VAR7); 25 end; 26 / OBJECT_NAME = SPACES --------------------------- TOTAL_BLOCKS = 16 TOTAL_BYTES = 131072 UNUSED_BLOCKS = 8 UNUSED_BYTES = 65536 LAST_USED_EXTENT_FILE_ID = 9 LAST_USED_EXTENT_BLOCK_ID = 40 LAST_USED_BLOCK = 8 Процедура PL/SQL успешно завершена.
Здесь верхняя отметка таблицы (в байтах) представляет собой разницу между значениями TOTAL_BYTES и UNUSED_BYTES. Значение UNUSED_BLOCKS соответствует числу блоков выше высшей точки. TOTAL_BLOCKS это общее количество блоков связанное с данной таблицей! Улавливаете! Если нужно сжать таблицу и значение UNUSED_BLOCKS не равно нулю, с помощью команды ALTER TABLE можно забрать пространство выше верхней отметки. Чтобы освободить занимаемое таблицей пространство можно дать команду:
ALTER TABLE MILLER.CUSTOMERS DEALLOCATE UNUSED KEEP 65536 /
Получаем:
SQL> ALTER TABLE MILLER.CUSTOMERS DEALLOCATE UNUSED KEEP 65536 2 / Таблица изменена.
И действительно зачем ей лишние 8 блоков! У меня это получается (16 * 8192) - (8 * 8192) = 65536! Вот так лишнее долой! Кстати, если не указать конструкцию keep, то значение параметров сохранения minextents и initial таблицы останутся прежними. Если использовать keep, то можно освобождать свободное пространство из любого экстента! Даже из initial, если в других экстентах данных нет! Так, что пользуйтесь возможностью борьбы с неиспользуемым свободным местом табличных пространств! Но, осторожно! Удачи!