Бакшар Шиматсингка и
Джоан Лойаза,
корпорация Oracle

Устраните фрагментацию и начните жизнь заново:
прощальное слово о фрагментации
(HOW TO STOP DEFRAGMENTING AND START LIVING:
THE DEFINITIVE WORD ON FRAGMENTATION,
by Bhaskar Himatsingka, Oracle Corporation & Juan Loaiza, Oracle Corporation)

Источник: www.oracle.com/technology/deploy/availability/pdf/defrag.pdf

[На первый взгляд, эта статья уже давно устарела. Она (цитата) "основана на Oracle7.3, но также охватывает новые возможности, представленные в Oracle8.0". Так я и подумал, когда занимался дефрагментацией и миграцией некоей базы данных с Oracle8.1.7 на Oracle10g и мне предложили познакомиться с этой статья в том плане, в ней уже все изложено. Но "моя" база данных Oracle8.1.7 была настолько наполнена фрагментированными и неправильно построенными сегментами, что мне захотелось рассказать о своем опыте преодоления фрагментации, об опыте как бы "расчистки" базы данных Oracle8i перед миграцией ее под управление СУБД Oracle10g. Это я постарался изложить в отдельной статье "Заметки об преодолении фрагментации при переходе на Oracle10g", которая публикуется в этом же выпуске журнала.

Работая над своей статьей, я понял, что мне надо практически полностью перевторить статью Б.Шиматсингка и Д.Лойаза. Поэтому лучшим выходом было заказать и опубликовать этот перевод, а в своем опусе постараться следовать заключительным словам авторов: "Мы рекомендуем АБД Oracle далее обогащать этот документ своими собственными приемами и методами."

Несколько слов о сокращениях текста исходного источника. Изъятия в тексте касаются в основном дефрагментации табличных пространств TEMP и ROLLBACK. Информация об этом, на мой взгляд, устарела окончательно, и соответствующие фрагменты текста были изъяты.

Эта статья в первую очередь предназначена для начинающих и еще не очень опытных АБД, чтобы навести их на мысль более внимательно присмотреться к структуре и фрагментации памяти табличных пространств своих баз данных. Опытным же АБД Oracle она действительно может показаться устаревшей, поскольку они уже давно выполнили изложенные в ней рекомендации.

Анатолий Бачин,
профессиональный АБД Oracle,
главный редактор "Oracle Magazine/Русское Издание"]

Абстракт

Проблема фрагментации [имеется в виду физическая фрагментация данных в базе в понятиях структр размещения данных – блочная, экстентная, файловая фрагментация – прим. А.Бачина] всегда представляла большой интерес для АБД (администраторов баз данных). Oracle включает много команд, опций, представлений, средств статистики, которые выявляют различные аспекты фрагментации, но не всегда очевидно, как эти механизмы должны использоваться при ежедневном администрировании. Также имеется много частной и устаревшей, а также неверной информации, которая была собрана о некоем предмете, что тоже может смущать и вводить в заблуждение администраторов.

Большая часть этого материала была написана до выхода Oracle7.3. Новые функциональные возможности в Oracle7.3 сокращают фрагментацию и позволяют применять различные более простые и более эффективные методы. Эта статья описывает механизмы Oracle, связанные с фрагментацией, и приводит рекомендации, как они должны примениться. Эта статья предназначена для опытных АБД. Она основана на Oracle7 выпуск 7.3, но также охватывает новые возможности, представленные в Oracle8 версии 8.0.

1. Введение

Oracle управляет дисковым пространством как набором пользовательских файлов, называемых файлами данных.

Схемные объекты, которые занимают место на диске, например таблицы, индексы, кластеры и т.д., состоят из наборов блоков. Для обеспечения производительности и управляемости в Oracle введено понятие сегмента, который является собранием блоков, принадлежащих объекту схемы.

Многие сегменты ассигнованы схемному объекту по принципу горизонтального секционирования (horizontally partitioned) – использование диапазона (using range), случайное размещение (hash) и т.д., или же по вертикальному принципу - большие объекты (large objects), коллекции (collections) или сегменты переполнения (overflow segment) [В частности, для индекс-организованных таблиц– прим. А.Бачина].

Oracle группирует файлы данных в логические объекты, называемые табличными пространствами. Табличное пространства обеспечивают разделение места диска между различными объектами базы данных, гарантируя, что все блоки, принадлежащие одному сегменту, будут расперделены только в файлах одного табличное пространства, то есть сегмент локален по отношению к табличному пространству.

