ReSQL.ru
     
powered by simpleCommunicator - 2.0.61     © 2026 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / Oracle [игнор отключен] [закрыт для гостей] / pipelined vs non-pipelined
10 сообщений из 10, страница 1 из 1
pipelined vs non-pipelined
    #39566983
Фотография dbms_photoshop
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
dbms_photoshop
Участник
Было у меня мнение с древних времен, что pipelined быстрее возвращает первые строки, а non-pipelined быстрее отрабатывает в целом (с весьма незначительной разницей).
Сие умозаключение нерелевантно для 11g, где pipelined может отрабатывать в два раза быстрее non-pipelined (выделено желтым).
Но вот что больше удивило - просаживание производительности на 12c, где все выполняется заметно дольше,
а время выполнение non-pipelined более чем в два раза медленее 11g (выделено красным).
 
src
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
create or replace type to_id_value as object (id int, value int)
/
create or replace type tt_id_value as table of to_id_value
/

create or replace function f_pipe return tt_id_value
  pipelined is
begin

  for i in (select rownum id, 1 value from dual connect by rownum <= 1e6) loop
    pipe row(to_id_value(i.id, i.value));
  end loop;

end f_pipe;
/

create or replace function f_non_pipe return tt_id_value is
  result tt_id_value;
begin

  select to_id_value(rownum, 1) bulk collect
    into result
    from dual
  connect by rownum <= 1e6;
  return result;

end f_non_pipe;
/


 
test
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
set lines 150 pages 150
alter session set statistics_level = all;
alter session set plsql_optimize_level = 0;
alter function f_pipe compile;
select /*+ monitor */ sum(id * value) s from table(f_pipe);
select * from table(dbms_xplan.display_cursor(format => 'ALLSTATS LAST'));
alter session set plsql_optimize_level = 2;
alter function f_pipe compile;
select /*+ monitor */ sum(id * value) s from table(f_pipe);
select * from table(dbms_xplan.display_cursor(format => 'ALLSTATS LAST'));
alter session set plsql_optimize_level = 0;
alter function f_non_pipe compile;
select /*+ monitor */ sum(id * value) s from table(f_non_pipe);
select * from table(dbms_xplan.display_cursor(format => 'ALLSTATS LAST'));
alter session set plsql_optimize_level = 2;
alter function f_non_pipe compile;
select /*+ monitor */ sum(id * value) s from table(f_non_pipe);
select * from table(dbms_xplan.display_cursor(format => 'ALLSTATS LAST'));


11.2
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
SQL> alter session set plsql_optimize_level = 0;
-------------------------------------------------------------------------------------------------------
| Id  | Operation                          | Name   | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                   |        |      1 |        |      1 |00:00:07.90 |      25 |
|   1 |  SORT AGGREGATE                    |        |      1 |      1 |      1 |00:00:07.90 |      25 |
|   2 |   COLLECTION ITERATOR PICKLER FETCH| F_PIPE |      1 |   8168 |   1000K|00:00:07.39 |      25 |
-------------------------------------------------------------------------------------------------------
SQL> alter session set plsql_optimize_level = 2;
-------------------------------------------------------------------------------------------------------
| Id  | Operation                          | Name   | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                   |        |      1 |        |      1 |00:00:02.55 |      25 |
|   1 |  SORT AGGREGATE                    |        |      1 |      1 |      1 |00:00:02.55 |      25 |
|   2 |   COLLECTION ITERATOR PICKLER FETCH| F_PIPE |      1 |   8168 |   1000K|00:00:02.21 |      25 |
-------------------------------------------------------------------------------------------------------
SQL> alter session set plsql_optimize_level = 0;
-----------------------------------------------------------------------------------------------------------
| Id  | Operation                          | Name       | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                   |            |      1 |        |      1 |00:00:06.43 |      24 |
|   1 |  SORT AGGREGATE                    |            |      1 |      1 |      1 |00:00:06.43 |      24 |
|   2 |   COLLECTION ITERATOR PICKLER FETCH| F_NON_PIPE |      1 |   8168 |   1000K|00:00:05.81 |      24 |
-----------------------------------------------------------------------------------------------------------
SQL> alter session set plsql_optimize_level = 2;
-----------------------------------------------------------------------------------------------------------
| Id  | Operation                          | Name       | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                   |            |      1 |        |      1 |00:00:06.45 |      24 |
|   1 |  SORT AGGREGATE                    |            |      1 |      1 |      1 |00:00:06.45 |      24 |
|   2 |   COLLECTION ITERATOR PICKLER FETCH| F_NON_PIPE |      1 |   8168 |   1000K|00:00:05.83 |      24 |
-----------------------------------------------------------------------------------------------------------


