Шаг 128 - БД Oracle - Работа с табличными пространствами в целом

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


Предыдущий Шаг | Следующий Шаг | Оглавление
Автор Летучий Сергей.