• Системы поддержки принятия решений (decision support systems)
• Считывание значений переменных связывания в СУБД Oracle9i
• Параметр cursor_sharing в СУБД Oracle9i
• Заключение

Резюме

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

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

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

Обработка операторов SQL в СУБД Oracle

При обработке операторов SQL в СУБД Oracle обычно выполняется четыре или пять шагов в зависимости от типа оператора SQL:

Шаг полного разбора (hard parse), во время которого СУБД Oracle разбирает оператор SQL, проверяет его синтаксическую корректность и существование таблиц и столбцов, на которые есть ссылки в операторе.

Шаг частичного разбора (soft parse), во время которого оператор SQL связывается с конкретным сеансом и проверяются права доступа.

Шаг оптимизации (optimization), во время которого СУБД Oracle определяет лучший способ выполнения оператора SQL, используя информацию о размере различных объектов (таблиц, индексов и т.д.), к которым будет осуществляться доступ.

Шаг выполнения (execution), в котором для фактического доступа к данным используются результаты шагов разбора и оптимизации.

Для запросов (то есть операторов select) после шага выполнения следует шаг выборки (fetch), во время которого приложение действительно получает строки запроса.

Понимание обработки операторов SQL в СУБД Oracle позволяет разработчикам приложений писать масштабируемые приложения. В частности избегая полных и частичных разборов, можно существенно уменьшить накладные расходы ЦП и повысить разделение оперативной памяти.

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

Главным компонентом СУБД Oracle, задействованным в обработке операторов SQL, является библиотечный кеш (library cache), в которым кешируются текущие выполняемые операторы SQL. Во время шага разбора (с точки зрения программирования приложений существует только одна операция разбора; операции полного и частичного разбора различаются только в СУБД Oracle в зависимости от обстоятельств, описанных ниже). СУБД Oracle сначала проверяет, находится ли оператор SQL в библиотечном кеше. Если это так, то требуется только небольшая дополнительная обработка оператора, такая, как проверка прав доступа и связи с окружением сеанса, и частичный разбор. Если оператор не найден в библиотечном кеше, требуется полный разбор с проверкой синтаксиса, корректности имен таблиц и столбцов и т.д. Предшествующий тип разбора, частичный разбор, выполняется значительно быстрее полного разбора. Следовательно, в приложениях нужно избегать, в частности, полных разборов.

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

Для проверки, находится ли уже оператор SQL в библиотечном кеше, СУБД Oracle использует просмотр с хешированием, после этого выполняется просто строковое сравнение. Следовательно, только полностью идентичные операторы SQL будут рассматриваться как идентичные.

Использование переменных связывания

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

select * from emp where empno=1234
и
select * from emp where empno=5678

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

select * from emp where empno=:x

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

Поддержка приложений, использующих литералы

Значения 1234 и 5678 в двух примерах, показанных выше, являются константами (литералами), их использование в операторах SQL является причиной возникновения накладных расходов из-за допол-
нительных разборов и означает, что операторы SQL в библиотечном кеше не разделяются. Тем не менее при разработке многих приложений этому не уделяется должное внимание, поэтому в СУБД Oracle предусмотрено средство уменьшения влияния такого программирования на систему. Оно позволяет разделять в библиотечном кеше информацию о курсорах для тех операторов SQL, которые фактически не являются идентичными, но их различие заключается только в литералах. Реализация данного средства заключается в выполнении простого просмотра оператора SQL, во время которого все обнаруженные литералы заменяются переменными связывания. Этот просмотр выполняется перед фактическим шагом разбора, следовательно, когда СУБД будет разбирать два оператора, показанные выше, они будут заменены оператором, который будет иметь примерно следующий вид:

select * from emp where empno=:SYS_B_0

В этом операторе литерал был заменен переменной связывания, поэтому оператор станет разделяемым и полный разбор будет выполняться только один раз, соответственно уменьшая накладные расходы. Следовательно, даже для приложений, в которых не используются переменные связывания, средство автоматической замены литералов переменными связывания может принести пользу. Данный механизм управляется установкой в файле init.ora значений параметра cursor_sharing (разделение курсоров). Параметр может иметь следующие значения:

exact (точное соответствие)	Отключает данный механизм, то есть для операторов с разными литералами отключается разделение курсоров. Это значение устанавливается по умолчанию.
force (безусловное)	Включает механизм безусловного разделения операторов SQL, но только тех, которые имеют отличающиеся литералы.
similar (аналогичные операторы)	Разделение курсоров осуществляется тогда, когда известно, что оно не окажет никакого влияния на оптимизацию. Это значение не доступно в Oracle8i.

Этот параметр может быть установлен либо глобально в файле init.ora, либо динамически для всех сеансов с помощью оператора alter system или для конкретного сеанса с помощью оператора alter session. Его значение по умолчанию (exact) может быть изменено в будущих версиях СУБД Oracle.

Работа с курсорами

Курсор – это указатель на область контекста, с помощью которого приложение выполняет операторы SQL. Приложение может открывать столько курсоров, сколько требуется – в рамках ограничений по памяти и в зависимости от значения параметра open_cursors (максимальное количество открытых в экземпляре курсоров) в файле init.ora, впоследствии курсоры связываются с операторами SQL. Для наиболее эффективного выполнения операторов SQL приложения должны иметь необходимое количество курсоров, чтобы используемые часто операторы всегда были готовы к выполнению. Для этого приложения открывают требуемое количество курсоров и выполняют разбор соответствующих операторов SQL, использующих переменные связывания. Затем, когда будут доступны фактические данные, нужно будет выполнить только шаг выполнения (и шаг выборки для запросов).

Несмотря на то что корпорация Oracle рекомендует использовать данный подход, в большинстве приложений он не применяется или, другими словами, при разработке приложений редко соблюдается эта рекомендация корпорации Oracle. Поэтому приложения либо будут постоянно открывать и закрывать курсоры, либо будут повторно использовать один курсор с различными операторами SQL (в этом случае будет выполняться, по крайней мере, частичный разбор, а во многих случаях полный разбор). Для изменения такого поведения приложений можно использовать параметр инициализации session_cached_cursors (максимальное количество курсоров, кешируемых в сеансе). В этом параметре может быть установлено целочисленное значение. В этом случае некоторая часть области контекста на стороне сервера, связанная с операторами SQL, будет сохраняться, даже если на стороне клиента будут закрываться курсоры или повторно использоваться с новыми операторами SQL.

Для хорошо написанных приложений, которые открывают курсоры и выполняют разбор операторов SQL только один раз, а затем многократно повторяют шаги выполнения одного или более курсоров, требуется некоторая работа по выделению памяти, используемой только во время фазы выполнения оператора SQL (runtime memory). Для того чтобы эта память выделялась только один раз, при первом выполнении, нужно установить в параметре cursor_space_for_time (часть области контекста курсора, используемая во время фазы выполнения) значение true (по умолчанию устанавливается значение false) - это позволит повысить производительность за счет использования дополнительной памяти. Этот параметр следует использовать только тогда, когда ваше приложение имеет строго ограниченный набор операторов SQL, который полностью размещается в разделяемом пуле (shared pool), так как издержки использования памяти могут быть очень велики.

Итоги и лучшие практические методы

Результаты

В контексте данной статьи приложения, которые используют сходные (similar) операторы SQL (то есть операторы SQL, отличающиеся только фактическими значениями литералов), можно разделить на три категории:

1.	Приложения, в которых не используются переменные связывания. Такой стиль программирования наблюдается часто, когда разработчики приложений не осведомлены о важности уменьшения количества разборов, или когда приложения переносятся из других СУБД.
2a.	Приложения, выполняющие все шаги для каждого отдельного оператора SQL: открытие курсора, разбор с переменными связывания, выполнение (и выборка для запросов) и закрытие курсора. Это – поведение Oracle Forms при входе и выходе из блоков, а также в собственном динамическом SQL (native dynamic SQL) языка PL/SQL. Преимущества использования переменных связывания при таком подходе не очевидны программистам, работающим с интерфейсом вызовов Oracle (Oracle Call Interface, OCI) или прекомпиляторами Oracle (Oracle Precompilers), поэтому в этой среде переменные связывания встречаются редко.
2b.	Приложения, в которых для каждого оператора SQL один раз открывается курсор, а затем многократно выполняются следующие шаги: разбор с переменными связывания, выполнение (и выборка для запросов). Этот тип приложений часто наблюдается, когда разработчики приложений используют Oracle Precompilers с опциями HOLD_CURSOR=NO (не удерживать курсор) и RELEASE_CURSOR=NO (не освобождать курсор), которые устанавливаются по умолчанию, или когда используется пакет PL/SQL DBMS_SQL.
3.	Приложения, в которых для каждого отдельного (отличного от других) оператора SQL один раз открывается курсор, выполняется разбор с переменными связывания, а затем следует многократное выполнение (и выборка для запросов). Это наиболее эффективный способ разработки приложений, многократно исполняющих одни и те же операторы SQL. Обычно наблюдается в хорошо спроектированных приложениях, разработчики которых использовали Oracle Call Interface или Oracle Precompilers. Особенно это относится к Oracle Precompilers, когда для конкретных операторов SQL используются опции HOLD_CURSOR=YES (удерживать курсор) и RELEASE_CURSOR=NO (не освобождать курсор), или используется установка общего параметра MAXOPENCURSORS (максимальное количество открытых курсоров), которая определяет поведение неявных курсоров для данной категории приложений. Этот подход также используется в программах, написанных на PL/SQL, и при использовании курсоров в циклах и явных курсоров в статическом SQL.

Категории 2a и 2b практически идентичны, так как фактическое открытие и закрытие курсоров оказывает очень малое влияние на производительность, реальные накладные расходы – на разбор.

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

Категория приложения	Общее поведение	cursor_ sharing = force или similar	session_ cached_ cursors = достаточно высокое значение	cursor_ space_for _time = true
1. Совершенно не используются переменные связывания.	В общем, плохое время реакции и масштаби- руемость. Тем не менее эта категория рекомендуется для DSS- приложений, у которых нет или очень мало повторений операторов SQL.	Значительное сокращение времени ответа и увеличение масштаби- руемости; хотя результаты не столь хороши, как в приложениях 2-й и 3-й категорий.	Незна- чительное улучшение исходных данных.	Никакого влияния.
2. Разбор с переменными связывания, выполнение (и выборка) для каждого выполнения оператора SQL.	Относительно короткое время ответа из-за разделения курсоров в библиотечном кеше; ограниченная масштаби- руемость из-за повторяющихся открытий и закрытий курсоров и частичных разборов.	Никаких допол- нительных улучшений.	Сокращается время ответа и увеличивается масштаби- руемость, так как сервер сохраняет кешированные курсоры.	Никакого влияния.
3. Однократное открытие курсора и разбор с переменными связывания, многократное выполнение (и выборка).	Самые лучшие из возможных время ответа и масштаби- руемость.	Никаких допол- нительных улучшений.	Никаких допол- нительных улучшений	Допол- нительное увеличение масштаби- руемости.

Лучшие практические методы

Тесты (описаны в приложении к этой статье) показывают, что надлежащая установка значения параметра cursor_sharing позволяет повысить производительность приложений, которые были написаны без использования переменных связывания. В частности, время ответа в приложениях с часто повторяющимися операторами SQL, которые отличаются друг от друга только литералами, может быть чрезвычайно большим. Кроме указанного повышения производительности, использование этого параметра также позволяет уменьшить объем потребляемой памяти, так как разделяется большее количество операторов SQL. Однако поскольку по-прежнему выполняется частичный разбор, использование приложений без переменных связывания может привести к проблемам масштабируемости, хотя и на более высоких уровнях пропускной способности.

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

Для хорошо спроектированных приложений (многократное выполнение разобранных операторов SQL) установка параметра cursor_space_for_time = true может привести к некоторому улучшению масштабируемости за счет потребления дополнительной памяти.

Параметры инициализации cursor_sharing и session_cached_cursors позволяют разработчикам приложений немного "смягчить" несоблюдение руководящих принципов надлежащей разработки приложений. Однако чтобы полностью обеспечить масштабируемость и производительность системы баз данных Oracle, приложения следует проектировать в соответствии с руководящими принципами корпорации Oracle.

