В этом блоге будут представлены программные инструменты и методы, применяемые при оптимизации кода 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 записывает низкоуровневый вывод профайлера в заданный файл.
- Анализатор — обрабатывает низкоуровневый вывод профайлера и сохраняет результаты в иерархических таблицах, к которым затем можно обращаться с запросами для вывода профильной информации.
Работа с иерархическим профайлером проходит примерно так:
- Убедитесь в том, что вы можете выполнить пакет
dbms_hprof
. - Убедитесь в том, что у вас имеются привилегии write для каталога, заданного при вызове
DBMS_HPROF.START_PROFILING
. - Создайте три таблицы профайлера (подробности см. ниже).
- Вызовите процедуру
dbms_hprof.start_profiling
, чтобы запустить сбор данных иерархического профайлера в вашем сеансе. - Выполняйте код своего приложения достаточно долго и многократно, чтобы собранных данных было достаточно для получения интересных результатов.
- Вызовите процедуру
dbms_hprof.stop_profiling
для завершения сбора профильных данных. - Проанализируйте собранные данные и обратитесь с запросами к таблицам профайлера для получения результатов.
Чтобы получить самые точные данные о времени, потраченном при выполнении подпрограмм, сведите к минимуму всю постороннюю деятельность в системе, в которой выполняется приложение.
Конечно, в рабочей системе другие процессы могут замедлить работу вашей программы. Также желательно проводить сбор данных при использовании механизма 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;
/
Вот и все! Данные собраны, проанализированы и распределены по таблицам, и теперь я могу выбрать один из двух способов получения профильной информации:
- Запустите программу командной строки plshprof (из каталога
$ORACLE_HOME/bin
/). Программа генерирует очень простые отчеты HTML по данным одного или двух файлов, содержащих низкоуровневый вывод профайлера. Пример низкоуровневого вывода профайлера приведен в разделе «Collecting Profile Data
» руководстваOracle Database Development Guide
. Далее сгенерированные отчеты в формате HTML просматриваются в любом браузере. - Выполните специально написанный запрос. Допустим, предыдущий блок возвращает номер запуска 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 и выше. С их помощью можно легко вычислить время, затраченное на выполнение блока кода (как общее, так и процессорное) с точностью до секунды. Общая схема выглядит так:
- Вызовите
dbms_utility.get_time
(илиget_cpu_time
) перед выполнением кода. - Выполните код, для которого вы хотите получить хронометражные данные.
- Вызовите
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).
Рис. 1. Использование пакета Loop Killer
Предупреждения, относящиеся к производительности
Система предупреждений компилятора появилась в Oracle10g. При включении предупреждений в сеансе Oracle выдает информацию о качестве кода и советы по улучшению его удобочитаемости и производительности. Прислушивайтесь к предупреждениям компилятора — они помогут вам выявить области кода, пригодные для оптимизации.
Полный набор предупреждений, относящихся к производительности, включается следующей командой:
ALTER SESSION SET PLSQL_WARNINGS = 'ENABLE:PERFORMANCE'
Дополнительная информация о предупреждениях и их использовании есть вот в этом блоге. Полный список предупреждений приведен в справочнике Error Messages
документации Oracle.