Таким образом, база данных может быть разделена на много табличных пространств, учитывающих различные требования к наборам данных, составляющих базу данных. Сами же схемные объекты могут быть расклассифицированы по требованиям к производительности и управляемости. Конкретные секции (partitions) могут быть помещен в различные табличные пространства, как не существует какого-либо требования, чтобы все сегменты схемного объекта находились в одном и том же самом табличном пространстве. Более того, при управлении очень большими таблицами Oracle рекомендуют помещать их сегменты в различные табличные пространства. В следующих секциях мы рассмотрим, как Oracle осуществляет управление пространством таких объектов. В секции 2 мы рассматривам некоторые простые методы управления, которые могут использоваться, чтобы избежать фрагментации на уровне табличных пространств. В секции 3 рассматриваются проблемы фрагментации обычных [неупорядоченных] таблиц (heap – куча, навал) и приводятся простые шаги для контроля над фрагментацией таких таблиц. В секции 4 подобные проблемы обсуждаются для B*Tree (индексов). Секция 5 повествует об использовании секционирования как средства для устранения фрагментации. Наконец, мы завершаем эту статью резюме по результатам всего обсуждения.

2. Управление табличными пространствами

Как было сказано в предыдущем разделе, табличное пространство - это набор файлов данных, которые предоставляют место диска (в виде блоков), которое может использовать Oracle. Схемные объекты занимают место в табличных пространствах, используя понятие сегментов, каждый из которых является набором блоков, принадлежащих к одному и тому же табличное пространству. В табличном пространстве могут находиться несколько [много и даже слишком много! – А.Б.] сегментов, и эти сегменты могут принадлежать одной и той же или же разным схемным объектам.

Так в чем же причина фрагментации? Число блоков, принадлежащих сегменту, может быть весьма большим. Чтобы более эффективно управлять доступом к этим блокам, Oracle группирует их в виде экстентов. Экстент – это набор смежных блоков в файле. Следовательно, сегмент в конечном итоге – это собрание экстентов. Преимущества распределения места по экстентам, а не по отдельным блокам в следующем:

• Эффективность распределения – экстент - очень эффективный способ определить большое количество блоков, поскольку в список экстентов по каждому из них вносится только [номер] блок начала и его длина [количество смежных блоков], а не список всех блоков. Время, которое требуется для ассигнования и освобождения места во внутренней памяти Oracle, пропорционально числу экстентов, а не числу блоков. Поэтому большие сегменты, состоящие из тысяч или миллионов блоков, могут быть быстро ассигнованы и освобождены, потому что блоки могут быть организованы в небольшое число экстентов.
• Эффективность сканирования – тот факт, что блоки в экстентах являются смежными, позволяет Oracle при просмотре сегментов выдавать очень эффективные операции, читающие сразу много блоков.

Неудобство использования экстентов в том, что табличное пространство может стать фрагментированным.

• Экстентная фрагментация – этот тип фрагментации имеет место на уровне табличных пространств между экстентами. Табличное пространство фрагментировано, когда в нем имеется достаточно места, чтобы удовлетворить запрос [на выделение нового участка паяти для сегмента], но память представлена в виде нескольких свободных экстентов, никакой из которых не может быть использован для удовлетворения запроса. Например, табличное пространство может содержать 100 свободных экстентов по 10 мегабайтов каждый, но если все они не смежны, то запрос на выделение экстента в 40МВ не может быть удовлетворен даже притом, что в нем суммарно имеется гигабайт свободного пространства. Это и есть пример экстентной фрагментации. В этом случае пользователь может или уменьшать желательный размер экстента или переместить данные (экстенты), чтобы сделать доступными смежные свободные участки. Ни один из этих приемов не вызывает радости. Наилучшее решение в том, чтобы изначала не допустить фрагментации.

2.1. Устранение экстентной фрагментации, используя SAFE

Oracle обеспечивает АБД чрезвычайной гибкостью при оределении размеров экстентов, которые размещены в сегментах. В такой мощной гибкости спецификации почти никогда нет нужды, и в большинстве случаев этого следует избегать. Применяя следующий набор простыъ административных правил, экстентная фрагментация может быть полностью устранена. Мы будем использовать термин SAFE, чтобы обратиться к этим правилам. SAFE - это Простой Алгоритм для Устранения Фрагментации (Simple Algorithm for Fragmentation Elimination). SAFE состоит из набора правил, многие из которых были разработаны и реализованы как часть возможности локального управления табличными пространствами, реализованной в Oracle8i.

2.1.1. Использование одинаковых размеров экстентов

Все сегменты в табличном пространстве должны иметь одинаковые размеры экстентов. Это гарантирует, что любой свободный экстент в табличном пространстве может всегда использоваться для любого сегмента в этом табличном пространстве. Таким образом, никогда не будет такого маленького экстента, что его нельзя будет пустить в дело. Это подразумевает, что значение NEXT во всех storage- предложениях должно быть установлено таким же, как значение INITIAL, а значение PCTINCREASE всегда должно быть нулем.