В процессе программирования приложений следует соблюдать следующие общие рекомендации:

операторы SQL, многократно выполняемые в одном и том же модуле приложения, следует связывать с конкретными курсорами, которые открываются один раз: их операторы SQL с переменными связывания разбираются один раз и выполняются многократно. Такой способ обеспечивает возможно лучшие время ответа и масштабируемость. Этого можно добиться, используя явные курсоры в программах Oracle Precompiler и надлежащим образом используя описатели операторов (statement handles) в программах Oracle Call Interface. Программы Oracle Precompiler, использующие неявные курсоры, следует запускать с параметрами HOLD_CURSOR=YES, RELEASE_CURSOR=NO для конкретных операторов SQL или с общим параметром MAXOPENCURSORS, значение которого должно быть достаточно высоким для сохранения открытыми всех часто используемых курсоров; значение этого параметра, устанавливаемое по умолчанию и равное 10, обычно слишком мало. Этот подход также используется в программах, написанных на PL/SQL, и при использовании курсоров в циклах и декларации курсоров в статическом SQL;

операторы SQL, многократно выполняемые в разных модулях приложения, для которых нельзя применить описанный выше метод, следует выполнять с использованием переменных связывания. Если это невозможно, или если в уже написанном приложении необходимо воспользоваться преимуществами использования переменных связывания, можно установить в параметре cursor_sharing значения force или similar. Однако см. ниже замечания по приложениям систем поддержки принятия решений (decision support type applications);

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

не следует совершенно отказываться от использования в приложениях переменных связывания, полностью полагаясь на использование параметра cursor_sharing. Главное достоинство этого параметра заключается в повышении производительности существующих приложений, в которых переменные связывания не используются, что помогает избегать дорогостоящего полного разбора. Заметим, проблемы масштабируемости при обработке SQL возникают также из-за выполнения частичного разбора; использование этого параметра не помогает избавиться от частичного разбора. Заметим также, что никаких преимуществ от использования этого параметра не получают приложения, в которых фактически отсутствует многократное выполнение операторов SQL, отличающихся только литералами, а также приложения, не испытывающие реальных проблем с выполнением разбора;

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

Примечательно, что кроме повышения производительности, использование параметра cursor_sharing позволяет обеспечить реальное разделение операторов SQL, которые в противном случае были бы различными из-за различий в литералах (это также влияет на потребление памяти).

И еще следует упомянуть, что для некоторых приложений или некоторых частей приложений, использующих литералы, не желателен запуск с установленными значениями параметра cursor_sharing, если они отличаются от значения по умолчанию. В частности, это справедливо, если в вашем приложении используются литералы, которые не изменяются между вызовами. Примером такого оператора SQL может быть оператор со списком литералов в предикате (IN-list), типа:

select * from elements
where status in (1,4)

Здесь при каждом вызове оператора будут использоваться фактически одни и те же значения литералов: 1 и 4.

Особые ситуации

Использование переменных связывания в операторах SQL либо непосредственно запрограммированных в приложении, либо косвенно с помощью установки значения параметра cursor_sharing, в общем, позволяет повысить производительность. Однако есть случаи, когда их использование либо не оказывает никакого реального влияния на производительность, либо вы подвергаетесь реальному риску снижения ее. В этом разделе рассматриваются эти проблемы и обсуждаются пути их решения в Oracle9i.

Системы поддержки принятия решений (decision support systems)

Системы поддержки принятия решений, такие, как приложения хранилищ данных, характеризуются небольшим количеством пользователей, выполняющими сложные операторы SQL, которые имеют мало повторений или не имеют их вообще. Для каждого сложного оператора SQL также имеет важное значение то, чтобы оптимизатору для выбора правильного плана выполнения была доступна вся необходимая информация. В СУБД Oracle8i это возможно только в том случае, когда переменные связывания не используются. В Oracle8i использование переменных связывания скрывает от оптимизатора фактические значения, поэтому он для генерации лучшего плана выполнения будет использовать эвристику, вместо того чтобы потенциально использовать информацию о реальном распределении данных. Для этого типа приложений основной объем ресурсов используется не на шагах разбора или оптимизации, поэтому сокращение времени выполнения этих шагов мало влияет на общую производительностью. Важнее то, чтобы шаг выполнения не затягивался на минуты и часы, а шаг оптимизации выполнялся с возможно лучшими доступными данными, позволяющими сгенерировать наиболее эффективный план выполнения. В частности, это справедливо, если вы соберете статистики по столбцам, которые предоставляют оптимизатору детализированную информацию о доступных данных.