12.2
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
SQL> alter session set plsql_optimize_level = 0;
-------------------------------------------------------------------------------------------------------
| Id  | Operation                          | Name   | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                   |        |      1 |        |      1 |00:00:10.08 |      25 |
|   1 |  SORT AGGREGATE                    |        |      1 |      1 |      1 |00:00:10.08 |      25 |
|   2 |   COLLECTION ITERATOR PICKLER FETCH| F_PIPE |      1 |   8168 |   1000K|00:00:09.53 |      25 |
-------------------------------------------------------------------------------------------------------
SQL> alter session set plsql_optimize_level = 2;
-------------------------------------------------------------------------------------------------------
| Id  | Operation                          | Name   | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                   |        |      1 |        |      1 |00:00:04.14 |      25 |
|   1 |  SORT AGGREGATE                    |        |      1 |      1 |      1 |00:00:04.14 |      25 |
|   2 |   COLLECTION ITERATOR PICKLER FETCH| F_PIPE |      1 |   8168 |   1000K|00:00:03.89 |      25 |
-------------------------------------------------------------------------------------------------------
SQL> alter session set plsql_optimize_level = 0;
-----------------------------------------------------------------------------------------------------------
| Id  | Operation                          | Name       | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                   |            |      1 |        |      1 |00:00:15.92 |      23 |
|   1 |  SORT AGGREGATE                    |            |      1 |      1 |      1 |00:00:15.92 |      23 |
|   2 |   COLLECTION ITERATOR PICKLER FETCH| F_NON_PIPE |      1 |   8168 |   1000K|00:00:15.31 |      23 |
-----------------------------------------------------------------------------------------------------------
SQL> alter session set plsql_optimize_level = 2;
-----------------------------------------------------------------------------------------------------------
| Id  | Operation                          | Name       | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                   |            |      1 |        |      1 |00:00:15.83 |      24 |
|   1 |  SORT AGGREGATE                    |            |      1 |      1 |      1 |00:00:15.83 |      24 |
|   2 |   COLLECTION ITERATOR PICKLER FETCH| F_NON_PIPE |      1 |   8168 |   1000K|00:00:15.21 |      24 |
-----------------------------------------------------------------------------------------------------------



Смотрим может чего интересного есть в SQL monitor (для запросов с plsql_optimize_level = 2)
11.2
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
===========================================================
| Elapsed |   Cpu   | PL/SQL  |  Other   | Fetch | Buffer |
| Time(s) | Time(s) | Time(s) | Waits(s) | Calls |  Gets  |
===========================================================
|    2.55 |    2.54 |    1.08 |     0.01 |     1 |     25 |
===========================================================
===========================================================
| Elapsed |   Cpu   | PL/SQL  |  Other   | Fetch | Buffer |
| Time(s) | Time(s) | Time(s) | Waits(s) | Calls |  Gets  |
===========================================================
|    6.47 |    6.44 |    0.26 |     0.02 |     1 |     24 |
===========================================================


12.2
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
===========================================================
| Elapsed |   Cpu   | PL/SQL  |  Other   | Fetch | Buffer |
| Time(s) | Time(s) | Time(s) | Waits(s) | Calls |  Gets  |
===========================================================
|    4.14 |    2.28 |    1.01 |     1.87 |     1 |     25 |
===========================================================
===========================================================
| Elapsed |   Cpu   | PL/SQL  |  Other   | Fetch | Buffer |
| Time(s) | Time(s) | Time(s) | Waits(s) | Calls |  Gets  |
===========================================================
|      16 |    7.66 |    1.72 |     8.21 |     1 |     24 |
===========================================================



