Как же все же решить проблему дефрагментации табличного пространства? Существует несколько способов. Мы постепенно рассмотрим каждый и попробуем понять, как это работает и на что действует. А начнем с дефрагментации свободных экстентов. Свободный экстент в табличном пространстве представляет собой набор смежных блоков. После удаления сегмента его экстенты помечаются как свободные. Однако они не всегда объединяются с соседними свободными экстентами. Между ними могут быть барьеры. А, дело в следующем, если значение параметра 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 мы разобрались, далее будем решать проблемы собственно устранения самой дефрагментации. Так что, попробуйте поработать с вашим табличным пространством, например, создавая и очищая несколько таблиц! Пробуйте!