В прошлый раз я упустил один момент, давайте еще не надолго вернемся к дефрагментации. Это достаточно обширная тема, но думаю последнее, на что стоит обратить внимание это размер блока 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, если в других экстентах данных нет! Так, что пользуйтесь возможностью борьбы с неиспользуемым свободным местом табличных пространств! Но, осторожно! Удачи!