Обращает на себя внимание, что для 12с много времени уходит в Other Waits, но больше ничего интересного нет.
Смотрим в ash, на 12с появляется event "acknowledge over PGA limit", оказывается это связано с PGA_AGGREGATE_LIMIT .

Фикс 12c: 'acknowledge over PGA limit' Wait Event (Doc ID 2138882.1)
Код: plsql
1.
ALTER SYSTEM SET PGA_AGGREGATE_LIMIT=0 SID='*' SCOPE=BOTH;


После этого отличается от 11g в рамках погрешности.
...
Рейтинг: 0 / 0
09.12.2017, 12:16
| Ответить | Цитировать | Написать  
pipelined vs non-pipelined
    #39567037
Фотография Sayan Malakshinov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Sayan Malakshinov
Модератор форума
dbms_photoshop,

имхо такой тест несколько не сравним:
1. в pipe идет создание объекта в pl/sql, а в non_pipe в sql
2. в non_pipe на выделение памяти надо время
времени пока нет - не тестил, но на 99% уверен что вся разница по времени в эти два пункта и уходит. Попробуй c dtrace/systemtap посмотреть флеймграф.
Для валидного теста имхо надо в обоих функциях коллекцию сначала предсоздать и потом уже отдавать, чтобы разница была чисто в pipe row и return result.

Кроме того, я не знаю как работает "COLLECTION ITERATOR PICKLER FETCH", может он оптимизирован для pipe функций и достает пачками? если сам не глянешь - могу вечером потестить.

dbms_photoshop
Код: plsql
1.
PGA_AGGREGATE_LIMIT=0


эту штуку я на 12с сейчас сразу вырубаю, после того как нарвался на то что оракл кильнул бэкграунд процессы по этому лимиту и весь инстанс грохнулся, хотя задумано это было для пользовательских сессий
...
Рейтинг: 0 / 0
09.12.2017, 14:52
| Ответить | Цитировать | Написать  
pipelined vs non-pipelined
    #39567069
Фотография dbms_photoshop
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
dbms_photoshop
Участник
xtenderДля валидного теста имхо надо в обоих функциях коллекцию сначала предсоздать и потом уже отдавать, чтобы разница была чисто в pipe row и return result.Поскольку вариант с bulk collect медленнее, перепишем его на for loop.
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
create or replace function f_non_pipe0 return tt_id_value is
  result tt_id_value;
  idx    int := 0;
begin

  result := tt_id_value();
  result.extend(1e6);

  for i in (select rownum id, 1 value from dual connect by rownum <= 1e6) loop
    idx := idx + 1;
    result(idx) := to_id_value(i.id, i.value);
  end loop;

  return result;

end f_non_pipe0;

Смотрим dbms_hprof
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
SQL> select pci.runid,
  2         level depth,
  3         rpad(' ', (level - 1) * 3, ' ') || fi.function as name,
  4         fi.namespace,
  5         fi.subtree_elapsed_time,
  6         fi.function_elapsed_time,
  7         fi.calls
  8    from (select runid, parentsymid, childsymid
  9            from dbmshp_parent_child_info
 10          union all
 11          select runid, null, 2 from dbmshp_runs) pci
 12    join dbmshp_function_info fi
 13      on pci.runid = fi.runid
 14     and pci.childsymid = fi.symbolid
 15     and fi.function <> 'STOP_PROFILING'
 16  connect by prior childsymid = parentsymid
 17         and prior pci.runid = pci.runid
 18   start with pci.parentsymid is null
 19          and pci.runid in (:runid);

     RUNID      DEPTH NAME                           NAMESPACE  SUBTREE_ELAPSED_TIME FUNCTION_ELAPSED_TIME      CALLS
