Специализированные приемы оптимизации PL/SQL

Способы специальной оптимизации кода PL/SQLВсегда используйте 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.

  

Вас заинтересует / Intresting for you:

Встроенные методы коллекций PL...
Встроенные методы коллекций PL... 2307 просмотров sepia Tue, 29 Oct 2019, 09:54:01
Управление приложениями PL/SQL...
Управление приложениями PL/SQL... 1511 просмотров Rasen Fasenger Mon, 22 Oct 2018, 04:44:08
Символьные функции и аргументы...
Символьные функции и аргументы... 3647 просмотров Анатолий Wed, 23 May 2018, 18:54:01
Тип данных RAW в PL/SQL
Тип данных RAW в PL/SQL 1809 просмотров Doctor Thu, 12 Jul 2018, 08:41:33

Comments on Специализированные приемы оптимизации PL/SQL

Будьте первым прокомментировавшим
Пожалуйста, авторизуйтесь, для комментирования