Выпуск №16

Выпуск 16

Здравствуйте уважаемые читатели рассылки «СУБД Oracle с нуля»! Кажется, с момента выхода последнего выпуска рассылки прошла уже целая вечность (в самом прямом смысле слова). Но, как ни странно, интерес к моим статьям не угасает, а разгорается все сильнее и сильнее.

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

Но это одна сторона медали. Есть еще одна проблема, связанная … с ОГРОМНЫМ МОРЕМ документации. По системе Oracle существует столько документации, статей и книг, что нет никакой возможности даже прочитать все написанное. Не говоря уже о том, что это море продолжает разрастаться с огромной скоростью.

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

На сегодняшний день, наибольший интерес представляют версии СУБД Oracle 9i , и Oracle 10g . Многие предприятия постепенно переходят на более новые версии. Но переход – довольно болезненная процедура, которая, как правило, занимает много времени на подготовку. Поэтому необходимо смириться с фактом, что разные предприятия работают на разных версиях СУБД. Конечно, происходит постепенная миграция, но я думаю, что наиболее популярной версией на сегодняшний день является версия 9i. Именно от нее я и буду отталкиваться.

Обращаю ваше внимание, что архив рассылки переехал:
http://oraclefromzero.narod.ru/

А теперь предлагаю проверить свои знания по предыдущему выпуску рассылки.

1. Для чего нужны файлы журналирования операций ( redo log files )?
2. Что такое контрольная точка ( checkpoint )?
3. К каким последствиям приводит смена журнала ( log switch )?
4. Как осуществить зеркалирование контрольного файла ( control file multiplexing )?
5. Для чего нужны сегменты отката ( undo segments *)?

*Пятый вопрос требует небольшого пояснения. Дело в том, что, начиная с версии Oracle 9i , произошло небольшое изменение в терминологии. Сегменты отката, ранее известные как rollback segments , теперь называют undo segments.

Управление undo-данными (данными отмен)

В предыдущем выпуске было рассмотрено предназначение так называемых сегментов отката ( rollback ). В соответствии с новыми веяниями, термин «rollback» будет применяться при ручном управлении, а термин «undo» (отмена) при автоматическом управлении. Сегодня мы займемся управлением, настройкой и оптимизацией undo -данных.

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

• Ручное управление;
• Автоматическое управление (начиная с Oracle 9i ).

Рассмотрим их по порядку.

Ручное управление сегментами отката.

Создание сегментов отката

Как и все другие объекты БД, сегменты отката можно создать либо с помощью визуальных утилит (например, Oracle Enterprise Manager ), либо с помощью DDL-команды CREATE ROLLBACK SEGMENT в SqlPlus .

Наиболее простой путь, конечно же, через консоль Enterprise Manager . Все что надо сделать – это найти в дереве объектов БД ветку Storage , а в ней Rollback Segments . По щелчку правой кнопкой мыши появится меню возможных операций. После того, как вы выберите команду Create , появится окно опций создания сегмента отката. В нем надо указать имя сегмента и табличное пространство, которому он будет принадлежать. Здесь же можно поставить галочку Public , если необходимо разрешить использование сегмента несколькими экземплярами БД. Можно также выбрать режимы offline или online , и задать параметры размера сегмента на вкладке Storage .

Напоминаю, что после выбора всех параметров, можно нажать кнопку Show SQL , которая отобразит соответствующую DDL -команду.

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

Чтобы вы ощутили разницу в использовании скрипта и работы в графической среде, представьте такую ситуацию. Вам необходимо создать 50 или 100 сегментов отката. В текстовом редакторе можно создать одну команду, скопировать ее несколько раз и изменить имена сегментов и табличных пространств. Затем запустить этот скрипт и пойти съесть бутерброд с колбасой, запив его чашкой кофе. А в графической среде, на получение того же результата вы будете вынуждены полдня шуршать мышью, скучать и портить себе настроение.

Поэтому полезно знать и пользоваться следующей командой:

CREATE [ PUBLIC или PRIVATE ] ROLLBACK SEGMENT rsname
TABLESPACE tsname
STORAGE (
INITIAL число K или M
NEXT число K или M
OPTIMAL число K или M
MINEXTENTS число
MAXEXTENTS число
);

Вот что означают параметры этой команды:

• rsname – имя создаваемого сегмента отката;
• tsname – наименование табличного пространства, в котором будет создан сегмент отката;
• INITIAL – первоначальный размер экстента в К (Кб) или в М (Мб);
• NEXT – размер второго и следующих экстентов. Для сегментов отката нет необходимости использовать разные размеры экстентов, поскольку между ними нет никаких отличий;
• OPTIMAL – размер, к которому будет стремится сегмент отката. Когда в дополнительных экстентах нет необходимости, они освобождаются для того, чтобы сегмент отката приблизился к своему оптимальному размеру;
• MINEXTENTS – минимальное количество экстентов. Кроме того, это количество экстентов которое будет выделено при создании сегмента отката.
• MAXEXTENTS – максимальное количество экстентов, которые могут быть выделены для этого сегмента отката.

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

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

Оптимизация сегментов отката

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

Определение необходимого количества сегментов отката

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

Конфликты возникают, если очень большое количество транзакций одномоментно пытаются использовать один и то же сегмент отката, из-за чего некоторые транзакции вынуждены простаивать. Увидеть существование таких конфликтов позволяет динамическая таблица V$WAITSTAT . Колонки таблицы означают следующее:

• UNDO HEADER – Число ожиданий для буферов, содержащих заголовки блоков сегментов отката.
• UNDO BLOCK – Число ожиданий для буферов, содержащих все остальные блоки сегментов отката.
• SYSTEM UNDO HEADER – То же самое, что и UNDO HEADER , но только для системного сегмента отката.
• SYSTEM UNDO BLOCK – Аналогично UNDO BLOCK , но только для системного сегмента отката.

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

Эти значения можно узнать с помощью следующего запроса:

SQL> SELECT class, count
2> FROM V$WAITSTAT
3> WHERE class IN
4> ('undo header', 'undo block', 'system undo header', 'system undo 

block');

Пример результатов вышеприведенного запроса:

CLASS                                    COUNT
----------------------------  ----------------
system undo header                           0
system undo blocks                           0
undo header                                  0
undo block                                   0

Полученные значения необходимо сравнить с общим числом запросов к данным. Число запросов к данным равно сумме значений DB BLOCK GETS и CONSISTENT GETS из динамической таблицы V$SYSSTAT . Например, можно воспользоваться следующим запросом:

SQL>  SELECT SUM(value) "Data Requests"
2> FROM v$sysstat
3> WHERE name IN ('db block gets', 'consistent gets');

Например, получим следующий результат:

Data Requests
------------------
           2983832

В результате запроса мы получили необходимое нам общее число запросов к данным. Если число ожиданий блоков или заголовков сегментов отката более 1% общего количества запросов, то необходимо снизить конфликты с помощью введения дополнительных сегментов отката. В приведенном примере общее число запросов составляет 2983832, в то время как число ожиданий равно 0. Это говорит нам о том, что конфликтов не возникает и количество сегментов отката соответствует требующемуся.

Определение размеров и количества экстентов

Обычно, наибольшая производительность сегмента отката достигается при использовании от 10 до 20 экстентов одного размера. Для определения размера и числа экстентов можно использовать следующую формулу:

Размер сегмента отката = Rsize = Объем самой большой таблицы / 10

Число экстентов = NE = 10

Размер экстентов = Esize = Rsize / NE

При создании сегментов отката, используйте значение Esize для параметров INITIAL и NEXT , а для параметра MINEXTENTS – значение NE . Но даже если придерживаться этих правил, возможно, вы не достигните эффективного размера вашего сегмента отката. В случае динамического роста сегмента производительность будет падать.

Как избежать динамического роста

Как упомянуто выше, желательно избегать динамического управления размером сегментов отката, так как это приводит к дополнительной нагрузке на систему и задержке транзакций. Чтобы выявить такую проблему, можно посмотреть данные динамической таблицы производительности V$ROLLSTAT . Наибольший интерес представляют следующие столбцы:

• EXTENTS – Число экстентов в сегменте отката.
• RSSIZE – Размер (в байтах) сегмента отката.
• OPTSIZE – Размер, в который был установлен параметр OPTIMAL .
• AVEACTIVE – Текущий средний размер активных экстентов. Активными считаются экстенты, в которых содержаться данные незафиксированных транзакций.
• AVESHRINK – Общий размер незанятых экстентов, деленный на количество уменьшений сегмента отката (т.е. уменьшений количества экстентов, направленных на достижение параметра OPTIMAL ).
• EXTENDS – Число случаев, когда к сегменту отката добавлялись экстенты.
• SHRINKS – Число случаев, когда сегмент отката уменьшался. Каждое уменьшение может удалять один или более экстентов за раз.
• HWMSIZE – Отметка максимального размера сегмента отката (т.е. тот максимальный размер которого сегмент отката когда либо достиг).

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

SQL> 

 SELECT substr(name, 1, 40), extents, rssize, aveactive,
2> aveshrink, extends, shrinks
3> FROM v$rollname rn, v$rollstat rs
4> WHERE rn.usn = rs.usn;

Если средний размер близок к OPTIMAL, то параметр OPTIMAL выбран верно. Если значения EXTENDS или SHRINKS велики, то вы должны увеличить значение параметра OPTIMAL.

Автоматическое управление undo-данными

Для работы в автоматическом режиме необходимо создать хотя бы одно UNDO табличное пространство достаточного размера на один экземпляр Oracle . В автоматическом режиме сервер Oracle сам позаботится о количестве и размерах сегментов отката, имена которых соответствуют такому соглашению: _SYSSMUn$. Например: _SYSSMU1$, _SYSSMU2$ и т.д.

Используются два инициализационных параметра:

• UNDO_MANAGEMENT – определяет режим управления undo -данными базы данных. Можно указывать 2 режима: AUTO и MANUAL . Параметр UNDO_MANAGEMENT нельзя менять динамически при запущенном экземпляре Oracle . Режим AUTO включает автоматическое управление и требует наличия UNDO табличного пространства. Режим MANUAL (значение по-умолчанию) позволяет управлять сегментами отката вручную. Например, для включения автоматического режима надо в файле параметров указать: UNDO_MANAGEMENT = AUTO );
• UNDO_TABLESPACE – Указывает на UNDO табличное пространство, которое будет использоваться в автоматическом режиме. Этот параметр может быть установлен как в инициализационном файле ( UNDO_TABLESPACE = UNDOTBS ), так и изменен динамически ( ALTER SYSTEM SET undo_tablespace = UNDOTBS ).

