Проблематика и назначение:

Периодически Oracle разработчики сталкиваются с проблемой производительности PL/SQL кода. Возникают проблемы с тем, чтобы найти место pl/sql кода, в котором возникают проблемы.

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

Методы решения проблемы:

В решение проблем с производительность в БД Oracle нам помогут:

• PL/SQL Developer — Популярное IDE для Oracle разработчиков.
• DBMS_PROFILE — Oracle пакет для профилирования (не будет рассматриваться в рамках данной статьи, т.к. информации достаточно на просторах интернета).

Метод(процедура) для профилирования:

В рамках данной статьи покажу всего лишь подход к профилированию, поэтому метод, который мы будем профилировать будет не большим.

Создадим метод для профилирования:

create or replace procedure check_cycle_performance(p_cycle_size in number) is
    c_method_error         constant number := -20000;
    c_method_error_message constant varchar2(4000) := 'Cycle size should be > 0';
    l_power_value number;
    l_dual_value  number;
begin
    if p_cycle_size > 0 then
        --Cycle with power calculation
        for i in 1 .. p_cycle_size
        loop
            l_power_value := power(i, 2);
        end loop;
        --Cycle with switching context(sql-pl/sql)
        for i in 1 .. p_cycle_size
        loop
            select i into l_dual_value from dual;
        end loop;
    else
        raise_application_error(c_method_error, c_method_error_message);
    end if;
end check_cycle_performance;

Профилирование через IDE PL/SQL Developer:

В pl/sql Developer запускаем Test Window:


Рисунок 1 — Запуск Test Window

В тестовом окне наберём код для запуска метода check_cycle_performance, заполним входную переменную l_cycle_size и нажмем кнопку для запуска профилирования:


Рисунок 2 — Запуск профилирования в PL/SQL Developer

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

Далее по F8 запускаем метод, после его выполнения переходим во вкладку Profiler:


Рисунок 3 — Окно профайлера в PL/SQL.

Из рисунка 3 видно, что основная проблема по производительности состоит в строке 16.

Не трудно понять, что основная проблема производительности кроется в переключениях контекста SQL — PL/SQL.

При этом возведение в степень: строка 11, занимает гораздо меньше времени.

По мерам предотвращения переключения контекстов рассмотрим в рамках другой статьи.