Всегда используйте FORALL и BULK COLLECT для любых нетривиальных многострочных операций SQL (то есть операций, в которых задействовано более нескольких десятков строк). Всегда ищите возможность организовать кэширование данных. А для многих задач обработки данных следует активно рассмотреть использование конвейерных функций. Иначе говоря, некоторые приемы оптимизации настолько эффективны, что должны использоваться при любой возможности.
Другие приемы оптимизации работают только в специальных ситуациях. Например, замена типа данных INTEGER типом PLS_INSTEGER принесет сколько-нибудь заметную пользу только в программе, интенсивно обрабатывающей числовые данные.
Именно этой теме и посвящена настоящая статья: в ней рассматриваются функции PL/SQL, способные значительно повысить производительность, но только в особых ситуациях. В общем случае вам не стоит изначально беспокоиться о применении всех этих возможностей. Направьте усилия на разработку удобочитаемого, легкого в сопровождении кода, а если впоследствии в программе обнаружатся узкие места, посмотрите, не помогут ли вам какие-либо из описанных приемов.
Метод передачи параметров NOCOPY
Режим передачи параметров NOCOPY требует, чтобы ядро PL/SQL передавало аргументы IN OUT по ссылке, а не по значению. Передача по ссылке повышает производительность программ, потому что она предотвращает копирование аргументов в программный модуль. При передаче больших, сложных структур (коллекций, записей или объектов) копирование данных может быть сопряжено с заметными затратами.
Чтобы понять смысл режима NOCOPY и его возможное влияние на производительность, необходимо ознакомиться с тем, как PL/SQL передаются параметры. Существует два способа передачи:
- По ссылке. С соответствующим формальным параметром связывается указатель, а не фактическое значение. После этого и формальный и фактический параметры ссылаются на одну ячейку памяти, содержащую значение параметра.
- По значению. Значение фактического параметра копируется в соответствующий формальный параметр. Если впоследствии программа завершается без инициирования исключений, значение формального параметра присваивается фактическому. В случае же возникновения ошибки измененное значение обратно не передается. Без использования
NOCOPYпередача параметров вPL/SQLвыполняется по следующим правилам.
| Режим | Передача (по умолчанию) |
| IN | По ссылке |
| OUT | По значению |
| IN OUT | По значению |
Из этих определений и правил следует, что передача большой структуры в режиме OUT или IN OUT производится по значению, а копирование может привести к потере производительности и лишним затратам памяти. Для предотвращения всех этих неприятностей и служит секция NOCOPY. Она включается в объявление параметра следующим образом:
имя_параметра [ IN | IN OUT | OUT | IN OUT NOCOPY | OUT NOCOPY ] тип_данных_
параметра Секция NOCOPY используется только совместно с режимами OUT и IN OUT. Пример списка параметров с секцией NOCOPY:
PROCEDURE analyze_results (
date_in IN DATE,
values IN OUT NOCOPY numbers_varray,
validity_flags IN OUT NOCOPY validity_rectype
);
Применяя секцию NOCOPY, следует учитывать два важных обстоятельства:
- Каждый раз при вызове подпрограммы с выходным параметром, объявленным с
NOCOPY, значение фактического параметра, соответствующего выходному, устанавливается вNULL. - Секция
NOCOPY— это рекомендация, а не команда, поэтому не исключено, что компилятор самостоятельно примет решение о невозможности выполнения запроса. Ограничения на использованиеNOCOPYперечислены в следующем разделе.
Ограничения на использование NOCOPY
Иногда компилятор PL/SQL игнорирует секцию NOCOPY и использует метод передачи по значению, который задан для параметров с режимами OUT и IN OUT по умолчанию. Это может произойти в следующих ситуациях:
- Фактический параметр является элементом ассоциативного массива. Секция
NOCOPYможет применяться ко всей коллекции, но не к ее отдельным элементам. Данное ограничение легко обойти: скопируйте структуру в переменную, скаляр или запись, а затем передайте ее как параметр, объявление которого содержит секциюNOCOPY. - Некоторые ограничения на фактические параметры. Под действием некоторых ограничений секция
NOCOPYигнорируется. Например, к ним относится указание количества знаков в дробной части для числовых значений или ограничениеNOT NULL. На строковые переменные, объявленные с ограничением длины, это не распространяется. - Фактические и формальные параметры представляют собой записи. Одна или обе записи объявлены с использованием атрибута
%ROWTYPEили%TYPE, а ограничения на соответствующие поля этих двух записей разные. - Передача фактического параметра требует неявного преобразования типов данных. Выход из этой ситуации может быть следующим: поскольку всегда лучше использовать явное преобразование типов данных, выполните его, а затем передавайте преобразованные значения как параметры, в объявлении которых имеется секция NOCOPY.
- Обращение к подпрограмме осуществляется путем внешнего или удаленного вызова процедуры. В таких ситуациях
PL/SQLвсегда будет передавать фактические параметры по значению.
Эффективность NOCOPY
Итак, насколько же NOCOPY ускорит вашу программу? Чтобы ответить на этот вопрос, я создал пакет с двумя процедурами:
PACKAGE nocopy_test
IS
TYPE numbers_t IS TABLE OF NUMBER;
PROCEDURE pass_by_value (numbers_inout IN OUT numbers_t);
PROCEDURE pass_by_ref (numbers_inout IN OUT NOCOPY numbers_t);
END nocopy_test;
Каждая процедура удваивает значение элемента вложенной таблицы:
PROCEDURE pass_by_value (numbers_inout IN OUT numbers_t)
IS
BEGIN
FOR indx IN 1 .. numbers_inout.COUNT
LOOP
numbers_inout (indx) := numbers_inout (indx) * 2;
END LOOP;
END;
Затем для каждой процедуры:
- Вложенная таблица была заполнена 100 000 строк данных.
- Процедура была вызвана 1000 раз.
В Oracle Database 10g были получены следующие результаты:
By value (without NOCOPY) - Elapsed CPU : 20.49 seconds.
By reference (with NOCOPY) - Elapsed CPU : 12.32 seconds.
Однако в Oracle Database 11g результаты были такими:
By value (without NOCOPY) - Elapsed CPU : 13.12 seconds.
By reference (with NOCOPY) - Elapsed CPU : 12.82 seconds.
Я провел аналогичные тесты с коллекциями строк — с аналогичными результатами. После проведения многократных тестов я делаю вывод, что до Oracle Database 11g наблюдался значительный прирост производительности, а в Oracle Database 11g этот прирост существенно сократился. Как я полагаю, это объясняется общей оптимизацией ядра PL/SQL в новой версии.
Недостатки NOCOPY
В зависимости от приложения конструкция NOCOPY может повысить производительность программ с параметрами IN OUT и OUT. Однако за потенциальный прирост приходится расплачиваться: если программа завершается с необработанным исключением, вы не можете доверять значениям в фактическом параметре NOCOPY.
Что значит «доверять»? Давайте разберемся, что происходит в PL/SQL с параметрами при завершении программы с необработанным исключением. Предположим, я передаю своей процедуре calculate_totals запись IN OUT. Исполнительное ядро PL/ SQL сначала создает копию этой записи, а затем во время выполнения программы вносит изменения в копию. Фактический параметр не изменяется до того момента, когда процедура calculate_totals успешно завершится (без передачи исключения). В этот момент локальная копия переносится в фактический параметр, а программа, вызвавшая calculate_totals, может обратиться к измененным данным. Но если процедура calculate_totals завершится с необработанным исключением, вызывающая программа может быть уверена в том, что значение фактического параметра осталось неизменным.
С рекомендацией NOCOPY эта уверенность исчезает. При передаче параметра по ссылке (эффект NOCOPY) любые изменения, вносимые в формальный параметр, также немедленно вносятся в фактический параметр. Предположим, программа calculate_totals читает коллекцию из 10 000 строк и вносит изменения в каждую строку. Если ошибка возникла в строке 5000 и была передана из calculate_totals необработанной, коллекция (фактический параметр) будет изменена только наполовину.
Файл nocopy.tst на сайте github демонстрирует проблемы с использованием NOCOPY. Запустите сценарий и разберитесь во всех тонкостях этого режима передачи параметров, прежде чем использовать его в своих приложениях.
В целом, будьте внимательны при использовании рекомендации NOCOPY. Используйте ее только тогда, когда вы твердо уверены в наличии проблем, связанных с передачей параметров; будьте готовы к возможным последствиям при инициировании исключений.
Руководитель разработки PL/SQL Брин Луэллин придерживается иных взглядов на NOCOPY: он рекомендует широко применять эту возможность в программах. Брин полагает, что побочные эффекты частично измененных структур данных не должны представлять большой опасности, потому что эта ситуация возникает только при появлении непредвиденных ошибок. В таких случаях почти всегда следует прервать работу приложения, сохранить информацию об ошибке и передать исключение во внешний блок. Тот факт, что коллекция находится в неопределенном состоянии, в этот момент уже не важен.
Выбор типа данных
При выполнении относительно небольшого количества операций не так уж важно, будет ли PL/SQL выполнять неявные преобразования или использовать относительно медленную реализацию. С другой стороны, для алгоритмов, сопряженных с большими объемами вычислений, следующие рекомендации могут обеспечить заметный выигрыш.
Избегайте неявных преобразований
PL/SQL, как и SQL, во многих ситуациях выполняет неявные преобразования. Так, в следующем блоке PL/SQL сначала преобразует целочисленное значение 1 в вещественный формат 1.0, прежде чем суммировать его с другим числом и присвоить результат переменной типа NUMBER:
DECLARE
l_number NUMBER := 2.0;
BEGIN
l_number := l_number + 1;
END;
Многие разработчики знают, что выполнение неявных преобразований в командах SQL может привести к снижению производительности. Неявные преобразования в PL/ SQL тоже могут повлиять на производительность, хотя и не так серьезно, как преобразования в SQL.
Чтобы проверить влияние неявных преобразований на производительность в вашей среде, запустите сценарий test_implicit_conversion.sql.
Используйте тип PLS_INTEGER для интенсивных целочисленных вычислений
Целочисленная переменная, объявленная с типом PLS_INTEGER, расходует меньше памяти по сравнению с INTEGER и выполняет свою работу более эффективно за счет использования аппаратной поддержки вычислений. В программе с интенсивной обработкой числовых данных даже простое изменение типа может заметно повлиять на производительность. Более подробная информация о разных целочисленных типах приведена в разделе «Тип PLS_INTEGER» вот этого блога.
используйте тип BINARY_FLOAT или BINARY_DOUBLE для интенсивных вещественных вычислений
В Oracle10g появились два новых вещественных типа, BINARY_FLOAT и BINARY_DOUBLE. Они соответствуют стандарту IEEE 754 и используют машинную реализацию математических операций, что делает их более эффективными по сравнению с NUMBER или INTEGER. За дополнительной информацией обращайтесь к разделу «Типы BINARY_FLOAT и BINARY_DOUBLE» этой статьи.
Оптимизация вызовов функций в SQL (версия 12.1 и выше)
Oracle Database 12c предоставляет два важных усовершенствования, повышающих эффективность вызова функций PL/SQL в командах SQL:
- Секция
with function. - Директива
UDF.
Синтаксис WITH FUNCTION более подробно рассматривается в этом блоге.
Директива UDF предоставляет гораздо более простое средство повышения эффективности вызовов функций из SQL. Чтобы воспользоваться ею, достаточно добавить следующую строку в раздел объявлений функции:
PRAGMA UDF;
Эта директива сообщает Oracle: «Эта функция будет в основном вызываться из SQL, а не из блоков PL/SQL». Oracle использует эту информацию для сокращения затрат на переключения контекста с SQL на PL/SQL при выполнении этой функции.
В результате функция значительно быстрее выполняется из SQL (руководитель группы разработки PL/SQL утверждает, что возможно четырехкратное повышение производительности), но при выполнении из блока PL/SQL она будет работать чуть медленнее (!). Сценарий в файле 12c_udf.sql демонстрирует использование этой возможности. Он сравнивает производительность функций с включенным режимом UDF и без него. Проверьте сами и посмотрите, какой выигрыш может принести использование этой очень простой директивы!
Подведем итог: сначала попробуйте использовать директиву UDF. Если она не обеспечит заметного прироста скорости, тогда пробуйте WITH FUNCTION.