Таким образом, для данного типа приложений, работающих под управлением СУБД Oracle8i, не должны использоваться переменные связывания, а значение параметра cursor_sharing должно быть установлено как exact, чтобы курсоры и, следовательно, планы выполнения не разделялись.

Считывание значений переменных связывания в СУБД Oracle9i

В СУБД Oracle9i оптимизатор считывает фактические значения переменных связывания, если они доступны. В результате оптимизатор при использовании переменных связывания может использовать собранные статистики на уровне таблиц, индексов или столбцов. Это выгодно как для приложений, которые были написаны с использованием переменных связывания, так и для приложений, в которых литералы заменяются переменными связывания (при соответствующей установке значения параметра cursor_sharing).

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

Параметр cursor_sharing в СУБД Oracle9i

В СУБД Oracle9i параметр cursor_sharing может иметь значение similar (аналогичные операторы), которое позволяет средствам разделения курсоров учитывать особенности запросов в системах поддержки принятия решений. Рассмотрим два оператора SQL, показанных ниже. Предположим, что таблица emp имеет индекс первичного ключа (столбец empno) и индекс столбца deptno:

select * from emp where empno=1234
                и
select * from emp where deptno<400

В первом случае будет выполняться поиск по индексу первичного ключа, и это будет лучшим планом выполнения; решение, принятое оптимизатором не будет изменено, если литерал 1234 будет заменен другим литералом, таким, как 5678. Следовательно, курсоры могут благополучно разделяться, не оказывая никакого влияния на производительность; такие литералы называются надежными литералами (safe literals). Во втором случае с условием неравенства желательно, чтобы оптимизатор сгенерировал другой план выполнения, если, например, литерал 400 будет заменен другим литералом, таким, как 10. Следовательно, несмотря на то что два оператора SQL разбираются одинаково, они не будут иметь разделяемого плана выполнения; такие литералы называются ненадежными литералами (unsafe literals). В СУБД Oracle9i это распознавание осуществляется механизмом разделения курсоров, если значение параметра cursor_sharing – similar.

Значение similar в параметре cursor_sharing также выгодно использовать для изменения поведения механизма разделения курсоров, когда в операторах SQL используются литералы, которые фактически не изменяются между вызовами, как в показанном выше примере оператора со списком литералов в предикате (IN-list).

Кажется заманчивым в СУБД Oracle9i устанавливать параметр cursor_sharing=similar как для OLTP-систем, так и для систем поддержки принятия решений, однако существуют случаи, когда это не выгодно. В тех случаях, когда в операторах SQL есть литералы, фактические значения которых инициируют оптимизацию, а сами значения изменяются почти в каждом операторе, производительность выполнения операторов будет равна производительности выполнения операторов без разделения курсоров (то есть как при установке значения параметра по умолчанию: cursor_sharing=exact), во время которого выполняется полный разбор каждого оператора. Поэтому в таких случаях не следует использовать разделение курсоров. В тех случаях, когда фактические значения литералов изменяются лишь несколько раз, установка параметра cursor_sharing=similar может улучшить масштабируемость и сократить время ответа. Однако представляется, что это редкие случаи, установка параметра cursor_sharing=similar в общем не дает какого-либо положительного эффекта по сравнению с надлежащим программированием приложений. Результаты тестирования, приведенные в приложении, ясно показывают, что установка параметра cursor_sharing=similar реально приводит к небольшому снижению производительности, если фактическое значение литерала (инициирующее оптимизацию) изменяется между вызовами.

Заключение

