Выпуск 13

Какой тип данных использовать

Уважаемые подписчики! Этот выпуск посвящен сравнению типов PLS_INTEGER и NUMBER с точки зрения производительности получаемого кода. Том Кайт вернулся к этому вопросу еще раз вчера, 11 сентября 2002 года. Вашему вниманию предлагается сокращенный перевод соответствующего обсуждения. Это обсуждение демонстрирует также простой и рациональный подход Тома Кайта к проблеме сравнения производительности тех или иных решений в PL/SQL.

Вопрос: Сравнение PLS_INTEGER, NUMBER и "динамических типов"

Мне порекомендовали использовать в PL/SQL объявления

PROCEDURE foo (p_id IN PLS_INTEGER) ...

вместо

PROCEDURE foo (p_id IN NUMBER) ...

или

PROCEDURE foo (p_id IN mytable.my_id%TYPE) ...

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

Ответ Тома Кайта:

По-моему, вы правы.

Тип PLS_INTEGER позволяет повысить производительность лишь очень немногих локальных фрагментов кода (он почти всегда, в конечном итоге, преобразуется в тип NUMBER).

Имеющиеся теоретические преимущества может перевесить простота программирования, и использование %type в этом отношении - замечательно.

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

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

http://asktom.oracle.com/~tkyte/runstats.html

можно убедиться, что простая передача параметров такого типа не дает ни преимуществ, ни потерь:

ops$tkyte@ORA817DEV.US.ORACLE.COM> create or replace procedure p1(x in pls_integer)
  2  as
  3  begin
  4      null;
  5  end;
  6  /

Procedure created.

ops$tkyte@ORA817DEV.US.ORACLE.COM>
ops$tkyte@ORA817DEV.US.ORACLE.COM> create or replace procedure p2(x in number)
  2  as
  3  begin
  4      null;
  5  end;
  6  /

Procedure created.

ops$tkyte@ORA817DEV.US.ORACLE.COM> declare
  2      l_start number;
  3      l_run1  number;
  4      l_run2  number;
  5
  6      l_p1    pls_integer := 0;
  7      l_p2    number := 0;
  8  begin
  9      insert into run_stats select 'before', stats.* from stats;
 10
 11      l_start := dbms_utility.get_time;
 12      for i in 1 .. 1000000
 13      loop
 14          p1( l_p1 );
 15      end loop;
 16      l_run1 := (dbms_utility.get_time-l_start);
 17      dbms_output.put_line( l_run1 || ' hsecs' );
 18
 19      insert into run_stats select 'after 1', stats.* from stats;
 20      l_start := dbms_utility.get_time;
 21      for i in 1 .. 1000000
 22      loop
 23          p2( l_p2 );
 24      end loop;
 25      l_run2 := (dbms_utility.get_time-l_start);
 26      dbms_output.put_line( l_run2 || ' hsecs' );
 27      dbms_output.put_line
 28      ( 'run 1 ran in ' || round(l_run1/l_run2*100,2) || '% of the time' );
 29
 30      insert into run_stats select 'after 2', stats.* from stats;
 31  end;
 32  /
724 hsecs
723 hsecs
run 1 ran in 100.14% of the time

PL/SQL procedure successfully completed.

При 100000 вызовов... и для множества общих операций

ops$tkyte@ORA817DEV.US.ORACLE.COM> create or replace procedure p1(x in out pls_integer)
  2  as
  3  begin
  4      select count(*) into x from dual;
  5  end;
  6  /

Procedure created.

ops$tkyte@ORA817DEV.US.ORACLE.COM>
ops$tkyte@ORA817DEV.US.ORACLE.COM> create or replace procedure p2(x in out number)
  2  as
  3  begin
  4      select count(*) into x from dual;
  5  end;
  6  /

Procedure created.

