Oracle 流水线函数有什么开销 (如果有的话)？

我有一些情况，我有一个在Apex内置的报告，在应用程序中有一个巨大的查询。理想情况下，我们希望在数据库上保留尽可能多的代码 (用于依赖项检查等)，但是这些留在Apex中的原因是因为查询是参数化的，无论出于何种原因，谓词都无法推入视图。
所以我一直在考虑的一件事是一个包含一组流水线的包，其中我有函数的参数，运行查询，然后管道排出来。

然而，我主要担心的是，是否存在这样做的性能风险-即一次真正处理一行还是数据库内部批量增加？

我最初的测试看起来有一些差异-但我100% 不确定如何解释结果-即它是否只是所用方法的工件或真正的性能下降？

create table xxtst as select mod(level,5) i, level j from dual connect by level <= 10000;

exec dbms_stats.gather_table_stats(ownname=>user,tabname=>'xxtst');

create type xxtst_tab as table of number;
/

create or replace function xxtst_p(pi in number) return xxtst_tab pipelined is
begin
  for i in (select j from xxtst where i=pi) loop
    pipe row(i.j);
  end loop;
end;
/

set autotrace traceonly;

select j from xxtst where i=2;

select * from table(xxtst_p(pi=>2));

drop function xxtst_p;
drop type xxtst_tab;
drop table xxtst;

exit;

$> sp @tst.sql

SQL*Plus: Release 10.1.0.5.0 - Production on Wed Aug 30 12:01:07 2017

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options


Table created.


PL/SQL procedure successfully completed.


Type created.


Function created.


2000 rows selected.


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=25 Card=2000 Bytes
          =14000)

   1    0   TABLE ACCESS (FULL) OF 'XXTST' (TABLE) (Cost=25 Card=2000
          Bytes=14000)





Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
        162  consistent gets
          0  physical reads
          0  redo size
      37017  bytes sent via SQL*Net to client
       2182  bytes received via SQL*Net from client
        144  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
       2000  rows processed


2000 rows selected.


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=104 Card=8168 Byte
          s=16336)

   1    0   COLLECTION ITERATOR (PICKLER FETCH) OF 'XXTST_P' (PROCEDUR
          E) (Cost=104 Card=8168 Bytes=16336)





Statistics
----------------------------------------------------------
         53  recursive calls
          6  db block gets
        117  consistent gets
          0  physical reads
          0  redo size
      37028  bytes sent via SQL*Net to client
       2182  bytes received via SQL*Net from client
        144  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
       2000  rows processed


Function dropped.


Type dropped.


Table dropped.

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

谢谢你的好测试用例。

是的，我不确定我们是否可以完全相信这里的自动跟踪结果，所以我将使用SQL跟踪，以便我们可以深入研究递归调用。

SQL> create table xxtst as select mod(level,5) i, level j 来自 dual connect by level <= 100000;

Table created.

SQL>
SQL> exec dbms_stats.gather_table_stats(ownname=>user,tabname=>'xxtst');

PL/SQL procedure successfully completed.

SQL>
SQL> create or replace type xxtst_tab as table of number;
  2  /

Type created.

SQL>
SQL> create or replace function xxtst_p(pi in number) return xxtst_tab pipelined is
  2  begin
  3    for i in (select j 来自 xxtst where i=pi) loop
  4      pipe row(i.j);
  5    end loop;
  6  end;
  7  /

Function created.

SQL> set arraysize 100
SQL> set feedback only
SQL> exec dbms_monitor.session_trace_enable

PL/SQL procedure successfully completed.

SQL> select j 来自 xxtst where i=2;

20000 rows selected.

SQL> 选择 *来自表 (xxtst_p(pi =>2));

20000 rows selected.

SQL> exec dbms_monitor.session_trace_disable

PL/SQL procedure successfully completed.

SQL> set feedback on
SQL> @tk

VALUE
-------------------------------------------------------------------------------------------------
VALUE1
-------------------------------------------------------------------------------------------------
C:\ORACLE\diag\rdbms\db122\db122\trace\db122_ora_4492.trc
C:\ORACLE\diag\rdbms\db122\db122\trace\db122_ora_4492.prf


1 row selected.


TKPROF: Release 12.2.0.1.0 - Development on Wed Sep 6 08:52:45 2017

Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.



SQL>
SQL>
SQL>

在跟踪文件中，我看到了这个:

原始SQL
=

select j 
来自
 xxtst where i=2


调用计数cpu经过磁盘查询当前行
-
Parse        1      0.00       0.00          0          0          2           0
执行1 0.00 0.00 0
Fetch      201      0.00       0.00          0        388          4       20000
-
total      203      0.00       0.00          0        388          6       20000

流水线SQL
=======
选择J
来自
XXTST其中I =:B1


调用计数cpu经过磁盘查询当前行
-
解析1 0.00 0.00 0
执行1 0.00 0.00 0
获取201 0.01 0.00 0 388 4 20000
-
总203 0.01 0.00 0 388 4 20000


选择 *
来自
表 (xxtst_p(pi =>2))


调用计数cpu经过磁盘查询当前行
-
解析1 0.01 0.00 0 45 0 0
执行1 0.00 0.00 0
获取201 0.01 0.02 0 0 20000
-
总203 0.03 0.02 0 45 0 20000


So (as expected) in a case where you are doing *exactly* the same, ie, fetching 来自 a table, then adding a pipeline function on top of that, does create an overhead, but you can also see, all the normal goodness applies, ie, we are doing array fetching both inside the function and 来自 the function itself.

当然，当功能更复杂时，管道的真正好处就会发挥作用，即

-获取 (或被传递) 一些源行
-扰动数据的附加功能
-管道输出计算结果