Процесс компиляции нового курсора называется полным разбором (и сам по себе заслуживает отдельной книги); в контексте этой главы он может быть упрощенно представлен четырьмя этапами:
Проверка
Курсор проверяется на соответствие синтаксическим правилам SQL, также проверяются объекты (таблицы и столбцы), на которые он ссылается.
Курсор компилируется в исполняемый вид и загружается в разделяемый пул сервера базы данных. Для определения местоположения курсора в разделяемом пуле используется его адрес.
Оптимизатор по стоимости (cost-based optimizer - CBO) Oracle определяет наилучший для данного курсора план выполнения и присоединяет его к курсору.
ASCII-значения всех символов курсора складываются и передаются в функцию хеширования. Эта функция рассчитывает значение, по которому курсор легко может быть найден при повторном обращении. Данное значение называется хеш-значением курсора. Ниже в этом разделе мы еще вернемся к ASCII-значениям.
Значительная часть активности защелок БД приходится на период выполнения этих операций, так как Oracle не может позволить изменять используемые курсором объекты (таблицы и столбцы) во время его проверки и компиляции. Выполнение этих задач почти полностью ложится на процессор, поэтому во время компиляции наблюдается большой расход процессорного времени сервера БД. Здесь важно то, что основная работа по извлечению записей из-за этого откладывается. Последовательное выполнение одного и того же курсора (одной или несколькими программами) позволяет заменить дорогостоящий процесс полного разбора простой проверкой наличия доступа к используемым объектам (таблицам, представлениям и т. п.) и перейти сразу к извлечению записей. Благодаря этому экономится значительное время.
4439
Общее количество выполненных базой данных полных разборов можно получить из таблицы V$SYSSTAT следующим запросом:
Если количество полных разборов в некотором приложении постоянно растет, то это говорит о том, что оно не использует в полной мере преимущества повторного использования курсоров.
Даже после того, как курсор подвергся полному разбору, при повторном использовании он может потребовать дополнительного разбора. Однако этот процесс будет гораздо менее затратным, он в основном сводится к проверке безопасности, удостоверяющей, что запросивший выполнение курсора пользователь имеет права на доступ к его объектам. Такая неполная обработка называется частичным разбором.
Общее количество выполненных базой данных частичных разборов можно рассчитать, вычтя из общего числа разборов число полных разборов:
SOFT_PARSE
В идеальном случае эти значения не должны заметно расти при работе приложения. Однако в действительности некоторое увеличение почти всегда происходит, так как даже простейший частичный разбор потребляет некоторое количество процессорного времени.
Хорошей практикой является ограничение количества разборов курсора - оптимальное значение равно, конечно, единице. Одним из путей в достижении идеала будет предварительный разбор всех курсоров, которые, возможно, будут выполняться вашим приложением. В таком случае при старте приложения все курсоры уже будут ждать его в разделяемом пуле. Однако этот подход связан с большой трудоемкостью при сопровождении больших приложений и при использовании в них не- регламентируемых (ad hoc) запросов. Поэтому лучше понести затраты один раз при первом выполнении курсора и принять меры к тому, чтобы в дальнейшем он при каждой возможности использовался повторно.
В этой главе, если явно не оговорено обратное, параметр CURSOR SHARING во всех примерах установлен в значение EXACT. Срав- f jf; нение точного и неточного соответствий см. ниже в разделе «Алгоритмы сопоставления».
Время, затраченное базой данных на разбор (процессорное и фактическое), тоже можно получить из таблицы V$SYSSTAT.
В следующих разделах мы объясним, как Oracle принимает решение о повторном использовании курсора. Эти знания будут исключительно полезны при планировании повторного использования курсора. К сожалению, многие пишущие на PL/SQL разработчики находятся в блаженном неведении даже о существовании такой концепции, поэтому администраторам вдвойне необходимо понимание принципов повторного использования курсоров и последствий этого использования. Сначала мы рассмотрим некоторые детали алгоритма хеширования Oracle, а затем перейдем к нюансам повторного использования курсоров. Рекомендуем вам прочитать весь раздел, прежде чем встраивать в свое приложение механизмы (реальные или предполагаемые) повторного использования.
Чтобы определить, может ли планируемый к выполнению курсор воспользоваться уже скомпилированной версией из разделяемого пула, Oracle применяет сложный алгоритм. Вот его упрощенное изложение:
1. Рассчитать сумму ASCII-значений всех символов курсора (исключая переменные связывания). Например, сумма ASCII-значений следующего курсора равна 2556:
Это значение рассчитывается как ASCII(S) + ASCII(E) + ASCII(L)... или 83 + 69 + 76 и т. д.
Применить алгоритм хеширования к полученной сумме.
Проверить наличие в разделяемом пуле курсора с таким же значением кэша.
Если такой курсор найден, он может быть использован повторно.
Обратите внимание: мы говорим «может» быть использован повторно. В большинстве случаев совпадения ASCII-кэша достаточно, но не всегда. Пояснения приводятся в этом разделе далее.
В пункте 1 говорится, что используются ASCII-значения всех символов. Поэтому при планировании повторного использования курсора надо учитывать даже такую мелочь, как регистр символов. Рассмотрим два курсора, выполняющихся непосредственно в SQL*Plus.
Очевидно, что оба они делают в точности одно и то же, - извлекают дату заказа с номером 11. Единственное отличие заключается в том, что во втором курсоре использована прописная буква R. Этого достаточно, чтобы Oracle посчитал их разными, в результате чего в разделяемом пуле окажутся оба курсора.
Столбец EXECUTIONS показывает, сколько раз выполнялся определенный курсор, а столбец PARSE_CALLS - сколько раз курсор подвергался разбору. Оба курсора потребовали полного разбора, так как их суммы ASCII-значений не совпали.
Такой подход может показаться слишком строгим и безжалостным, но таким он и должен быть, потому что база данных не может позволить себе тратить время на предварительный анализ или переформатирование курсора. У нее есть дела поважнее, например выполнение вашего приложения. В последних версиях Oracle появилась возможность переформатирования литералов в курсорах, способствующая их повторному использованию (см. раздел «Алгоритмы сопоставления» ниже в той главе), но это чревато дополнительной нагрузкой при выполнении запросов с литералами.
Один из наиболее удачных способов воспользоваться преимуществами автоматического переформатирования курсоров и стимулировать их повторное использование заключается в помещении их в PL/SQL, как показано в следующем анонимном блоке:
Компилятор PL/SQL переформатирует отличающиеся курсоры, и в разделяемом пуле они будут представлены одним экземпляром. На первый взгляд улучшение незначительно, но будучи реализовано в масштабе всего приложения, оно может дать существенный выигрыш в производительности, так как защелкам разделяемого пула придется отслеживать меньшее число курсоров.
Данная возможность доступна начиная с Oracle8i Database вплоть до Oracle Database 10^ Release 2 с досадным пробелом в Oracle9/ Database Release 2.
Нам представляется, что из двух компиляторов - PL/SQL и SQL - первый ведет себя более снисходительно. Тем не менее каждое выполнение нашего автономного блока влечет за собой проверку и разбор каждого из курсоров. Поэтому, даже если мы обойдемся одним курсором, счетчик полных разборов будет постоянно расти. Десять выполнений нашего автономного блока вызовут 20 полных разборов:
Хорошая новость заключается в том, что PL/SQL позволяет исключить практически все полные разборы простым перенесением курсоров в хранимые процедуры, как в следующем примере:
Эти два разбора имели место при первой компиляции процедуры. После этого процедура и все ее содержимое (включая курсоры) считаются корректными и не требуют повторного разбора.
Простое перемещение двух этих курсоров в PL/SQL позволяет воспользоваться двумя его ключевыми возможностями, особенно полезными администраторам БД:
Компилятор PL/SQL способствует повторному использованию, будучи более снисходителен к структуре курсоров. Степень этой снисходительности обсуждается в следующем разделе.
После того как курсор был скомпилирован в составе процедуры, пакета или функции PL/SQL, он автоматически считается разобранным и действительным до тех пор, пока остаются действительными процедура, пакет или функция.
Как уже упоминалось, компилятор PL/SQL прилагает дополнительные усилия, способствующие повторному использованию курсора, отыскивая мелкие отличия типа лишних пробелов, изменений регистра и переводов строки. Например, для следующей процедуры в разделяемом пуле будет создан единственный скомпилированный курсор:
Такой предварительный разбор выполняется в PL/SQL для всех курсоров, что позволяет обнаружить совпадения для всего хранимого кода. Например, для приведенного ниже курсора будет использован скомпилированный курсор из процедуры forgiveness.
Еще один фактор, который надо учитывать при планировании повторного использования курсора, - это использование литералов. Рассмотрим простой фрагмент кода, выполняющий два элементарных запроса. Обратите внимание на то, что тексты запросов отличаются только номерами заказов, заданными литералами.
Каждый из курсоров был однократно разобран и выполнен, так как их ASCII-суммы не совпали. Более того, шанс быть повторно использованными появится у них только при выполнении запроса с явно указанным номером заказа 100 или 200. Такой подход далек от оптимального: при обработке десятков тысяч заказов потребуется выполнять десятки тысяч полных разборов.
Простейший способ добиться повторного использования этих курсоров с помощью PL/SQL заключается в их параметризации, как показано в следующем фрагменте:
В разделяемом пуле после его очистки и выполнения новой функции будет следующее:
Разделяемый пул содержит единственный курсор со счетчиком выполнений, равным 2, что говорит о том, что он уже был повторно использован.
Теперь любой другой код, выполняющий такой же запрос для получения даты заказа по его номеру, может воспользоваться уже скомпилированной версией. Например, следующая процедура будет использовать скомпилированную версию данного курсора:
Скомпилированный ранее курсор был использован второй процедурой. Потребовался только частичный разбор. Из этого примера видно, что хорошей практикой является отказ от использования литералов в курсорах везде, где это возможно.
Но что если приложение не может быть модифицировано из-за отсутствия исходных текстов или из-за недостатка времени или денег? В таких ситуациях Oracle может кое-чем помочь, о чем и рассказывается в следующем разделе.
По умолчанию повторное использование курсоров в базе данных требует их полного совпадения. За пределами PL/SQL это правило не терпит компромиссов - ASCII-значения должны совпадать в точности. Есть только черное и белое, и никаких полутонов. Курсоры или совпадают, или нет. В PL/SQL компилятор, переформатируя курсоры, старается помочь их повторному использованию, но это все, что он может сделать. Это ограничение особенно досадно, когда курсоры отличаются лишь текстом литералов. Рассмотрим два курсора:
Оба они выполняют одно и то же действие, извлекая значение or- der_date для заданного заказа, а сумма их ASCII-значений отличается всего на 3 единицы. Но стараниями алгоритма точного сопоставления оба удостаиваются собственного полного разбора и места в разделяемом пуле (даже если находятся в коде PL/SQL).
В конце концов, разделяемый пул может быть заполнен аналогичными курсорами, для которых повторное выполнение так близко - и все еще так далеко. Такое поведение в отношении литералов в курсорах
Применяемый алгоритм совместного использования определяется параметром инициализации CURSOR_SHARING:
Наряду с заданием данного параметра для БД в целом вы можете определить его для отдельного сеанса при помощи команды
ALTER SESSION.
свойственно отдельным коммерческим приложениям, а также ранним версиям ODBC. С учетом такого поведения в Oracle добавлен второй алгоритм совместного использования курсоров - сопоставление подобных (similar matching). «Подобные» означает здесь, что от курсоров требуется совпадение ASCII-сумм, не учитывающих литералы.
Вот что находится в разделяемом пуле после выполнения отличающихся литералами курсоров в случае применения алгоритма сопоставления подобных:
Явно заданные значения были преобразованы в переменные связывания, что значительно повысило вероятность повторного использования.
Значение SIMILAR не означает, что Oracle будет слепо подставлять переменные связывания вместо каждого найденного им литерала. Например, он не станет делать этого, если в результате значительно изменится план выполнения, выбранный оптимизатором по стоимости - как в приведенном ниже примере с существенно асимметричным распределением заказов по регионам.
Если оптимизатор располагает актуальной статистикой, Oracle учтет эту асимметрию и выберет разные планы выполнения для получения записей по каждому из регионов. Для региона 1 он выберет полный просмотр таблицы, так как ему надо просмотреть все записи кроме одной. Для региона 2 он предпочтет выборку одной строки с помощью индекса по полю REGION_ID. Выполним эти запросы с включенным режимом AUTOTRACE, чтобы продемонстрировать сказанное.
В действительности нас интересуют находящиеся в разделяемом пуле курсоры.
Несмотря на то что тексты курсоров после подстановки идентичны, был создан отдельный курсор, так как Oracle обнаружил слишком большие изменения в плане выполнения. Благодаря такой стратегии производительность не страдает при установке режима SIMILAR для совместного использования курсора.
Параметр CURSOR_SHARING может принимать еще и третье значение - FORCE. В соответствии со своим наименованием оно означает принудительное повторное использование курсоров при совпадении их текстов после замены литералов на переменные связывания. При этом методе выполняется прямая подстановка, непосредственно на основании которой оптимизатор по стоимости (CBO - cost-based optimizer) строит план выполнения. Такой путь не всегда оптимален, так как, зная действительное значение, оптимизатор CBO мог бы принять лучшее решение - как было показано в примере с REGION_ID.
После выполнения запроса с подсчетом регионов 1 и 2 с использованием значения FORCE в разделяемом пуле будет находиться только один курсор.
Значение FORCE появилось в Oracle8i Database (8.1.6) еще до того, как в Oracle9i Database появилось значение SIMILAR. Режим SIMILAR был введен после того, как многие администраторы и разработчики сочли метод FORCE слишком грубым в отношении производительности запросов (как было показано в примере выше). Если вы решите воспользоваться одним из этих режимов, мы настоятельно рекомендуем вам основательно протестировать производительность, чтобы убедиться в том, что преимущества совместного использования курсоров не сводятся на нет снижением производительности запросов.
Помните: несмотря на удобство использования режимов SIMILAR и FORCE, они не могут заменить хорошо продуманного и логичного использования курсоров в коде ваших программ.
Помимо совпадения текстов курсоров есть еще ряд факторов, влияющих на повторное использование курсоров - например, несовпадения, определяемые статистикой оптимизатора или настройкой архитектуры Globalization Support (поддержка глобализации, прежде называвшаяся NLS - National Language Support, поддержка национальных языков). В таких ситуациях простого совпадения ASCII-значений недостаточно.
Рассмотрим пример установки режима работы оптимизатора.
Квалифицированные администраторы знают, что в этом случае будут созданы два курсора: тексты совпадают, но использованы разные режимы оптимизации, поэтому Oracle строит для курсоров разные планы выполнения. Вот что находится в разделяемом пуле:
Начиная с Oracle Database 10g Release 1 для однозначной идентификации курсора в таких представлениях, как V$SQL и V$OPEN_CUR- SOR, используется столбец SQL_ID. В более ранних версиях для этой цели применялась комбинация столбцов HASH_VALUE и ADDRESS.
Очевидно, Oracle обнаружил, что курсоры идентичны, так как присвоил им одинаковые идентификаторы, но сделал второй курсор потомком первого.
В данном случае мы знаем, что два курсора потребовались из-за различных режимов оптимизации, но что если бы этой информации у нас не было? Как мы можем понять, зачем понадобился второй курсор? Обратимся к представлению V$SQL_SHARED_CURSOR:
Значение «Y» во втором столбце означает: да, дочерний курсор понадобился из-за различий в оптимизаторе. Мы намеренно ограничились в этом запросе столбцом OPTIMIZER_MISMATCH, чтобы показать причину невозможности повторного использования курсора. На самом деле представление V$SQL_SHARED_CURSOR содержит множество полей (например, в Oracle Database 10g Release 1 их 39), каждое из которых соответствует определенной возможной причине отказа от повторного использования.
Меня всегда интересовало, почему это представление не называется V$SQL_UNSHARED_SQL_CURSOR, так как оно показывает именно это. Так или иначе, это представление очень удобно для диагностики повторного использования курсоров, поэтому рекомендую вам чаще обращаться к нему.
Проблема выбора между явными и неявными курсорами породила многолетнюю дискуссию - в общем, о выборе между конструкциями «OPEN, FETCH, CLOSE» и «SELECT INTO». В этом разделе мы не будем касаться вопросов производительности, так как в последних версиях Oracle проделана большая работа для снятия остроты этой проблемы Вместо этого сосредоточимся на том, что происходит в базе данных, и обсудим различия в применении этих курсоров в PL/SQL, включая и тот факт, что они не всегда совпадают в разделяемом пуле.
В PL/SQL неявные курсоры - это курсоры, которые определяются в момент выполнения. Вот пример:
В ходе выполнения этого кода создается курсор для выборки значения order_date для заказа с номером 100. Таким образом, курсор был неявно определен во время выполнения кода.
Явный курсор - это курсор, который определяется до начала выполнения. Вот простой пример:
Неявный курсор выглядит гораздо проще и короче в записи, поэтому первым побуждением может быть выбор именно этого варианта. Однако явные курсоры имеют ряд преимуществ, оправдывающих удлинение кода PL/SQL; их рассмотрению посвящены следующие два раздела.
Ключевое преимущество явного курсора заключается в наличии у него атрибутов, облегчающих применение условных операторов. Рассмотрим следующий пример: мы хотим найти заказ и, если он найден, выполнить некоторые действия. Первая процедура, использующая неявный курсор, вынуждена заниматься перехватом исключений, чтобы определить, была ли найдена запись.
Вторая процедура, написанная с использованием явного курсора, выглядит гораздо понятнее, так как наличие у курсора атрибута %NOT- FOUND делает очевидным проверяемое условие. Отсутствует также необходимость в дополнительном блоке BEGIN-END, нужном только для реализации логики.
Oracle поддерживает следующие атрибуты курсоров:
|
|
Атрибут
|
Описание
|
%BULK_ROWCOUNT
|
|
%FOUND
|
TRUE, если последняя операция FETCH была успешной; FALSE - в противном случае.
|
%NOTFOUND
|
TRUE, если последняя операция FETCH была неуспешной; FALSE - в противном случае.
|
%ISOPEN
|
|
%ROWCOUNT
|
|
Вы, возможно, знаете, что некоторые из этих атрибутов доступны также для неявных курсоров. Однако программную логику гораздо удобнее реализовывать с помощью явных курсоров, особенно когда в вашей программе несколько курсоров, как показано в этом коротком примере:
| < Предыдущая | Следующая > |
|---|
