Ричард Дж. Нимик

Эта статья впервые была размещена на сайте www.oramag.ru

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

В традиционной архитектуре клиент/сервер на плохо настроенной базе данных прикладные программы могут выполняться медленно, но в среде Web эти же приложения могут не выполняться вовсе. Представьте, например, что вы разрешили заказчикам или клиентам делать запросы к базе данных через ваш Web-сайт. Приложение, которое ранее было доступно всего лишь немногим пользователям вашего учреждения, теперь внезапно стало доступно, скажем, тысячам ваших заказчиков. Тем не менее, когда вы переводите ваши деловые действия на Web, основные принципы настройки базы данных остаются теми же самыми, независимо от того, как будет расти число запросов к вашей базе данных (пусть даже по экспоненте). Естественно, много связанных с эффективностью проблем являются характерными только для каждой конкретной системы, но я обнаружил, что если при настройке постоянно иметь в виду следующие четыре направления, это поможет быстро и просто улучшить производительность системы:

• Выделение экземпляру Oracle достаточного объема оперативной памяти.
• Хранение необходимых данных и других объектов в оперативной памяти.
• Выделение запросов, вызывающих проблемы.
• Настройка проблемных запросов.

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

Правило 1. Выделение экземпляру Oracle достаточного объема оперативной памяти

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

    DB_BLOCK_BUFFERS и DB_BLOCK_SIZE 
    SHARED_POOL_SIZE
    SORT_AREA_SIZE 

Используйте приведенный в листинге 1 запрос, чтобы найти текущие значения этих параметров. Увидеть значения этих параметров вы можете также, используя для этого средства Oracle Enterprise Manager (OEM).

DB_BLOCK_BUFFERS и DB_BLOCK_SIZE

Параметр DB_BLOCK_BUFFERS управляет системной глобальной областью (SGA), которую сервер базы данных Oracle использует для хранения и обработки данных в памяти. Когда потребители запрашивают данные, сервер помещает их в память, так, чтобы при последующих запросах пользователя (или пользовательского процесса) обеспечить более быстрый доступ к ним. Если значение параметра DB_BLOCK_BUFFERS слишком мало, то сервер слишком рано сбросит на диск самые старые из имеющихся в памяти данных. А это означает, что когда эти данные потребуются в следующий раз, серверу придется считывать их с диска, а не брать в оперативной памяти. Если же значение параметра DB_BLOCK_BUFFERS слишком велико, вашей системе может не хватить памяти для эффективного функционирования.

Вы можете выяснить, насколько эффективна настройка параметра DB_BLOCK_BUFFERS, измеряя коэффициент попаданий (hit ratio), значение которого говорит вам, какая часть данных, к которым осуществляют доступ потребители, находится в памяти. Чтобы найти его, вы можете использовать запрос, приведенный в листинге 2. (Для графического представления этих данных, вы можете использовать модуль OEM Performance Manager.)

Я считаю, что для коэффициента попаданий вполне приемлемо значение 95 процентов или выше. Если получившееся у вас значение ниже, чем 95 процентов, вам стоит скорректировать значение DB_BLOCK_BUFFERS. Хорошее эмпирическое правило для определения этого значения – назначить для DB_BLOCK_BUFFERS 25 процентов доступной оперативной памяти, хотя конкретный выбор может зависеть от размера вашей системы, объема доступной памяти и числа потребителей.

В дополнение к корректировке числа блоков данных, которые сервер хранит в памяти, вы можете изменить размер этих блоков, регулируя настройку параметра DB_BLOCK_SIZE. Этот параметр управляет количеством данных, которые база данных может считать в память в течение одной транзакции ввода/вывода. При работе с Oracle8, вы можете установить это значение равным 32Кбайт (в предыдущих версиях Oracle только 16Кбайт), но вы должны реорганизовать базу данных, чтобы значение параметра было изменено. Прежде, чем изменить этот параметр, загляните в руководства по настройке и администрированию базы данных Oracle.

[Прим. редактора: Обратите особое внимание, что автор все же говорит о необходимости реорганизовать базу данных, если Вы захотите изменить параметр DB_BLOCK_SIZE.]

SHARED_POOL_SIZE

