Шаг 126 - БД Oracle - Дефрагментация и борьба с ней!

Как же все же решить проблему дефрагментации табличного пространства? Существует несколько способов. Мы постепенно рассмотрим каждый и попробуем понять, как это работает и на что действует. А начнем с дефрагментации свободных экстентов. Свободный экстент в табличном пространстве представляет собой набор смежных блоков. После удаления сегмента его экстенты помечаются как свободные. Однако они не всегда объединяются с соседними свободными экстентами. Между ними могут быть барьеры. А, дело в следующем, если значение параметра pcincrease по умолчанию для табличного пространства не равно нулю, то фоновый процесс SMON - периодически объединяет соседние свободные экстенты. Если же pcincrease = 0, то БД не будет объединять свободное место в табличном пространстве. Для слияния соседних свободных экстентов можно использовать параметр COALESCE команды ALTER TABLESPACE! В этом случае слияние произойдет независимо от параметра pcincrease. Кстати для заметки, процесс SMON осуществляет слияние в тех табличных пространствах, в которых значение параметра pcincrease по умолчанию не равно нулю! А вот pcincrease = 1 заставит SMON объединять смежные области свободного места в табличном пространстве не оказывая особого влияния на размер следующего экстента! Вот такие дела! Так что, только в идеале каждый обьект БД находится только в одном экстенте и все доступное свободное место на диске расположено в одном большом непрерывном экстенте! Но, это только в идеале! :) Для оценки фрагментации табличного пространства основным показателем является размер самого большого свободного экстента, выраженный в процентах от общего свободного места (т.е. на сколько БД близка к идеалу). Число полученное для каждого табличного пространства называется "индексом фрагментации свободного места" (free space fragmentation index - FSFI). Можно развить этот индекс, уделив внимание другим критериям. Заметьте, что индекс учитывает не общий объем доступного свободного места на диске, а только его структуру! Итак, выглядит это так:

FSFI = 100 * sqrt( размер самого большого экстента / сумма всех экстентов ) * 
	1 /( число экстентов )^1/4

Запомните эту формулу, она вам пригодиться еще в жизни! :) Наибольшее значение FSFI (для идеального табличного пространства содержащего только один файл данных) равно 100 (у меня было и больше!) По мере роста количества экстентов значение FSFI медленно снижается. С уменьшением размера самого большого экстента значение FSFI падает очень быстро! Давайте посмотрим, как это все работает на практике, вот, например, что я получил для своей БД, которая у меня развернута дома:

-- ****************************************
--      Letuchiy S.V. @ By Presents       *
-- ****************************************
--                                        *
--        Calculate FSFI index.           *
--                                        *
--         DATA BASE "MONOLIT"            *
--                                        *
-- ****************************************

SET NEWPAGE 0 PAGESIZE 60
COLUMN FSFI FORMAT 999.99

SELECT TABLESPACE_NAME,
	SQRT(MAX(BLOCKS)/SUM(BLOCKS))+
	(100/SQRT(SQRT(COUNT(BLOCKS)))) FSFI
FROM DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME
ORDER BY 1
/

Получаем:

SQL> -- ****************************************
SQL> --      Letuchiy S.V. @ By Presents       *
SQL> -- ****************************************
SQL> --                                        *
SQL> -- Calculate FSFI index.                  *
SQL> --                                        *
SQL> --                                        *
SQL> -- DATA BASE "MONOLIT"                    *
SQL> --                                        *
SQL> -- ****************************************
SQL> 
SQL> SET NEWPAGE 0 PAGESIZE 60
SQL> COLUMN FSFI FORMAT 999.99
SQL> 
SQL> SELECT TABLESPACE_NAME,
  2   SQRT(MAX(BLOCKS)/SUM(BLOCKS))+
  3   (100/SQRT(SQRT(COUNT(BLOCKS)))) FSFI
  4  FROM DBA_FREE_SPACE
  5  GROUP BY TABLESPACE_NAME
  6  ORDER BY 1
  7  /
TABLESPACE_NAME                FSFI
------------------------------ -------
CWMLITE                          85.08
DRSYS                           101.00
EXAMPLE                         101.00
INDX                            101.00
ODM                             101.00
OEM_REPOSITORY                  101.00
PROBATBS                         84.91
SYSTEM                          101.00
TOOLS                           101.00
UNDOTBS1                         47.68
USERS                           101.00
XDB                             101.00

