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