Для оптимизации undo -сегментов в автоматическом режиме применяется параметр UNDO_RETENTION , который определяет время хранения undo -данных при согласованном чтении. Этот параметр определяется в секундах и может быть как прописан в инициализационном файле, так и изменен динамически, например:

ALTER SYSTEM SET UNDO_RETENTION = 1200;

Значение 1200 принуждает сервер сохранять undo -данные на 20 минут (т.е. для запросов, которые длятся более 20 минут, есть вероятность завершения с ошибкой).

Следует отметить, что для больших значений UNDO_RETENTION требуется большое табличное пространство. Если UNDO табличное пространство мало, то undo-данные не будут храниться указанное время.

Как же определить требующийся размер UNDO табличного пространства? Для этого надо знать значение UNDO_RETENTION в секундах ( UR ), количество undo блоков данных генерируемых в секунду ( UPS ) и размер блока данных ( DBS ).

Формула для расчета размера undo табличного пространства:

UndoSpace = [ UR * (UPS * DBS)] + (DBS*24).

Рассчитать эту формулу можно с помощью следующего запроса:

SQL> SELECT 

(UR*(UPS*DBS))+(DBS*24) AS "Bytes"
2> FROM (SELECT value AS UR
3> FROM v$parameter
4> WHERE name='undo_retention'),
5> (SELECT (SUM(undoblks)/SUM(((end_time-begin_time)*86400))) AS UPS
6> FROM v$undostat),
7> (SELECT value AS DBS
8> FROM v$parameter
9> WHERE name='db_block_size');

         Bytes
--------------
      19106213

Чтобы перевести байты в мегабайты, надо разделить полученное значение на 1048576. В результате получим 18,22 мегабайта. Для более надежного результата желательно производить расчет во время активной работы пользователей с базой.

Подводя итоги.

Легко можно заметить, что при автоматическом режиме управления данными отмен, у администратора возникает гораздо меньше головной боли. Но этим новшеством могут воспользоваться только счастливые обладатели Oracle 9i и старше.

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

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

Что дальше?

В следующих выпусках мы узнаем о нескольких инструментах для управления данными, которые входят в состав дистрибутива Oracle Server : это Export , Import и SQL * Loader . Export и Import используются для перемещения данных между БД. SQL * Loader предназначен для загрузки данных в базу данных из различных источников.

На этом я завершаю этот выпуск. Жду ваших комментариев и вопросов. И еще, приглашаю всех на новый сайт этой рассылки - Russian Oracle Network

Всего наилучшего!

Злыгостев. А.А.

zlygostev@gmail.com