Параметр SHARED_POOL_ SIZE управляет объемом памяти, распределенным для кэширования библиотеки и словаря данных. Вы можете выяснить, насколько эффективна настройка этого параметра, таким же образом, как и для параметра DB_BLOCK_BUFFER_SIZE – измеряя коэффициент попаданий. Коэффициент попаданий можно измерять отдельно для библиотеки и для словаря данных.

Коэффициент попаданий для библиотеки указывает, какой процент памяти сервер использует для операторов и объектов PL/SQL, например, процедур, пакетов, и триггеров. В дополнение к вычислению процента удач (см. листинг 2), вы должны также исследовать значение столбец RELOAD в представлении v$librarycache.

Перезагрузками называются операторы, которые были размещены в памяти, но которые пришлось перезагрузить после того, как сервер удалил их оттуда. Если у вас коэффициент попаданий меньше, чем 95 процентов, или число перезагрузок больше нуля, вам следует увеличить значение SHARED_POOL_SIZE.

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

   select (1-(sum(getmisses)/sum(gets))) * 100 "Hit Ratio"
   from v$rowcache;

коэффициент попаданий ---------- 95.40 %

Напомню еще раз: хорошо, если полученное значение 95 или выше. Если полученное вами значение меньше 95 процентов, стоит подумать об увеличении значения SHARED_POOL_SIZE.

Вы можете использовать приведенный в листинге 1 запрос, чтобы выяснить, сколько свободной памяти становится доступной для используемого значения SHARED_POOL_SIZE. Количество свободной памяти – еще один индикатор того, является ли значение SHARED_POOL_SIZE оптимальным.

SORT_AREA_SIZE

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

В представлении V$systat содержатся сведения о проценте сортировок, которые выполняются сервером в оперативной памяти и с использованием дисковой памяти. Чтобы найти процент сортировок, выполняемых в памяти, я использую запрос из листинга 2. Если этот процент меньше, чем 90, вы должны рассмотреть вопрос об увеличении значения SORT_AREA_SIZE.

Правило 2. Хранение в оперативной памяти нужных данных

После того, как вы распределили экземпляру сервера Oracle оптимальный объем памяти, вы должны быть уверены, что наиболее важные данные остаются в памяти. Вы можете “закрепить” в памяти (pin) основные таблицы, объекты PL/SQL и пакеты, чтобы избежать сбрасывания их сервером на диск.

Закрепление таблиц

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

Если вы обнаружили, что сервер выталкивает основные таблицы из памяти, вы можете закрепить их в памяти, используя параметр CACHE оператора CREATE/ALTER TABLE:

ALTER TABLE [TABLENAME] CACHE;

Table altered.

Этот параметр гарантирует, что данные из таблицы после полного ее сканирования находятся в списке самых недавно использованных (most recently used – MRU) данных, а не в списке самых давно использованных (least recently used – LRU) данных, в результате чего они будут сохранены в памяти для последующего использования. При создании таблицы значение параметра по умолчанию – NOCACHE. Поэтому для того, чтобы кэшировать таблицу при первом же к ней доступе, нужно использовать следующий синтаксис:

   CREATE TABLE TEST_TAB (COL1 NUMBER)
   TABLESPACE USERS
   CACHE;

Можете также использовать в запросе подсказку CACHE, чтобы закрепить таблицу и сохранять ее в кэше, начиная с первого ее использования, а именно:

   SELECT /*+ CACHE(CUST) */ ENAME, JOB
   FROM CUST
   WHERE TABLE_NAME = 'EMP';

Перед закреплением таблиц в памяти следует выяснить, сколько памяти все еще остается свободной, чтобы можно было учесть непредвиденные запросы. Полезно проводить проверку того, достаточно ли памяти распределено для данных после того, как система проработает большую часть дня. Чтобы выяснять, сколько памяти доступно для данных в любой момент времени, выполните следующий запрос к таблице x$bh (чтобы иметь возможность обратиться к таблицам x$, вы должны войти в систему как SYS, или создать представления таблиц, а затем создать привилегии для этих представлений):

select decode(state,0, 'FREE', 1, decode
(lrba_seq,0,'AVAILABLE','BEING USED'),
3, 'BEING USED', state) "BLOCK STATUS",
count (*)
from x$bh
group by decode(state,0,'FREE',1, decode
(lrba_seq,0,'AVAILABLE','BEING USED'),
3, 'BEING USED', state); 