ops$tkyte@ORA817DEV.US.ORACLE.COM> declare
  2      l_start number;
  3      l_run1  number;
  4      l_run2  number;
  5
  6      l_p1    pls_integer := 0;
  7      l_p2    number := 0;
  8  begin
  9      insert into run_stats select 'before', stats.* from stats;
 10
 11      l_start := dbms_utility.get_time;
 12      for i in 1 .. 100000
 13      loop
 14          p1(l_p1);
 15      end loop;
 16      l_run1 := (dbms_utility.get_time-l_start);
 17      dbms_output.put_line( l_run1 || ' hsecs' );
 18
 19      insert into run_stats select 'after 1', stats.* from stats;
 20      l_start := dbms_utility.get_time;
 21      for i in 1 .. 100000
 22      loop
 23          p2(l_p2);
 24      end loop;
 25      l_run2 := (dbms_utility.get_time-l_start);
 26      dbms_output.put_line(l_run2 || ' hsecs');
 27      dbms_output.put_line
 28      ('run 1 ran in ' || round(l_run1/l_run2*100,2) || '% of the time');
 29
 30      insert into run_stats select 'after 2', stats.* from stats;
 31  end;
 32  /
2288 hsecs
2266 hsecs
run 1 ran in 100.97% of the time

PL/SQL procedure successfully completed.

Принципиальной разницы нет. Ну, если честно, - в некоторых случаях это может быть эффективнее:

ops$tkyte@ORA817DEV.US.ORACLE.COM> create or replace procedure p1(x in out pls_integer)
  2  as
  3  begin
  4      x := x+1;
  5  end;
  6  /

Procedure created.

ops$tkyte@ORA817DEV.US.ORACLE.COM>
ops$tkyte@ORA817DEV.US.ORACLE.COM> create or replace procedure p2(x in out number)
  2  as
  3  begin
  4      x := x+1;
  5  end;
  6  /

Procedure created.

ops$tkyte@ORA817DEV.US.ORACLE.COM> declare
  2      l_start number;
  3      l_run1  number;
  4      l_run2  number;
  5
  6      l_p1    pls_integer := 0;
  7      l_p2    number := 0;
  8  begin
  9      insert into run_stats select 'before', stats.* from stats;
 10
 11      l_start := dbms_utility.get_time;
 12      for i in 1 .. 100000
 13      loop
 14          p1(l_p1);
 15      end loop;
 16      l_run1 := (dbms_utility.get_time-l_start);
 17      dbms_output.put_line(l_run1 || ' hsecs');
 18
 19      insert into run_stats select 'after 1', stats.* from stats;
 20      l_start := dbms_utility.get_time;
 21      for i in 1 .. 100000
 22      loop
 23          p2(l_p2);
 24      end loop;
 25      l_run2 := (dbms_utility.get_time-l_start);
 26      dbms_output.put_line(l_run2 || ' hsecs');
 27      dbms_output.put_line
 28      ('run 1 ran in ' || round(l_run1/l_run2*100,2) || '% of the time');
 29
 30      insert into run_stats select 'after 2', stats.* from stats;
 31  end;
 32  /
93 hsecs
171 hsecs
run 1 ran in 54.39% of the time

PL/SQL procedure successfully completed.

но в большинстве случаев - ненамного, и если вдруг выяснится, что это - узкое место (например, с помощью пакета dbms_profiler), можете "настраивать" до победного конца.

Комментарий читателя из Техаса, 10 сентября 2002 года

Я приветствую ваш постоянный интерес к разъяснению, как делать "правильно" (кодировать, использовать связываемые переменные, сегменты отката и т.д..). Почему столь многие оперируют соображениями "производительности", за которыми ничего реально не стоит?

Ответ Тома Кайта

Меня это тоже удивляет. Срабатывает старый подход: "Эй, я знаю вот такую тонкость. Делать надо так, и заработает быстрее".

