02.10.2017 05:01
ympukhov 8 2600 Источник

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


Периодически 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, занимает гораздо меньше времени.

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

Комментарии (8)


  1. YurySS
    02.10.2017 10:44
    #10447302

    Спасибо! Много лет пользуюсь PL/SQL Developer, а о такой возможности узнал только сейчас.


  1. lemesh
    02.10.2017 10:54
    #10447316

    Отличная статья, спасибо! Ждем продолжения.


  1. oleg_agapov
    02.10.2017 12:25
    #10447474

    А это профилирование изолированно от реальных данных?


    1. ympukhov Автор
      02.10.2017 12:28
      #10447476

      Конкретно этот пример был показан для выявления проблем на тестовой/разработческой среде. Можно конкретнее в чем заключается вопрос?


      1. oleg_agapov
        02.10.2017 12:58
        #10447528

        Ну то есть, если у меня уже есть написанные процедуры и я хочу найти в них слабое место. Выполнение профилирования отразится на таблицах, которые учавствуют в процедуре? Или это будет сделано без коммита данных?


        1. ympukhov Автор
          02.10.2017 13:14
          #10447550

          Это зависит от того как ты сам управляешь транзакцией: если у тебя где-либо в коде стоит фиксация транзакции, то данные естественно зафиксируются, если нет — то нет. Если нет фиксации транзакций, то можешь в тестовом окне(рисунок 2) поставить rollback.


  1. xtender
    05.10.2017 01:26
    #10454194

    В принципе, PL/SQL Developer использует тот же DBMS_PROFILER (кстати, есть еще иерархический профайлер ).
    Для большей автоматизации иногда удобнее пользоваться своими запросами к этим же таблицам. Вот рыба для них:

    select r.runid
      ,r.related_run
      ,r.run_owner
      ,r.run_date
      ,r.run_comment
      ,r.run_total_time
      ,r.run_system_info
      ,r.run_comment1
      ,u.unit_number
      ,u.unit_type
      ,u.unit_owner
      ,u.unit_name
      ,d.line#
      ,s.TEXT
      ,u.total_time
      ,d.total_occur
      ,d.total_time
      ,d.min_time
      ,d.max_time      
from plsql_profiler_runs r
     join plsql_profiler_data d
          on  r.runid = d.runid
     join plsql_profiler_units u
          on  d.runid = u.runid
          and d.unit_number = u.unit_number
     left join all_source s
          on  u.unit_owner = s.OWNER
          and u.unit_name  = s.NAME 
          and u.unit_type  = s.TYPE 
          and d.line#      = s.LINE
where 1=1
--  and r.run_owner = user -- запуски от текущей юзера
--  and r.runid = (select max(runid) from plsql_profiler_runs where run_owner=user) -- последний запуск
order by r.runid,r.related_run,u.unit_number,d.line#;


  1. air_squirrel
    05.10.2017 11:50
    #10454954

    В нем (PLSQL Dev) удобно еще дебажить.
    Спасибо за статью.