СУБД Oracle написана для поддержки приложений с масштабированием от десятков до тысяч пользователей. Обеспечить такое масштабирование можно на стадии разработки приложений, следуя определенным рекомендациям. Некоторые параметры инициализации СУБД Oracle могут быть использованы для изменения поведения ядра СУБД – как для обеспечения некоторого повышения производительности некорректно написанных приложений, так и для дополнительного повышения производительности корректно написанных приложений.

В этой статье рассмотрены:

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

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

cursor_sharing	Этот параметр позволяет повысить масштабируемость и производительность приложений, которые были написаны с использованием в операторах SQL литералов, а не переменных связывания. Использование значения force этого параметра может привести к большому увеличению пропускной способности и к некоторому увеличению масштабируемости. В СУБД Oracle8i в системах поддержки принятия решений и приложениях хранилищ данных его следует использовать осторожно или не использовать вообще. В СУБД Oracle9i появилось новое значение этого параметра similar, которое во многих случаях позволяет разделять курсоры в системах поддержки принятия решений и приложениях хранилищ данных. Решение об установке значения этого параметра обычно принимает администратор базы данных, когда многократно повторяются полные разборы операторов SQL, отличающиеся только литералами.
session_cached_cursors	Установка значения этого параметра, которое находится между фактическим количеством используемых курсоров и значением параметра open_cursors, может привести к некоторому повышению масштабируемости и пропускной способности за счет некоторого увеличения нагрузки на ЦП (установка значений, превышающих 50-100, делает этот параметр бесполезным). Кроме того, немного увеличивается потребление памяти, но этим обычно можно пренебречь. Решение об установке значения этого параметра принимает, как правило, администратор базы данных, когда многократно повторяются частичные разборы операторов SQL.
cursor_space_for_time	Если установить в этом параметре значение true, то можно дополнительно повысить масштабирование хорошо написанных приложений за счет увеличения потребления памяти.

СУБД Oracle9i

В СУБД Oracle9i появились новые средства (значение similar для параметра cursor_sharing и считывание значений переменных связывания), которые могут помочь немного повысить производительность плохо написанных приложений. Тем не менее необходимо подчеркнуть, что возможно лучшая производительность обеспечивается использованием надлежащих методов проектирования приложений, которые фактически не изменились в СУБД Oracle9i. Эти методы можно представить очень просто:

• приложения (обычно OLTP- приложения), многократно выполняющие одни и те же операции, должны инициировать разбор операторов SQL только один раз, использовать переменные связывания и реально повторять только шаг выполнения;
• приложения (обычно приложения систем поддержки принятия решений), выполняющие операции, которые редко повторяются, должны инициировать разбор операторов SQL, использующие литералы, чтобы у оптимизатора была возможность прибегать ко всей доступной информации.

Приложение: результаты тестирования

В данной статье обсуждается эффективность разных способов выполнения операторов SQL: с использованием различных моделей программирования и параметров инициализации ÇУБД Oracle. Тесты были специально спроектированы, чтобы показать эту эффективность. Прогоны тестов выполнялись в испытательной системе с 24 ЦП. Ядро каждого теста – цикл, выполняющий оператор SQL одну тысяч раз, и каждый тест выполнялся как одиночная программа, а также как 3, 6, 12 и 24 параллельно выполняемых копии. Во всех случаях измерялись среднее значение суммы использованного времени всеми копиями, а также время, затраченное на ожидание защелок (время ЦП извлекалось из статистики ÇУБД Oracle 'CPU used by this session' – "время ЦП, использованное в данном сеансе", а время ожидания защелок из данных события 'latch free' – "время ожидания защелки, занятой другим процессом"). Абсолютные значения результатов не имеют никакого значения, но результаты можно сравнивать для демонстрации преимущества использования конкретных моделей программирования приложений или параметров инициализации ÇУБД Oracle.

В первом тесте используются переменные связывания, как советует корпорация Oracle для обеспечения лучшей производительности OLTP-приложений. Он демонстрирует эффективность моделей программирования, в которых каждый оператор SQL разбирается и выполняется или один раз разбирается и многократно выполняется, а также эффективность использования параметров session_cached_cursors и cursor_space_for_time startup.