Чтобы обеспечить однородность размеров экстентов, Oracle8 добавил параметр ‘MINIMUM EXTENT’ ' в команду Create Tablespace.

Определение значения MINIMUM EXTENT гарантирует, что размеры всех размещенныех в табличном пространстве экстентов кратны этому значению. Если пользователь размещает экстент с некратным размером, Oracle автоматически округляет его до кратного значения MINIMUM EXTENT. Также, если экстент урезается, например, в результате параллельной прямой загрузки (parallel direct load), то и оставшийся и урезанный размер будут установлены в кратное число MINIMUM EXTENT. Всегда определяйте значение MINIMUM EXTENT равным значению INITIAL, чтобы гарантировать однородные размеры экстентов.

2.1.2. Определение параметров распределения памяти только на уровне табличного пространства

Чтобы гарантировать, что все сегменты в пределах табличного пространства имеют одинаковый размер экстентов, никогда не надо явно определить параметры распределения памяти (storage clause) на уровне сегмента. Надо позволить значениям памяти хранения всех сегментов унаследоваться из аналогичных параметров табличного пространства. Всегда надо явно установить параметры распределения памяти на уровне табличных пространств. Если требуется, чтобы сегмент при создании имел гарантируемое количество дисковой памяти, надо определить параметр MINEXTENTS при создании сегмента.

Тем самым будет выделено необходимое число одинаковых экстентов (в том же табличном пространстве) для этого сегмента. [Заодно и проверим, поместится ли размещаемый сегмент в это табличное пространство – А.Б.]

Предостережение: Когда сегмент экспортируется, обычно параметры хранения задаются для полного экспорта. Тем самым, при экспорте сегменты из одного табличного пространства в другое, когда они имеют различные размеры экстентов, это правило нарушается. В Oracle нет никакого простого способа, чтобы справиться с этой проблемой. Мы рекомендуем не использовать опцию экспорта compress=y с тем, чтобы эта проблема возникала только для табличных пространств с различными размерами экстентов. Когда сегмент перемещается с нарушением размеров экстентов, единственный БЕЗОПАСНЫЙ способ состоит в том, чтобы заранее создать [пустой] сегмент, а затем выполнить ИМПОРТ с опцией ignore=y.

2.1.3 Размер экстентов для всех табличных пространств данных должен быть: 160К, 5120К или 160M

Эти три значения размеров экстентов - все, в чем вы когда-либо будете нуждаться для таблиц и индексов. Использование других размеров экстентов кардинально не улучшит поизводительность или использование пространства, но усложнит администрирование и потенциально стимулирует фрагментацию.

Самый малый размер экстента 160К является достаточно большим, чтобы делать эффективные просмотры. Это значение достаточно мало, чтобы износ (wastage) диска был незначителен для малых таблиц. С другой стороны, 160К – это все-таки большое дисковое пространство, которое пропадает впустую в сегментах, в которых очень малое количество данных. Однако на практике рабочие затраты, которые требуются для управления меньшими экстентами, не стоят малых сбережений в стоимости диска. Например, если база данных имеет десять тысяч крошечных таблиц с размером экстентов по 160КБ, то впустую будет потрачено максимум 1.6G диска. Стоимость большого диска будет искупаться стоимостью фрагментации и неэффективными просмотрами во время срока службы базы данных. Дополнительно, если сегменты содержат только несколько блоков, остающиеся [выделенные] блоки находятся выше ‘high water mark’ (высшая отметка заполнения сегмента) и поэтому не влияют на производительность при сканировании. Если ваша база данных не содержит много десятков тысяч очень маленьких сегментов, то не рекомендуется использовать размер экстента менее 160К.

Средний размер экстентиа в 5120К является достаточно большим, чтобы оперировать с почти самыми большими сегментами больших баз данных. Самый большой размер экстента в 160M является достаточно большим, чтобы эффективно оперировать с сегментами размером до сотен Гигабайтов. Как мы увидим позже, нужно всегда, когда возможно, избегать сегментов большего размера. Этот размер экстента должен использоваться только для самых больших таблиц, которые содержат много гигабайтов данных. Как правило, этот размер экстента будет главным образом использоваться для очень больших сегментов, которые не могут быть секционированы, или потому, что секционирование не доступно, или потому, что тип сегмента не позволяет его секционировать.

Когда вы создаете новый сегмент, нужно иметь достаточно грубую оценку окончательного размера сегментов, чтобы назначить им табличные пространства с соответствующими размерами экстентов. Следуйте этим трем простым правилам при помещении сегментов в табличные пространства:

1. сегменты, меньше чем 160M, должны быть помещены в табличные пространства с экстентами 160К.
2. сегменты между 160M и 5120M должны быть помещены в табличные пространства с экстентами 5120К.
3. сегменты, больше чем 5120M, должны быть помещены в табличные пространства с экстентами 160M.

Эти размеры экстентов выбраны как кратные к пяти блокам, поскольку Oracle7 округляет размеры экстентов к кратному до пяти блоков. В Oracle8 экстенты не будут округлены к кратному до пяти блоков, если они кратны к минимальному размеру экстента для табличного пространства. В базах данных Oracle8 правила выбора следующих размеров экстентов еще немного более просты:

1. сегменты, меньше чем 128M, должны быть помещены в табличные пространства с экстентами 128К.
2. сегменты между 128M и 4G должны быть помещены в табличные пространства с экстентами в 4M.
3. сегменты, больше чем 4G, должны быть помещены в табличные пространства с экстентами 128M.

Для конца этой статьи мы будем полагать, что размеры экстентов Oracle7 установлены, как должно в обоих выпусках.

2.1.4. Отслеживайте и по-возможности перемещайте сегменты, имеющие больше чем 1024 экстентов

Oracle поддерживает неограниченное число экстентов в сегменте. Производительность DML-операций существенно не зависит от числа экстентов в сегменте. Однако некоторые DDL-операции типа удаления (dropping) или опустошения (truncating) сегментов чувствительны [очень!! – А.Б.] к числу экстентов. Оценка производительности показала, что несколько тысяч экстентов могут быть поддержаны Oracle без существенного влияния на производительность. Был определен разумный максимум – 4096 [экстентов].

Цель нашего рекомендуемого алгоритма состоит в том, чтобы держать число экстентов ниже 1024, который хорошо укладывается в диапазон, с которым Oracle эффективно работает. Когда сегмент расширяется до 1024 экстентов, он становится кандидатом на перемещение в следующее табличное пространство с большим размером экстента. Сегмент не обязательно должен быть перемещен немедленно или вообще перемещен. Сегмент может быть на пике своего устойчивого размера, даже если он включает несколько тысяч экстентов, и это должно быть сохранено. Вышесказанная рекомендация относится только к сегментам, которые разрастаются, которые должны целенаправлено и по возможности перемещены в табличные пространства с большими экстентами.

Три рекомендуемых размеров экстентов - 160К, 5120К и 160M отличаются друг от друга фактором 32. Поэтому перемещение сегмента с 1024 экстентами в следующее табличное пространство, большее по размеру экстентов, уменьшает число экстентов в сегменте в 32 раза. В 32 раза – это достаточно много, но потеря дискового пространства из-за неиспользованных блоков в сегменте будет не более 3% от полного объема сегмента, а средняя потеря - 1.5 %. Эта потеря достаточно мала, и ею можно практически пренебречь.

Существует общее неправильное представление, что выгоднее, если сегмент расположен в одном или малом числе экстентов. Реальное фактическое преимущество достигается, когда используются большие экстенты, а не малое числа экстентов. При использовавнии больших экстентов операции сканирования могут захватывать для чтения большие смежные участки диска. Размера экстента в 160К уже достаточно для большого ускорения этих операций. Экстенты размером в 5120К обеспечивают полное ускорение операций. К большинству сегментов доступ осуществляется только через индексы, так время сканирования таблицы полностью несоответствуюет ожиданиям.

Вспомним, что сегменты, использующие экстенты размером 120К, вероятно, совсем не велики, из чего следует, что если к ним идут частые обращения, они сохраняются в буферном кеше, и, таким образом, они не выбираются с диска. Иногда можно извлечь выгоду от неиспользованных блоков для лучшего времени сканирования, размещая сегменты, которые обычно используют экстенты 160К, в табличных пространствах с экстентами по 5120К. Однако это можно быть делать очень редко, так как такой прием редко существенно улучшает производительность.

Другой класс сегментов, которые могли бы быть помещены в табличное пространство с большими экстентами, это класс ‘volatile’ ('временных') сегментов. Временные сегменты - это сегменты, которые часто удаляются или опустошаются. Таблицы, которые содержат временные результаты, - пример временных сегментов. Так как временные сегменты часто порождают и освобождают экстенты, их производительность может быть слегка улучшена, если разместить их в табличных пространствах с большими экстентами и таким образом сократить общее число экстентов.

2.1.5 Максимальный размер единственного сегмента должен быть где-то между 4G и 128G

Очень большие сегменты становятся проблемой для производительности, управляемости и восстановления. Предел максимального размера сегмента зависит от инсталляции, от характеристик аппаратных средств ЭВМ и требований готовности базы данных. Этот предел находится в диапазоне от 4G до 128G. Сегменты, которым этого мало, должны быть разделены на несколько сегментов, когда это возможно. Табличное пространство с экстентами по 160M может содержать сегменты до 160G и содержать до 1024 экстентов на сегмент.

