Средства оптимизации приложений PL/SQL

Средства оптимизации PL/SQL приложенияВ этом блоге будут представлены программные инструменты и методы, применяемые при оптимизации кода PL/SQL. Они делятся на несколько категорий:

  • анализ использования памяти;
  • выявление «узких мест» в коде PL/SQL;
  • хронометраж;
  • выбор самых быстрых программ, предотвращение зацикливания;
  • использование предупреждений, относящихся к производительности.


 

Анализ использования памяти

Как упоминалось ранее, при оптимизации производительности кода также необходимо учитывать объем памяти, занимаемой вашей программой. Данные программы занимают место в PGA; каждый сеанс, подключенный к базе данных Oracle, имеет собственную область PGA. Таким образом, общие затраты памяти приложения обычно многократно превышают объем памяти, необходимой для одного экземпляра программы. Затраты памяти особенно важны при работе с коллекциями, объектными типами с большим количеством атрибутов и записями с большим количеством полей.

 

Выявление «узких мест» в коде PL/SQL

Прежде чем заниматься оптимизацией приложения, необходимо разобраться, что работает медленно и на чем следует сосредоточить усилия. Oracle и сторонние разработчики

предлагают разнообразные программные средства, упрощающие решение этой задачи; обычно они анализируют команды SQL в вашем коде, предлагают альтернативные реализации и т. д. Эти инструменты чрезвычайно полезны, но они нередко сбивают с толку разработчиков PL/SQL: выдавая объемистые данные из области производительности, они не отвечают на самый важный вопрос: где находятся «узкие места» в вашем коде? Для получения этой информации Oracle предлагает ряд своих встроенных средств. Самые полезные из них:

  • DBMS_PR0FILER — встроенный пакет для сбора профильных данных. При запуске кода Oracle собирает в специальных таблицах подробную информацию о том, сколько времени заняло выполнение каждой строки в вашем коде. Далее вы можете обращаться к этим таблицам с запросами или (предпочтительный вариант) используйте такие продукты, как Toad или SQL Navigator, для вывода данных в удобном графическом виде.
  • DBMS_HPR0F — в Oracle11g появился новый иерархический профайлер, способный легко получать данные производительности из стека вызовов. DBMS_PR0FILER предоставляет «плоские» данные производительности, и при работе с ним трудно получить ответы на вопросы типа: «Сколько времени потрачено на выполнение процедуры ADD_ITEM?» Иерархический профайлер упрощает получение подобной информации.

 

DBMS_PROFILER

На тот случай, если ваш инструментарий не предлагает интерфейс к DBMS_PR0FILER, ниже приводятся некоторые инструкции и примеры.

Прежде всего следует отметить, что Oracle не всегда автоматически устанавливает DBMS_PR0FILER в вашей системе. Чтобы проверить доступность пакета DBMS_PR0FILER, подключитесь к своей схеме в SQL*Plus и введите следующую команду:

DESC DBMS_PR0FILER

Если вы увидите сообщение:

ERROR:
ORA-04043: object dbms_profiler does not exist

значит, вам (или вашему администратору базы данных) придется установить программу. Для этого запустите файл $0RACLE_HOME/rdbms/admin/profload.sql с правами SYSDBA.

Затем выполните файл $ORACLE_HOME/rdbms/admin/proftab.sql в вашей схеме, чтобы создать три таблицы, заполняемые данными DBMS_PR0FILER:

  •  PLSQL_PR0FILER_RUNS — родительская таблица запусков.
  •  PLSQL_PR0FILER_UNITS — выполняемые программные модули.
  •  PLSQL_PR0FILER_DATA — профилировка данных в каждой строке программного модуля.

После того как эти объекты будут определены, можно переходить к сбору профильной информации приложения, для чего пишется код следующего вида:

BEGIN
   DBMS_PROFILER.start_profiler (
      'my application' || TO_CHAR (SYSDATE, 'YYYYMMDD HH24:MI:SS')
   );

   код_приложения;
   DBMS_PROFILER.stop_profiler;
END;

После того как код приложения будет выполнен, вы можете обращаться с запросами к данными таблиц PLSQL_PR0FILER_. Пример запроса, который выводит строки кода, на выполнение которых было затрачено не менее 1% общего времени выполнения:

/* Файл в Сети: slowest.sql */
   SELECT TO_CHAR (p1.total_time / 10000000, '99999999')
         II '-'
         || TO_CHAR (p1.total_occur)
            AS time_count,
            SUBSTR (p2.unit_owner, 1, 20)
         || '.'
         || DECODE (p2.unit_name,
                    '', '<anonymous>',
                    SUBSTR (p2.unit_name, 1, 20))
            AS unit,
         TO_CHAR (p1.line#) || '-' || p3.text text
    FROM plsql_profiler_data p1, 
         plsql_profiler_units p2, 
         all_source p3,
         (SELECT SUM (total_time) AS grand_total 
            FROM plsql_profiler_units) p4 
   WHERE     p2.unit_owner NOT IN ('SYS', 'SYSTEM')
         AND p1.runid = &&firstparm
         AND (p1.total_time >= p4.grand_total / 100)
         AND p1.runid = p2.runid
         AND p2.unit_number = p1.unit_number
         AND p3.TYPE = 'PACKAGE BODY'
         AND p3.owner = p2.unit_owner AND p3.line = p1.line#
         AND p3.name = p2.unit_name 
ORDER BY p1.total_time DESC

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

 

Иерархический профайлер (DBMS_HPR0F)

В Oracle Database 11g появился второй механизм профилирования: DBMS_HPROF, также называемый иерархическим профайлером. Используйте его для получения профиля выполнения кода PL/SQL, упорядоченного по вызовам подпрограмм вашего приложения. «Погодите, — скажете вы, — но разве DBMS_PR0FILER не делает это за меня?» Не совсем. Не-иерархические профайлеры (такие, как DBMS_PR0FILER) сохраняют время, проведенное вашим приложением в каждой подпрограмме, вплоть до времени выполнения отдельных строк кода. Эта информация безусловно полезна, но у ее полезности есть свои ограничения. Часто требуется узнать, сколько времени приложение проводит в конкретной подпрограмме, — то есть «свернуть» профильную информацию до уровня подпрограммы. Именно это и делает новый иерархический профайлер.

Иерархический профайлер PL/SQL выдает информацию о затратах времени на выполнение каждой профилируемой подпрограммы приложения, различая время выполнения SQL и PL/SQL. Профайлер отслеживает разнообразную информацию, включая количество вызовов подпрограммы; проводимое в ней время; время, затраченное на поддерево подпрограммы (то есть с включением подпрограмм нижних уровней) и подробную информацию о связях «родитель — потомок».

Иерархический профайлер состоит из двух компонентов:

  • Сборщик данных — предоставляет API для включения и выключения иерархического профилирования. Исполнительное ядро PL/SQL записывает низкоуровневый вывод профайлера в заданный файл.
  • Анализатор — обрабатывает низкоуровневый вывод профайлера и сохраняет результаты в иерархических таблицах, к которым затем можно обращаться с запросами для вывода профильной информации.

Работа с иерархическим профайлером проходит примерно так:

  1. Убедитесь в том, что вы можете выполнить пакет dbms_hprof.
  2. Убедитесь в том, что у вас имеются привилегии write для каталога, заданного при вызове DBMS_HPROF.START_PROFILING.
  3. Создайте три таблицы профайлера (подробности см. ниже).
  4. Вызовите процедуру dbms_hprof.start_profiling, чтобы запустить сбор данных иерархического профайлера в вашем сеансе.
  5. Выполняйте код своего приложения достаточно долго и многократно, чтобы собранных данных было достаточно для получения интересных результатов.
  6. Вызовите процедуру dbms_hprof.stop_profiling для завершения сбора профильных данных.
  7. Проанализируйте собранные данные и обратитесь с запросами к таблицам профайлера для получения результатов.

Чтобы получить самые точные данные о времени, потраченном при выполнении подпрограмм, сведите к минимуму всю постороннюю деятельность в системе, в которой выполняется приложение.

Конечно, в рабочей системе другие процессы могут замедлить работу вашей программы. Также желательно проводить сбор данных при использовании механизма RAT (Real Application Testing) в Oracle Database 11g и выше для получения реального времени отклика.

Чтобы создать таблицы профайлера и другие необходимые объекты базы данных, запустите сценарий dbmshptab.sql (находящийся в каталоге $ORACLE_HOME/rdbms/admin). Сценарий создает три таблицы:

  •  DBMSHP_RUNS — информация верхнего уровня о каждом запуске программы ANALYZE пакета dbms_hprof.
  •  dbmshp_function_info — подробная информация о выполнении каждой подпрограммы, профилированной при конкретном запуске программы ANALYZE.
  •  dbmshp_parent_child_info — информация «родитель — потомок» для каждой подпрограммы, профилированной в dbmshp_function_info.

Рассмотрим очень простой пример: допустим, я хочу протестировать производительность процедуры intab (предназначенной для вывода содержимого заданной таблицы средствами DBMS_SQL). Итак, сначала я включаю профилирование, указывая, что низкоуровневые данные должны записываться в файл tab_trace.txt в каталоге TEMP_DIR. Этот каталог должен быть предварительно определен командой CREATE DIRECTORY:

EXEC DBMS_HPROF.start_profiling ('TEMP_DIR', 'intab_trace.txt')

Затем вызывается программа (выполняется код приложения):

EXEC intab ('DEPARTMENTS')

Наконец, сеанс профилирования завершается:

EXEC DBMS_HPROF.stop_profiling;

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

Итак, файл трассировки заполнен данными. Я могу открыть его и просмотреть данные — скорее всего, без особой пользы. Гораздо более эффективным применением моего времени и технологий Oracle будет вызов программы ANALYZE из пакета DBMS_HPROF.

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

BEGIN
   DBMS_OUTPUT.PUT_LINE (
      DBMS_HPROF.ANALYZE ('TEMP_DIR', 'intab_trace.txt'));
END;
/

Вот и все! Данные собраны, проанализированы и распределены по таблицам, и теперь я могу выбрать один из двух способов получения профильной информации:

  1. Запустите программу командной строки plshprof (из каталога $ORACLE_HOME/bin/). Программа генерирует очень простые отчеты HTML по данным одного или двух файлов, содержащих низкоуровневый вывод профайлера. Пример низкоуровневого вывода профайлера приведен в разделе «Collecting Profile Data» руководства Oracle Database Development Guide. Далее сгенерированные отчеты в формате HTML просматриваются в любом браузере.
  2. Выполните специально написанный запрос. Допустим, предыдущий блок возвращает номер запуска 177. Следующий запрос возвращает все текущие запуски:
SELECT runid, run_timestamp, total_elapsed_time, run_comment 
   FROM dbmshp_runs

Запрос для получения имен всех профилированных подпрограмм по всем запускам:

SELECT symbolid, owner, module, type, function, line#, namespace 
   FROM dbmshp_function_info

Запрос для вывода информации о выполнении подпрограмм для конкретного запуска:

SELECT FUNCTION, line#, namespace, subtree_elapsed_time 
      , function_elapsed_time, calls 
   FROM dbmshp_function_info 
 WHERE runid = 177

Этот запрос получает информацию «родитель — потомок» для текущего запуска, но результат не представляет особого интереса, так как пользователь видит только значения ключей, а не имена программ:

SELECT parentsymid, childsymid, subtree_elapsed_time, function_elapsed_time 
      , calls
   FROM dbmshp_parent_child_info 
 WHERE runid = 177

Следующий запрос, полученный объединением с таблицей информации о функциях, уже более интересен; теперь пользователь видит имена родительских и дочерних программ, а также время их выполнения и количество вызовов:

SELECT RPAD (' ', LEVEL * 2, ' ') || fi.owner || '.' || fi.module AS NAME
          , fi.FUNCTION, pci.subtree_elapsed_time, pci.function_elapsed_time 
          , pci.calls
       FROM dbmshp_parent_child_info pci JOIN dbmshp_function_info fi 
            ON pci.runid = fi.runid AND pci.childsymid = fi.symbolid 
      WHERE pci.runid = 177 
CONNECT BY PRIOR childsymid = parentsymid 
START WITH pci.parentsymid = 1

Иерархический профайлер — чрезвычайно мощная и полезная программа. Я рекомендую ознакомиться с ее подробным описанием в главе 13 руководства Oracle Database Development Guide.

Хронометраж

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

Пакет DBMS_UTILITY содержит две функции для получения такой информации: DBMS_utility.get_time и dbms_utility.get_cpu_time. Обе функции доступны в Oracle 10g и выше. С их помощью можно легко вычислить время, затраченное на выполнение блока кода (как общее, так и процессорное) с точностью до секунды. Общая схема выглядит так:

  1. Вызовите dbms_utility.get_time (или get_cpu_time) перед выполнением кода.
  2. Выполните код, для которого вы хотите получить хронометражные данные.
  3. Вызовите dbms_utility.get_time (или get_cpu_time) для получения конечной точки интервала. Вычтите начальное время из конечного; разность определяет промежуток времени между этими двумя моментами (в сотых долях секунды).

Пример:

DECLARE
   l_start_time PLS_INTEGER;
BEGIN
   l_start_time := DBMS_UTILITY.get_time; 

   my_program;

   DBMS_OUTPUT.put_line (
      'Elapsed: ' || DBMS_UTILITY.get_time - l_start_time);
END;

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

BEGIN
   sf_timer.start_timer (); 

   my_program;

   sf_timer.show_elapsed_time ('Программа my_program выполнена');
END;

Я рекомендую избегать прямых вызовов dbms_utility.get_time и вместо них использовать пакет таймеров sf_timer по двум причинам:

  •  Повышение производительности: кому захочется вручную объявлять локальные переменные, писать код вызова нескольких встроенных функций и вычисления разности? Гораздо удобнее воспользоваться готовым решением.
  •  Обеспечение корректности данных: простая формула «конец — начало» иногда дает отрицательный результат!

Откуда может появиться отрицательное время? Число, возвращаемое DBMS_UTILITY. GET_TIME, представляет общее количество секунд, прошедших с некоторого момента времени. Если значение становится очень большим (конкретное значение зависит от операционной системы), оно обнуляется, и отсчет начинается заново. Если функция GET_TIME будет вызвана непосредственно перед сбросом, разность окажется отрицательной!

Чтобы предотвратить отрицательные значения, приходится писать код следующего вида:

DECLARE
   c_big_number NUMBER := POWER (2, 32); 
   l_start_time PLS_INTEGER;
BEGIN
   l_start_time := DBMS_UTILITY.get_time; 
   my_program;
   DBMS_OUTPUT.put_line (
      'Elapsed: '
      || TO_CHAR (MOD (DBMS_UTILITY.get_time - l_start_time + c_big_number 
                     , c_big_number)));
END;

Какой нормальный человек, которого к тому же постоянно подгоняет начальство, захочет писать такой код при каждом выполнении хронометража? По этой причине я создал пакет sf_timer, который скрывает технические подробности и упрощает анализ/сравнение результатов.

 

Выбор самой быстрой программы

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

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

  •  Положительные результаты — программа получает действительные входные данные и делает то, что ей положено делать.
  •  Отрицательные результаты — программа получает недействительные входные данные (скажем, несуществующий первичный ключ) и не может выполнить необходимые действия.
  •  Нейтральность алгоритма по отношению к данным — программа хорошо работает с таблицей из 10 строк... а если таблица содержит 10 000 строк? Программа ищет данные в коллекции, но как повлияет на производительность местонахождение совпадения — в начале, в середине или в конце коллекции?
  •  Многопользовательское выполнение — программа хорошо работает для одного пользователя, но ее следует проверить для параллельного многопользовательского доступа. Вы ведь не хотите, чтобы взаимные блокировки обнаружились уже после запуска продукта в эксплуатацию, не так ли?
  •  Тестирование во всех поддерживаемых версиях Oracle — например, если приложение должно надежно работать в Oracle10g и Oracle11g, вы должны выполнить хронометражные сценарии в экземплярах каждой из этих версий.

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

Далее приведена заготовка такой процедуры с вызовами sf_timer:

/* Файл в Сети: compare_performance_template.sql */
PROCEDURE compare_implementations ( 
   title_in      IN VARCHAR2
 , iterations_in IN INTEGER
/*
Параметры, необходимые для передачи данных сравниваемым программам...
*/
)
IS
BEGIN
   DBMS_OUTPUT.put_line ('Compare Performance of <CHANGE THIS>: '); 
   DBMS_OUTPUT.put_line (title_in);
   DBMS_OUTPUT.put_line ('Each program execute ' || iterations_in || ' times.');
   /*
   Для каждой реализации запускается таймер, программа выполняется N раз, 
   после чего выводится затраченное время.
   */
   sf_timer.start_timer;

   FOR indx IN 1 .. iterations_in 
   LOOP
      /* Вызов вашей программы. */
      NULL;
   END LOOP;

   sf_timer.show_elapsed_time ('<CHANGE THIS: Implementation 1');
   --
   sf_timer.start_timer;

   FOR indx IN 1 .. iterations_in 
   LOOP
      /* Вызов вашей программы. */
      NULL;
   END LOOP;
   sf_timer.show_elapsed_time ('<CHANGE THIS: Implementation 2');
END compare_implementations;

Пакет sf_timer используется далее в некоторых примерах этой статьи.

 

Предотвращение зацикливания

Бесконечные циклы редко встречаются в окончательных версиях приложений (при условии, что ваша группа тестирования потрудилась на совесть!) — эта проблема обычно встречается только в процессе разработки. Иногда приходится программировать хитроумную логику завершения циклов, а аварийное прерывание сеанса в ходе тестирования отрицательно сказывается на эффективности.

Для борьбы с этим раздражающим недостатком был разработан пакет Loop Killer. Он базируется на простой идее: даже если вы не знаете, как успешно завершить цикл, вам может быть известно, что выполнение тела цикла более N раз (100, 1000 — в зависимости от ситуации) свидетельствует о возникшей проблеме.

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

Спецификация пакета выглядит так (полный код имеется на сайте книги):

/* Файл в Сети: sf_loop_killer.pks/pkb */
PACKAGE sf_loop_killer 
IS
   c_max_iterations CONSTANT PLS_INTEGER DEFAULT 1000; 
   e_infinite_loop_detected EXCEPTION; 
   c_infinite_loop_detected PLS_INTEGER := -20999;
   PRAGMA EXCEPTION_INIT (e_infinite_loop_detected, -20999);

   PROCEDURE kill_after (max_iterations_in IN PLS_INTEGER);

   PROCEDURE increment_or_kill (by_in IN PLS_INTEGER DEFAULT 1);

   FUNCTION current_count RETURN PLS_INTEGER;
END sf_loop_killer;

Простой пример использования: мы указываем, что цикл должен прерываться после 100 итераций, а затем вызываем increment_or_kill в конце тела цикла. При выполнении этого кода (в бесконечном цикле) генерируется необработанное исключение (рис. 1).

 

 Использование пакета Loop Killer

Рис. 1. Использование пакета Loop Killer

 

 

Предупреждения, относящиеся к производительности

Система предупреждений компилятора появилась в Oracle10g. При включении предупреждений в сеансе Oracle выдает информацию о качестве кода и советы по улучшению его удобочитаемости и производительности. Прислушивайтесь к предупреждениям компилятора — они помогут вам выявить области кода, пригодные для оптимизации.

Полный набор предупреждений, относящихся к производительности, включается следующей командой:

ALTER SESSION SET PLSQL_WARNINGS = 'ENABLE:PERFORMANCE'

Дополнительная информация о предупреждениях и их использовании есть вот в этом блоге. Полный список предупреждений приведен в справочнике Error Messages документации Oracle.

 

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

Встроенные методы коллекций PL...
Встроенные методы коллекций PL... 6941 просмотров sepia Tue, 29 Oct 2019, 09:54:01
Управление приложениями PL/SQL...
Управление приложениями PL/SQL... 3083 просмотров Stas Belkov Thu, 16 Jul 2020, 06:20:48
Работа с числами в PL/SQL на п...
Работа с числами в PL/SQL на п... 16260 просмотров Antoniy Mon, 28 May 2018, 16:45:11
Программирование динамического...
Программирование динамического... 3036 просмотров Максим Николенко Sun, 09 Sep 2018, 06:56:23
Войдите чтобы комментировать