Второй и третий тесты демонстрируют использование параметра cursor_sharing. Второй тест – типичный пример OLTP-приложения с надежными литералами: никакие литералы не инициируют оптимизацию и используется установка параметра cursor_sharing=force. Третий тест – вариант с ненадежными литералами, в котором используется установка параметра cursor_sharing=similar.

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

Тестирование параметров session_cached_cursors, cursor_space_for_time и моделей программирования

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

	1	3	6	12	24
В программе повторяются разбор и выполнение оператора SQL, параметр session_cached_cursors не используется.	74	78	78	95	146
В программе повторяются разбор и выполнение оператора SQL, установка параметра session_cached_cursors=1.	68	67	66	73	83
В программе выполняется только один разбор, повторяется выполнение оператора SQL, параметр cursor_space_for_time не используется.	33	32	37	43	47
В программе выполняется только один разбор, повторяется выполнение оператора SQL, установка параметра cursor_space_for_time=true.	28	28	31	35	33

Тестирование параметра cursor_sharing и операторов с надежными литералами

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

	1	3	6	12	24
В тесте используются надежные литералы, установка параметра cursor_sharing=exact (то есть разделение курсоров не используется).	349	407	499	995	2135
В тесте используются надежные литералы, установка параметра cursor_sharing=force.	85	95	97	102	164

Тестирование параметра cursor_sharing и операторов с ненадежными литералами

В тестовой программе выполняется запрос индексированного столбца таблицы с несимметричным распределением данных. Статистики столбца собраны вместе, поэтому в некоторых запросах будет использоваться полный просмотр таблицы, а в некоторых - поиск по индексу. Тесты демонстрируют эффективность использования ненадежных литералов, которые более или менее часто изменяются между вызовами, а также эффективность установки параметра cursor_sharing=similar. Во всех тестах открытие и закрытие курсоров выполняется за пределами циклов, в цикле повторяются разбор и выполнение операторов SQL. Кроме того, во всех тестах фактически используется корректный план выполнения (полный просмотр таблицы или поиск по индексу), поэтому различия в результатах не связаны с некорректным планом выполнения.

Заметим: результаты тестирования можно сравнивать только в пределах каждой подгруппы тестов (подгруппы разделены пустой строкой), так как выполняемые в разных подгруппах тесты отличаются друг от друга и имеют разную частоту изменения ненадежных литералов. Результаты нельзя также сравнивать с результатами первых двух тестов.

	1	3	6	12	24
В тесте используются ненадежные литералы, которые редко изменяются, параметр cursor_sharing не используется.	557	607	670	1058	2355
В тесте используются ненадежные литералы, которые редко изменяются, установка параметра cursor_sharing=similar.	318	350	352	396	554
В тесте используются ненадежные литералы, которые изменяются через каждые 10 вызовов, параметр cursor_sharing не используется.	321	389	489	938	1993
В тесте используются ненадежные литералы, которые изменяются через каждые 10 вызовов, установка параметра cursor_sharing=similar.	120	139	133	158	222
В тесте используются ненадежные литералы, которые изменяются через каждые 3 вызова, параметр cursor_sharing не используется.	330	365	485	900	1949
В тесте используются ненадежные литералы, которые изменяются через каждые 3 вызова, установка параметра cursor_sharing=similar.	172	194	210	264	524
В тесте используются ненадежные литералы, которые непрерывно изменяются, параметр cursor_sharing не используется.	316	358	468	846	1838
В тесте используются ненадежные литералы, которые непрерывно изменяются, установка параметра cursor_sharing=similar.	369	417	505	824	1724

Благодарности

Результаты, приведенные в данной статье, были получены, когда господин Энгсиг (Engsig) был сотрудником корпорации Oracle Corporation. Господин Энгсиг хочет поблагодарить своих коллег в корпорации Oracle за помощь в проработке концепций, в особенности Санджея Калускара (Sanjay Kaluskar), Грэма Вуда (Graham Wood), Дебю Хаттерье (Debu Chatterjee), Лекса де Хаана (Lex De Haan) и Сесилию Джервазио (Cecilia Gervasio).