2.1.6. Очень большие таблицы и индексы должны быть помещены в отдельные табличные пространства

Очень большие таблицы и индексы становятся проблемой для повышения производительности, управляемости и восстановления. Определение, какая таблицы или индексы являются очень большими, меняется в зависимости от установки, в зависимости от производительности аппаратных средств ЭВМ и требуемой готовности базы данных.

В общем случае, этот размер где-то в диапазоне от 4G до 128G. Таблицы и индексы, которые являются очень большими, должны быть помещены в набор отдельных табличных пространств, которые не содержат какие-либо другие данные. Это рекомендуется даже тогда, если таблица или индекс разделены на сегменты, которые по размеру меньше чем 4G. Выполнение этого правила сделает управление более легким и позволит использовать в некоторых условиях восстановление табличное пространства по точке во времени (point in time recovery) в случае серьезной проблемы.

2.1.7. Временные сегменты должен быть сосредоточены во временных (TEMP) табличных пространствах

Временный сегмент - это сегмент, который автоматически создается Oracle, чтобы хранить промежуточные результаты. Временные сегменты создаются операциями сортировки, хеш-соединений и некоторыми типами операций по соблюдениию ссылочной целостности, которые сбрасывают на диск [промежуточные результаты]. Фраза TEMPORARY TABLESPACE в команде CREATE USER называет табличное пространство, которое используется, когда создается временный сегмент [для этого пользователя – А.Б.]. Это табличное пространство всегда должно быть табличным пространством, в котором не содержится никаких постоянных (permanent) данных, и оно по созданию является временным табличным пространством, что декларируется фразой TEMPORARY в команде CREATE TABLESPACE. Табличные пространства, объявленные как временные, используют специальные алгоритмы управления свои объемом, что позволяет более эффективно управлять временными сегментами и избежать фрагментации.

Предостережение: Oracle8 на уровне базы данных не определяет по умолчанию табличные пространства, как временные. Таким образом, для реализации SAFE обязательно, чтобы фраза TEMPORARY TABLESPACE всегда обязательно присутствовала в предложении CREATE USER всякий раз, когда создается новый пользователь. Временное табличное пространство для пользователей SYS и SYSTEM должно также быть изменено на временное табличное пространство базы данных.

2.1.8. Размещайте сегменты отката в табличных пространствах, предназначенных для сегментов отката

Сегменты отката должны быть помещены в табличные пространства, содержащие только сегменты отката. Это позволяет сегментам отката расширяться и сжиматься без того фрагментирования экстентов в других табличных пространствах. Более того, сегменты отката жизнено важны для функционирования Oracle и потому должны быть расположены на наиболее надежном дисковом устройстве. Кроме того, если когда-либо будет необходимо восстановление табличных пространств по точке во времени (point in time recovery), табличные пространства с сегментами отката должны быть восстановлены наряду с действительно требующими восстанавления табличными пространствами данных. По всем этим причинам настоятельно рекомендуется, чтобы для сегментов отката были созданы отдельные табличные пространства. Такие табличные пространства мы будем называть UNDO табличными пространствами.

2.1.9. Табличные пространства TEMP и UNDO должны содержать от 1024 и 4096 экстентов

Табличные пространства TEMP и UNDO предъявляют особые требования к распределению места. Число экстентов в этих табличных пространствах более важно, чем их размер. Число экстентов в этих табличных пространствах должно быть установлено на основании числа одновременных (concurrent – параллельные, конкурирующие за ресурсы) пользователей в базе данных. Это следует из того, что простаивающие (idle) приложения не используют сколько-нибудь места в этих табличные пространствах, но работающие (busy) приложения могут потребовать требуемый объем для каждого из одновременных пользователей. Иногда один пользователь может задействовать почти все место в табличных пространствах UNDO и/или TEMP, расширя свои потребности от одного сегмента [безусловно, экстента – А.Б.] до полного объема табличного пространства. В плане эффективности мы захотим сдержать число экстентов в таком сегменте не более 4096. В других случаях большому числу пользователей каждому потребуется небольшой объем памяти. В этом случае мы захотим использовать относительно небольшие экстенты с тем, чтобы можно было сделать много независимых ассигнований экстентов. Можно пожелать оставить место для роста [объема], когда первоначально определяется размер этих табличных пространств. Поэтому мы рекомендуем, чтобы число экстентов в табличных пространствах UNDO и TEMP было первоначально установлено 1024. Чтобы сделать это, установите размер экстента как полный объем табличного пространства, разделенный на 1024. Распределение на 1024 экстентов, которые будут размещены в табличном пространстве, эффективно и для отдельного большого пользователя и для многих более скромных пользователей.