Если бы вся проблема была в скорости работы, мы бы программировали на ассемблере. Хитрый и запутанный код на ассемблере будет непревзойденным с точки зрения производительности. Для его создания надо только время и дейсвительно хороший знаток ассемблера.

Доля правды есть во всем - в утверждении о большей эффективности типа PLS_INTEGER, например (который на самом деле не заменяет тип NUMBER - я забыл упомянуть, что поведение программы изменится!!!!!!!!

ops$tkyte@ORA920.US.ORACLE.COM> declare
  2          x pls_integer;
  3          y number;
  4  begin
  5          y := 5.232;
  6          x := y;
  7          dbms_output.put_line(y);
  8          dbms_output.put_line(x);
  9  end;
 10  /
5.232
5

PL/SQL procedure successfully completed.)

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

ops$tkyte@ORA817DEV.US.ORACLE.COM> create or replace procedure p1(x in out pls_integer)
  2  as
  3  begin
  4      x := x+1.0;
  5  end;
  6  /

Procedure created.

ops$tkyte@ORA817DEV.US.ORACLE.COM>
ops$tkyte@ORA817DEV.US.ORACLE.COM> create or replace procedure p2(x in out number)
  2  as
  3          l_var pls_integer := x;
  4  begin
  5      x := x+1.0;
  6  end;
  7  /

Procedure created.

ops$tkyte@ORA817DEV.US.ORACLE.COM> declare
  2      l_start number;
  3      l_run1  number;
  4      l_run2  number;
  5
  6      l_p1    pls_integer := 0;
  7      l_p2    number := 0;
  8  begin
  9      insert into run_stats select 'before', stats.* from stats;
 10
 11      l_start := dbms_utility.get_time;
 12      for i in 1 .. 100000
 13      loop
 14          p1( l_p1 );
 15      end loop;
 16      l_run1 := (dbms_utility.get_time-l_start);
 17      dbms_output.put_line(l_run1 || ' hsecs');
 18
 19      insert into run_stats select 'after 1', stats.* from stats;
 20      l_start := dbms_utility.get_time;
 21      for i in 1 .. 100000
 22      loop
 23          p2( l_p2 );
 24      end loop;
 25      l_run2 := (dbms_utility.get_time-l_start);
 26      dbms_output.put_line(l_run2 || ' hsecs');
 27      dbms_output.put_line
 28      ('run 1 ran in ' || round(l_run1/l_run2*100,2) || '% of the time');
 29
 30      insert into run_stats select 'after 2', stats.* from stats;
 31  end;
 32  /
188 hsecs
158 hsecs
run 1 ran in 118.99% of the time

PL/SQL procedure successfully completed.

Все зависит от того, что она делает!

Все знают - процедуры требуют дополнительных накладных расходов, связанных с их вызовом. Так что, нужно подставить код всех процедур в места вызова и получить одну огромную процедуру из 1000 строк? Я такое решение даже рассматривать не буду, не то что принимать или тестировать в моей системе...

И главное - это INTEGER, в конечном итоге, а не NUMBER!

Оригинал обсуждения этого вопроса можно найти здесь.

Инструментарий для тестирования

В предыдущем обсуждении Том Кайт ссылается на свой подход к тестированию. Если вы еще не прошли по соответствующей ссылке на его сайт, вот вам краткое описание среды, в которой работали рассмотренные выше примеры. Продолжаем творчески цитировать Тома Кайта:

Этот подход к тестированию я использую, чтобы проверит разные идеи. Он позволяет получить два важнейших набора статистических показателей:

Различие между двумя подходами по времени работы. Я просто смотрю, какой подход работает быстрее по времени.
Сколько ресурсов требует каждый подход. Эти показатели могут иметь даже большее значение, чем время работы. Например, если один подход работает быстрее другого, но требует установки тысяч защелок (блокировок), я могу отказаться от него просто потому, что он будет плохо масштабироваться.