---------- ---------- ------------------------------ ---------- -------------------- --------------------- ----------
        33          1 __plsql_vm                     PLSQL                   8252654                    32      4
        33          2    __anonymous_block           PLSQL                        43                    43      1
        33          2    F_NON_PIPE                  PLSQL                   3792685                257538      1
        33          3       __static_sql_exec_line5  SQL                     3535147               3535147      1
        33          2    F_NON_PIPE0                 PLSQL                   2447433               1317506      1
        33          3       __sql_fetch_line9        SQL                     1129927               1129927  10001
        33          2    F_PIPE                      PLSQL                   2012461                886618      1
        33          3       __sql_fetch_line5        SQL                     1125843               1125843  10001

8 rows selected.

Казалось бы, теперь нагрузка равномерно размазана между SQL & PL/SQL для вариантов F_NON_PIPE0 и F_PIPE.
В обоих случаях выполняется 10000 фетчей по 100 строк и даже время выполнения примерно одинаковое.

Но по времени реакции на клиенте все несколько не так.
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
SQL> exec dbms_hprof.start_profiling('UDUMP', '1.trc');

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.01
SQL> select /*+ monitor */ sum(id * value) s from table(f_pipe);

         S
----------
5,0000E+11

Elapsed: 00:00:02.08
SQL> select /*+ monitor */ sum(id * value) s from table(f_non_pipe);

         S
----------
5,0000E+11

Elapsed: 00:00:05.72
SQL> select /*+ monitor */ sum(id * value) s from table(f_non_pipe0);

         S
----------
5,0000E+11

Elapsed: 00:00:04.95
SQL> exec dbms_hprof.stop_profiling;

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.05
SQL> select dbms_hprof.analyze('UDUMP', '1.trc') runid from dual;
...
Рейтинг: 0 / 0
09.12.2017, 16:55
| Ответить | Цитировать | Написать  
pipelined vs non-pipelined
    #39567205
Фотография Elic
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Elic
Участник
dbms_photoshopСие умозаключение нерелевантно для 11g, где pipelined может отрабатывать в два раза быстрее non-pipelined (выделено желтым).Ребяты, с точки зрения разработчика вы занимаетесь хернёй: если можно не использовать pipelined, то и не нужно натягивать удава на ежа.
...
Рейтинг: 0 / 0
10.12.2017, 09:53
| Ответить | Цитировать | Написать  
pipelined vs non-pipelined
    #39567401
rpovarov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
rpovarov
Гость
Elic,

pipelined очень удобны, позволяют запихнуть в PL/SQL сложную логику работы с большими объемами данных. В чистом SQL это бы означало кучу вспомогательных временных таблиц и сопутствующие ритуальные танцы вокруг них. В чистом PL/SQL - постоянная забота, чтобы в коллекциях не сожрать слишком много памяти. А в pipelined - сгенерил, сразу отдал, взял следующую порцию. Ни у кого голова не болит. Возможность без проблем всё это параллелить - вообще дар богов. Я у себя при обработке ещё попутно longops наполняю, очень удобно следить за прогрессом.
...
Рейтинг: 0 / 0
10.12.2017, 21:13
| Ответить | Цитировать | Написать  
pipelined vs non-pipelined
    #39567409
Фотография dbms_photoshop
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
dbms_photoshop
Участник
ElicРебяты, с точки зрения разработчика вы занимаетесь хернёй: если можно не использовать pipelined, то и не нужно натягивать удава на ежа.В названии темы указано какие инструменты сраваниваются.
rpovarovВозможность без проблем всё это параллелить - вообще дар богов.Это ты верно отнес в плюсы pipelined, но в теме речь идет про pipelined table function vs regular table function.
А не сравнение с SQL подходами или другими способами вернуть набор данных.
...
Рейтинг: 0 / 0
10.12.2017, 21:55
| Ответить | Цитировать | Написать  
pipelined vs non-pipelined
    #39567459
Фотография Sayan Malakshinov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Sayan Malakshinov
Модератор форума
xtenderя не знаю как работает "COLLECTION ITERATOR PICKLER FETCH", может он оптимизирован для pipe функций и достает пачкамиВ общем наоборот - это не pipelined оптимизированы, а обычные - так плохо реализованы :)
Проблемы тут две:
1. мучительно долгая обработка результата не-pipelined функции (см. kgmpoa_process_out_args в oncpu_nonpipe.svg)
2. копирование коллекции (см. pmusasc_Assign_Collection в oncpu_nonpipe.svg)