select decode(state,0, 'FREE', 1, decode
(lrba_seq,0,'AVAILABLE','BEING USED'),
3, 'BEING USED', state) "BLOCK STATUS",
count (*)
from x$bh
group by decode(state,0,'FREE',1, decode
(lrba_seq,0,'AVAILABLE','BEING USED'),
3, 'BEING USED', state); 

Если в первые 30 минут после запуска системы окажется, что нет свободных буферов, вам, может быть, придется возвратиться к первому правилу и увеличить значение DB_BLOCK_BUFFERS.

Закрепление в памяти объектов и пакетов

Если Вы не можете поддерживать удовлетворительную настройку для SHARED_POOL_SIZE, может оказаться важно сохранять закрепленными в памяти наиболее часто используемые объекты. Вы можете закреплять операторы объекта PL/SQL в памяти, используя процедуру DBMS_SHARED_POOL.KEEP, например, следующим образом:

BEGIN
DBMS_SHARED_POOL.KEEP('PROCESS_DATE','P');
END;

Вы можете также закрепить некоторые или все пакеты, используя некоторые встроенные пакеты Oracle типа STANDARD, DBMS_STANDARD и DIUTIL.

Чтобы закрепить все пакеты вашей системы, вы можете использовать скрипт из листинга 3.

Правило 3. Нахождение проблемных запросов

Один-единственный индекс или запрос могут затормозить или даже вовсе прекратить работу всей системы. Запрашивая v$sqlarea, вы можете выявить запросы, которые создают проблемы для системы. Проблемные запросы – это такие запросы, для которых требуется наибольшее количество физических или логических операций чтения с диска.

Обнаружение запросов, для которых требуется наибольшее количество физических операций чтения с диска

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

Выходные данные примера указывают на то, что большое количество операций чтения с диска вызывают два запроса. Проблема с первым запросом состоит в том, что индекс по столбцу orderid подавлен функцией SUBSTR. Проблема со вторым запросом – отсутствие индекса для столбца city.

Обнаружение запросов, для которых требуется наибольшее количество логических операций чтения с диска

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

Выходные данные примера указывают на то, что проблему создает индекс по столбцу division, когда в компании имеется только два отдела. Чтобы улучшить производительность, нужно либо отказаться от этого индекса, либо создать его, как двоичный (bitmap) – это должно помочь.

Заметьте, что представление v$sqltext отображает только ограниченную часть SQL_TEXT. В случае примеров в листингах 4 и 5, удается увидеть полный запрос, но в других случаях запрос может быть слишком длинным, чтобы быть отображенным полностью. В таких случаях, чтобы получить полный текст запроса, вы можете выполнить для представления v$sqltext оператор JOIN.

Использование оператора AUTOTRACE

Лучший способ измерять эффективность запросов (применяя SQL*PLUS 3.3 и более поздние версии) состоит в том, чтобы использовать команду AUTOTRACE. Для реализации свойства AUTOTRACE вы можете использовать SQL-операторы из листинга 6. (Помните, что вы должны заранее создать таблицу PLAN_TABLE, и что вы должны иметь соответствующие привилегии доступа, разрешенные для представлений V$.) Чтобы создать роль PLUSTRACE, выполните скрипт plustrce.sql как потребитель с именем SYS. (Подробности см. в документации Oracle.)

Что впереди

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

Ричард Дж. Немец (Richard J. Niemiec)– исполнительный вице-президент компании TUSC. Он работает с технологиями Oracle уже более десяти лет и является автором трех вышедших в издательстве Oracle Press книг. Сейчас он выполняет обязанности исполнительного вице-президента группы пользователей IOUG-A. Ричард Дж. Немец включен в Зал Славы предпринимателей (Entrepreneur Hall of Fame).

1. Листинг 1. Нахождение установленных значений основных параметров настройки.
2. Листинг 2. Нахождение коэффициента попадания по чтению (read hit ratio).
3. Листинг 3. Закрепление всех пакетов в кеше.
4. Листинг 4. Поиск запросов, которые выыполняют более 10,000 дисковых чтений.
5. Листинг 5. Поиск запросов, которые выполнят более чем 200,000 логических чтений.
6. Листинг 6. Реализация возможности AUTOTRACE

Дополнительную информацию Вы можете получить в компании Interface Ltd.