Суть подхода в том, что статистическая информация о системе и защелках сохраняется во временной таблице. Длеатеся снимок состояния системы до начала тестирования. Затем, мы проверяем первое решение и делаем другой "моментальный снимок". После этого мы проверяем второе решение, и делаем еще один моментальный снимок. Теперь можно показать, сколько ресурсов использует каждый из подходов.

Требования

Чтобы успешно тестировать решения вам, как минимум, необходимы:

Доступ к представлениям V$STATNAME, V$MYSTAT и V$LATCH.
Если хочется использовать представление, которое я продемонстрирую далее, необходимо получить привилегию на выполнение SELECT непосредственно из представлений SYS.V_$STATNAME, SYS.V_$MYSTAT и SYS.V_$LATCH. Надо полуить эту привилегию непосредственно, а не через роль. Можно обойтись и без этого, но не удастся использовать представление STATS, задействованное в моих примерах ниже (подставьте соответствующий текст запроса в блоке PLSQL, где я ссылаюсь на представление STATS).
Возможность создать таблицу, run_stats, для хранения предварительной, промежуточной и окончательной информации.

Учтите также, что информация о защелках собирается для всей системы. Если тестирование происходит в многопользовательской системе, информация о защелках будет технически некорректной, поскольку будут утчены защелки и других сеансов, а не только вашего. Этот подход к тестированию более приемлем в простой, управляемой среде.

Итак, надо создать очень простую таблицу:

create table run_stats
( runid varchar2(15),
  name  varchar2(80),
  value int
);

и, если можно получить непосредственные привилегии на доступ к представлениям V$ (или если АБД создаст такое представление и предоставит вам привилегию SELECT на него), следующее представление:

create or replace view stats
as select 'STAT...' || a.name name, b.value
     from v$statname a, v$mystat b
     where a.statistic# = b.statistic#
   union all
   select 'LATCH.' || name,  gets
     from v$latch;

Теперь сам шаблон теста, - он очень простой:

declare
    l_start number;
    -- добавляйте любые необходимые для теста переменные...
begin
    delete from run_stats;
    commit;
    -- начнем с получения состояния представлений v$
    insert into run_stats select 'before', stats.* from stats;
    l_start := dbms_utility.get_time;  -- начинаем измерять время...
    -- очень быстро выполняемые операции я повторяю в цикле
    -- много раз, чтобы измерять "продолжительные" интервалы
    -- Если тестируемая операция выполянется долго, делайте меньше итераций
    -- или вообще уберите цикл
    for i in 1 .. 1000
    loop
        -- код для подхода #1
    end loop;
    dbms_output.put_line((dbms_utility.get_time-l_start) || ' hsecs');
  
    -- делаем еще один моментальный снимок и снова начинаем измерять время...
    insert into run_stats select 'after 1', stats.* from stats;     
    l_start := dbms_utility.get_time;
    for i in 1 .. 1000
    loop
        -- код для подхода #2
    end loop;
    dbms_output.put_line((dbms_utility.get_time-l_start) || ' hsecs');
    insert into run_stats select 'after 2', stats.* from stats;
end;
/

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

select a.name, b.value-a.value run1, c.value-b.value run2,
       ((c.value-b.value)-(b.value-a.value)) diff
  from run_stats a, run_stats b, run_stats c
 where a.name = b.name
   and b.name = c.name
   and a.runid = 'before'
   and b.runid = 'after 1'
   and c.runid = 'after 2'
   and (c.value-a.value) > 0
   and (c.value-b.value) <> (b.value-a.value)
 order by abs( (c.value-b.value)-(b.value-a.value))
/

В следующем выпуске

Этот выпуск оказался "внеочередным", так что, теперь уже в следующем: кое что про использование REF CURSOR, а может - "Мифы об экстентах"... Выпуск выйдет в начале сле дующей недели. Следите за новостями на сайте проекта Open Oracle.

С наилучшими пожеланиями,

В.К.