Когда к табличным пространствам UNDO и TEMP добавляется большой дисковый объем, нужно проверить, не превышено ли в табличном пространстве число экстентов 4096. Если да, то следует, или добавлять новое табличное пространство, или увеличить размер экстентов существующего табличного пространства. Увеличение экстентов существующего табличного пространства выполняется следующим образом, изменяя для него параметры хранения:

ALTER TABLESPACE tbspc_name 
DEFAULT STORAGE (INITIAL new_extent_size  NEXT new_extent_size 
                                       PCTINCREASE 0  MAXEXTENTS 4096) 
MINIMUM EXTENT new_extent_size;

Если это табличное пространство UNDO, тогда нужно удалить (drop) существующие сегменты отката и пересоздать их так, чтобы использовался новый размер экстентов.

2.1.10. Никогда не размещайте данные пользователя в табличном пространстве SYSTEM

Табличное пространство SYSTEM содержит метаданные базы данных. Oracle настоятельно рекомендует, не создавать никаких схемных объектов других пользователей в этом табличном пространстве. Сегменты в этом табличном пространстве SYSTEM никогда не удаляются (dropped) или опустошаются (truncated). Поэтому в табличном пространстве SYSTEM не происходит фрагментация экстентов, если только пользователи не добавляют в него свои схемные пользовательские сегменты. Размеры экстентов для сегментов в табличном пространстве SYSTEM определяются Oracle так, чтобы гарантировать отсутствие множества крошечных сегментов.

Предостережение: Oracle8 не следует алгоритму SAFE для табличного пространства SYSTEM. Поэтому важно, чтобы сегменты пользователей не создавались в этом табличное пространстве, поскольку это (среди других проблем) безусловно приведет к фрагментации. Системные сегменты, которые имеют тенденцию к разрастанию, например, сегменты, которые содержат данные pl/sql-пакетов и т.д., следуют отчасти логике SAFE, и, следовательно, не должны стать причиной фрагментации в большинстве инсталляций.

2.1.11. Размеры файлов должны быть кратны размерам экстентов табличного пространства плюс 1 блок

Единственная возможная фрагментация экстентов в табличных пространствах с одинаковыми размерами экстентов - это фрагментация в конце каждого файла, если размер файлов не соответствует размеру экстента. Oracle использует первый блок каждого файла для поддержки внутренней системной информации. Поэтому при добавлении файлов к табличному пространству, надо удостовериться, что размер файла кратен размеру экстента табличного пространства плюс один блок. Таким образом, если АБД хочет создать табличное пространство с размером экстента 5120К и общим полезным объемом 200М, а размер блока базы данных равен 4К, то должна использоваться следующая формула (204804 = 200*1024 + 4)

CREATE TABLESPACE foo datafile ‘bar’ size 204804K;

Предостережение: Если используются “сырые” (raw device – неформатированное устройство) дисковые устройства, то размер “сырого” устройства должен быть равен желательному размеру файла плюс два блока. Это необходимо потому, что на некоторых платформах дополнительный блок в начале “сырого” устройства необходим Oracle. В вышеупомянутом примере “сырое” устройство должно быть по размеру больше или равно 204808К. Остальные элементы спецификации остаются теми же самыми. Да, мы понимаем, что концепция SAFE может раздражать время от времени.

Если для файла допускается возможность авторасширения, надо удостовериться, что максимальный размер [файла] кратен размеру экстента плюс 1 блок.

Значение по умолчанию для AUTOEXTEND NEXT - один блок.

[Прим.А.Бачина: обязательно обратите внимание на эту фразу, чтобы у Вас не загорелись диски, как было однажды на практике в одном известном мне случае, когда понадобилось расширить табличное пространство на несколько десятков мегабайтов, и Windows раз за разом перезаписывал расширяющийся каждый раз на 2К файл данных.]

Поэтому Oracle расширяет файлы только, когда необходимо и только настолько, чтобы разместить не менее чем еще один экстент. А так как экстенты одинаковы по размерам, файл всегда будет увеличиваться на один и тот же объем. Если параметр AUTOEXTEND устанавливается для файла явно, то значение NEXT должно быть кратно размеру экстента табличного пространства.

2.1.12. Никогда не дефрагментируйте табличные пространства с однородными экстентами (Uniform Extent Tablespace)