12 строк выбрано.

Этот сценарий я использую уже давно, и по этому в нем остался заголовок. Думаю вы не в обиде на мое тщеславие! :) Но главное, чтобы вы поняли, как это определяется и, что делать дальше! Имея значение FSFI базы данных можно определить базовую линию. Хотя я думаю вы не так часто, будете сталкиваться с проблемами доступности свободного места в табличном пространстве и имеющего адекватный объем этого свободного места и коэффициент FSFI более 30. А вот для того, чтобы определить распределение свободных экстентов и их размеры, а так же, чтобы определить какие объекты являются барьерами между свободными экстентами, запустите следующий сценарий:

set pagesize 60 linesize 132 verify off
column file_id heading "File|Id"

select
      'free space' Owner,
      '   '  Object,
      File_ID,
      Block_ID,
      Blocks
 from DBA_FREE_SPACE
where Tablespace_Name = 'USERS'
and Owner = 'MILLER'
union

select
      SUBSTR(Owner,1,20),
      SUBSTR(Segment_Name,1,32),
      File_ID,
      Block_ID,
      Blocks
 from DBA_EXTENTS
where Tablespace_Name = 'USERS'
and Owner = 'MILLER'
order by 3,4
/

Получаем:

SQL> set pagesize 60 linesize 132 verify off
SQL> column file_id heading "File|Id"
SQL> 
SQL> select
  2        'free space' Owner,
  3        '   '  Object,
  4        File_ID,
  5        Block_ID,
  6        Blocks
  7   from DBA_FREE_SPACE
  8  where Tablespace_Name = 'USERS'
  9  and Owner = 'MILLER'
 10  union
 11  
SQL> select
  2        SUBSTR(Owner,1,20),
  3        SUBSTR(Segment_Name,1,32),
  4        File_ID,
  5        Block_ID,
  6        Blocks
  7   from DBA_EXTENTS
  8  where Tablespace_Name = 'USERS'
  9  and Owner = 'MILLER'
 10  order by 3,4
 11  /
                                                File
SUBSTR(OWNER,1,20)   SUBSTR(SEGMENT_NAME,1,32)    Id BLOCK_ID BLOCKS
-------------------- ------------------------- ----- -------- ------
MILLER               BOYS                          9        9      8
MILLER               BOYS                          9       17      8
MILLER               SYS_C003505                   9       25      8
MILLER               SYS_C003505                   9       33      8
MILLER               CUSTOMERS                     9       41      8
MILLER               CUSTOMERS                     9       49      8
MILLER               SYS_C003506                   9       57      8
MILLER               SYS_C003506                   9       65      8
MILLER               GIRLS                         9       73      8
MILLER               GIRLS                         9       81      8
MILLER               SYS_C003507                   9       89      8
MILLER               SYS_C003507                   9       97      8
MILLER               OFFICES                       9      105      8
MILLER               OFFICES                       9      113      8
MILLER               SYS_C003511                   9      121      8
MILLER               SYS_C003511                   9      129      8
MILLER               ORDERS                        9      137      8
MILLER               ORDERS                        9      145      8
MILLER               SYS_C003512                   9      153      8
MILLER               SYS_C003512                   9      161      8
MILLER               PRODUCTS                      9      169      8
MILLER               PRODUCTS                      9      177      8
MILLER               SYS_C003513                   9      185      8
MILLER               SYS_C003513                   9      193      8
MILLER               SALESREPS                     9      201      8
MILLER               SALESREPS                     9      209      8
MILLER               SYS_C003515                   9      217      8
MILLER               SYS_C003515                   9      225      8
MILLER               TBLA                          9    74537      8
MILLER               ADT                           9    74545      8
MILLER               TSTTRIG                       9    74553      8
MILLER               SYS_C003550                   9    74561      8
MILLER               TSTSV                         9    74569      8
MILLER               SYS_C003551                   9    74577      8
MILLER               MYAUDIT                       9    75961      8

35 строк выбрано.

Как видно здесь нет записей вида free space, но это не так важно, главное что пользователь MILLER их пока не имеет, но думаю в дальнейшем мы устроим такую возможность нашему тренировочному пользователю! С оценками FSFI мы разобрались, далее будем решать проблемы собственно устранения самой дефрагментации. Так что, попробуйте поработать с вашим табличным пространством, например, создавая и очищая несколько таблиц! Пробуйте!


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