В результате, будет быстрее даже просто создать функцию, которая будет пайпить результаты не-pipelined функции (см. f_pipe_for_nonpipe)
 
ddl
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
set echo on feed on;
create or replace type to_id_value as object (id int, value int)
/
create or replace type tt_id_value as table of to_id_value
/
create or replace function f_pipe(n int) return tt_id_value pipelined 
as
  result tt_id_value;
  
  procedure gen is
  begin
     result:=tt_id_value();
     result.extend(n);
     for i in 1..n loop
        result(i):=to_id_value(i, 1);
     end loop;
  end;    
begin
  gen();
  dbms_lock.sleep(1);
  for i in 1..n loop
    pipe row (result(i));
  end loop;
end f_pipe;
/
create or replace function f_non_pipe(n int) return tt_id_value 
as
  result tt_id_value;
  
  procedure gen is
  begin
     result:=tt_id_value();
     result.extend(n);
     for i in 1..n loop
        result(i):=to_id_value(i, 1);
     end loop;
  end;    
begin
  gen();
  dbms_lock.sleep(1);
  return result;
end f_non_pipe;
/
create or replace function f_pipe_for_nonpipe(n int) return tt_id_value pipelined 
as
  result tt_id_value;
begin
  result:=f_non_pipe(n);
  for i in 1..result.count loop
    pipe row (result(i));
  end loop;
end;
/
set echo off feed off;


 
test
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
alter session set optimizer_adaptive_plans=false;
alter session set "_optimizer_use_feedback"=false;

pause;

select sum(id * value) s from table(f_pipe(1e6));

pause;

select sum(id * value) s from table(f_non_pipe(1e6));

pause;

select sum(id * value) s from table(f_pipe_for_nonpipe(1e6));

...
Рейтинг: 0 / 0
11.12.2017, 05:31
| Ответить | Цитировать | Написать  
pipelined vs non-pipelined
    #39567694
Фотография dbms_photoshop
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
dbms_photoshop
Участник
xtender,

Стек для pipe выглядит для меня вполне логично: фетч (opifch2) вызывает PL/SQL движок (pfrrun).
А вот для non-pipe есть отдельно фетч и отдельно на том же уровне opiexe (который вызывает PL/SQL движок и process_out_args отдельно).
...
Рейтинг: 0 / 0
11.12.2017, 14:56
| Ответить | Цитировать | Написать  
pipelined vs non-pipelined
    #39567697
Фотография Sayan Malakshinov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Sayan Malakshinov
Модератор форума
dbms_photoshop,

Про то и речь в первом пункте: у случае обычных table function, oracle сначала так долго предпроцессит что функция будет возвращать
...
Рейтинг: 0 / 0
11.12.2017, 14:59
| Ответить | Цитировать | Написать  
pipelined vs non-pipelined
    #39567706
Фотография dbms_photoshop
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
dbms_photoshop
Участник
xtenderсначала так долго предпроцессит, что функция будет возвращатьЭто, кстати, походу и объясняет почему так сильно отличается фактическое время для non-pipe и показанное с помощью dbms_hprof.
Вероятно, dbms_hprof просто не отлавливает kgmpoa_process_out_args.
...
Рейтинг: 0 / 0
11.12.2017, 15:09
| Ответить | Цитировать | Написать  
10 сообщений из 10, страница 1 из 1
Форумы / Oracle [игнор отключен] [закрыт для гостей] / pipelined vs non-pipelined
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение   Полит. конфиден.  
NoSQL.ru       Кролег: Проекты, Новости, Чат       РЕД ФОРУМ       Фотоальбом: Участники, Встречи
Закрыть
start [/forum/topic.php?fid=52&fpage=133&tid=1884757]:
 0ms
get settings:
 8ms
get forum list:
 21ms
check forum access:
 4ms
check topic access:
 4ms
track hit:
 81ms
get topic data:
 10ms
get forum data:
 3ms
get page messages:
 46ms
get tp. blocked users:
 1ms
others: 226ms
total:  404ms
созд. / загр.: 404ms
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]