Если вы следуете концерции SAFE, то табличные пространства никогда не станут фрагментированными, и поэтому никогда не будет нужды в дефрагментации. Некоторые администраторы обеспокоены наличием свободных экстентов, которые находятся в середине файла. Они предпочитают иметь все свободное место в конце файлов в одной экстенте. Это абсолютно не нужно в случае табличное пространств с однородным размером экстентов. Никогда не нужно реорганизовать сегменты в табличном пространстве с однородным размером экстентов. Это только трата времени, усилий и производительности. Даже при том, что свободное место может быть распространено по всему табличному пространству, оно не менее пригодно к употреблению, чем когда имеется [большой свободный экстент] в конце файла. Администраторы, которые реорганизуют место в табличном пространстве с однородным размером экстентов, рискуют совершить ошибку, не обретя ни какой от этого выгоды. Как это ни парадоксально, истина в том, что табличное пространство, использующее одинаковые размеры экстентов, лучше управляется, чем табличное пространство с множественными размерами экстентов, объединяющее все свободное место в конце файлов, или табличное пространство, в котором свободные место опрыскивается на всем его протяжении. Oracle проактивно не объединяет несколько смежных свободных экстентов в один свободный экстент в табличных пространствах с параметром PCTINCREASE равным 0. Поэтому, если вы следуете правилам SAFE, то можно увидеть много свободных смежных друг с другом экстентов. Это не составляет никакой проблемы. Объединение свободных экстентов (coalescing) привело бы к расходу производительности, без какой бы то выгоды. Пока размер всех свободных экстентов кратен размеру экстента табличного пространства, оно не является фрагментированным. Oracle автоматически соединит любые свободные экстенты, когда потребуется удовлетворить распределение, так как объединение по требованию не коррелируется с параметром PCTINCREASE для этого табличного пространства.

2.2. Скрипты для осуществления и администрирования SAFE

2.2.1. Правила 1-3: Табличные пространства данных должны иметь однородные размеры экстентов (160К, 5120К, 160M)

Для создания табличных пространств с однородными размерами экстентов (uniform extent sizes) в 5120К. Размер файла данных задается несколько сложно, так как он должен быть определен в КилоБайтах, а не в МегаБайтах и дополнительно блок заголовка. Если требуется файл размером 100M плюс блок заголовка 4К, то получаем 1024*100+4 = 102404К. Размер блока заголовка такой же, как размер обычного блока базы данных. Также не желательно, чтобы файл вырос более чем 200M.

      CREATE TABLESPACE tbspc_name
      DATAFILE 'data_filename'  size  102404K  AUTOEXTEND ON MAXSIZE  204804K
       MINIMUM EXTENT 4M
           DEFAULT STORAGE   (INITIAL 4M NEXT 4M PCTINCREASE 0
           MINEXTENTS 1 MAXEXTENTS 4096);

Наличие параметра MAXEXTENTS, установленного в 4096, предупреждает потенциальные проблемы, которые могли бы случиться, если объект неожиданно начинает увеличиваться и растет до 10 тысяч экстентов. Он также предотвращает увеличение числа экстентов выше установленного максимума, что предотвращает недоступность сегментов из-за незначительных ошибок или ограничений по времени.

Чтобы обнаружить какие табличные пространства данных не отвечают правилам однородных экстентов, следует выполнить следующий запрос:

select tablespace_name, initial_extent, next_extent, pct_increase, min_extlen
from dba_tablespaces
where (initial_extent not in (160*1024, 5120*1024, 160*1024*1024)
or next_extent != initial_extent
or pct_increase != 0
or min_extlen != initial_extent)
and contents = 'PERMANENT'
and tablespace_name != 'SYSTEM'
and tablespace_name not in (select tablespace_name from dba_rollback_segs);

2.2.2. Правило 4: Выявите и переместите сегменты, потенциально имеющие больше чем 1024 экстентов

Чтобы выявить сегменты, имеющие более 1024 экстентов, следует выполнить следующй запрос:

select owner, segment_name, extents from dba_segments
where extents > 1024 and segment_type != 'TEMPORARY';

2.2.3. Правило 5: Максимальный размер отдельного сегмента должен быть между 4G и 128G

Чтобы выявить сегменты, которые больше 4G, следует выполнить следующй запрос:

select owner, segment_name, bytes from dba_segments
where bytes > 4*1024*1024*1024 and segment_type != 'TEMPORARY';

2.2.4. Правило 6: Очень большие таблицы и индексы должны быть помещены в отдельное табличное пространство

Чтобы найти таблицы и индексы, объем которых более 4G, следует выполнить следующй запрос:

select owner, segment_name, sum(bytes) from dba_segments
group by owner, segment_name
having sum(bytes) > 4*1024*1024*1024;

2.2.5. Правило 7: Временные сегменты должны находиться в табличном пространстве TEMP

Чтобы выявить пользователей, которые не установили свое TEMP TABLESPACE как табличное пространство типа TEMP, следует выполнить следующй запрос:

select username from dba_users, dba_tablespaces
where temporary_tablespace = tablespace_name
and contents != 'TEMPORARY';

2.2.6. Правило 8: Разместите сегменты отката в табличных пространствах, специально отведенных для сегментов отката

Чтобы выявить табличные пространства, которые содержат, и сегменты отката, и данные пользователей, следует выполнить следующй запрос:

select tablespace_name from dba_segments
where segment_type != 'ROLLBACK'
and tablespace_name != 'SYSTEM'
and tablespace_name in (select tablespace_name from dba_rollback_segs);

2.2.7. Правило 9: Табличные пространства TEMP и UNDO должны располагать от 1024 до 4096 экстентов

Чтобы подчиняются ли табличные пространства UNDO и TEMP правилам количества экстентов, следует выполнить следующй запрос:

select tablespace_name, initial_extent, next_extent, pct_increase, min_extent
from dba_tablespaces t,
(select tablespace_name tbspc,
sum(bytes) tbspc_sz,
count(*) num_files,
sum(bytes)/sum(blocks) blk_sz
from dba_data_files
group by tablespace_name) f
where
( initial_extent < (tbspc_sz - blk_sz * num_files) / 4096
or initial_extent > (tbspc_sz - blk_sz * num_files) / 1024
or next_extent != initial_extent
or pct_increase != 0
or min_extlen != initial_extent)
and tablespace_name = tbspc
and tablespace_name != 'SYSTEM'
and ( contents = 'TEMPORARY'
or tablespace_name in (select tablespace_name from dba_rollback_segs));

2.2.8. Правило 10: Никогда не разместите данные пользователей в табличноь пространстве SYSTEM

Чтобы идентифицировать данные пользователей в табличном пространстве SYSTEM, следует выполнить следующй запрос:

select owner, segment_name from dba_segments
where tablespace_name = 'SYSTEM'
and owner != 'SYS'
and owner != 'SYSTEM';

2.2.9. Правило 11: Размер файла данных должен быть кратен размеру экстента + 1 [блок]

Чтобы выявить файлы данных, которые не подчиняются правилам размеров файлов, следует выполнить следующй запрос:

select t.tablespace_name, file_name, bytes file_size, initial_extent
from dba_tablespaces t, dba_data_files f
where mod(f.bytes - f.bytes/f.blocks, initial_extent) != 0
and f.tablespace_name = t.tablespace_name;

2.2.10. Правило 12: Никогда не дефрагментируйте табличные пространства с однородными экстентами

Чтобы выявить табличные пространства, использующие однородный размер экстентов и имеющие свободные экстенты, которые не кратны размеру экстента, следует выполнить следующй запрос:

select t.tablespace_name, file_id, block_id, bytes, initial_extent
from dba_tablespaces t, dba_free_space s
where next_extent = initial_extent
and pct_increase = 0
and min_extlen = initial_extent
and t.tablespace_name != 'SYSTEM'
and t.tablespace_name = s.tablespace_name
and mod(bytes, initial_extent) != 0;

3. Сегменты, организованные в виде кучи (иначе называемые таблицами)

Oracle организует хранение табличных данных в виде куч (Heaps) или же в виде B*Trees индексов. Проблемы фрагментации B*Trees индексов не зависят от того, хранят ли они только индекс или табличные данные. Связанные с B*Tree индексами проблемы мы рассмотрим в следующей секции. Куча же, как следует из названия, осуществляет управление коллекцией блоков, и, когда необходимо, пространством, используемым для этих блоков, для вставки и обновления. Собственно строки [данных] могут иметь изменяющиеся размеры, они должны быть помещены соответственно в блоках, принадлежащих к куче. Со временем строки удаляются/обновляются/вставляются (deleted/updated/inserted), и подобная проблема, которая была рассмотрена для табличных пространств, может иметь теперь место для сегментов. А так как нельзя потребовать, чтобы размеры строк были заранее установленными, мы должны придумать другие несколько более сложные средства предотвращения фрагментации для сегментов кучи. Алгоритм, который используется Oracle для размещения строк в сегменте, оптимизируется во времени (для доступа). Следовательно, мы пробуем разместить строки в минимальном числе блоков, необходимых для их содержания.

С течением времени использования сегмента некоторое свободное место в каждом из блоков оказывается достаточным, чтобы размещать новые строки. Это заставляет сегмент разрастаться, то есть ассигновать следующий экстент в табличном пространстве. Эта проблема называется как фрагментация кучи. [Прим. А.Бачина: на русском языке эта проблема имеет название экстентной фрагментизации.]

3.1. Управление списком свободных блоков (Freelist) в куче

Oracle управляет свободными блоками как отдельно связанным списком, основываясь на следующих параметрах: