Oracle SQL
  • LICENSE

“Collection iterator pickler fetch”: pipelined vs simple table functions

Posted on December 13, 2017 by Sayan Malakshinov Posted in oracle, PL/SQL, PL/SQL optimization, query optimizing, SQL, troubleshooting 2 Comments

Alex R recently discovered interesting thing: in SQL pipelined functions work much faster than simple non-pipelined table functions, so if you already have simple non-pipelined table function and want to get its results in sql (select * from table(fff)), it’s much better to create another pipelined function which will get and return its results through PIPE ROW().

A bit more details:

Assume we need to return collection “RESULT” from PL/SQL function into SQL query “select * from table(function_F(…))”.
If we create 2 similar functions: pipelined f_pipe and simple non-pipelined f_non_pipe,

create or replace function f_pipe(n int) return tt_id_value pipelined 
as
  result tt_id_value;
begin
  ...
  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;
begin
  ...
  return result;
end f_non_pipe;
/
Full functions definitions
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();
  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();
  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;
/
create or replace function f_udf_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();
  for i in 1..n loop
    pipe row (result(i));
  end loop;
end;
/
create or replace function f_udf_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();
  return result;
end;
/

[collapse]
Test queries

set echo on feed only timing on;
--alter session set optimizer_adaptive_plans=false;
--alter session set "_optimizer_use_feedback"=false;

select sum(id * value) s from table(f_pipe(&1));
select sum(id * value) s from table(f_non_pipe(&1));
select sum(id * value) s from table(f_pipe_for_nonpipe(&1));
select sum(id * value) s from table(f_udf_pipe(&1));
select sum(id * value) s from table(f_udf_non_pipe(&1));
with function f_inline_non_pipe(n int) return tt_id_value 
as
  result tt_id_value;
begin
     result:=tt_id_value();
     result.extend(n);
     for i in 1..n loop
        result(i):=to_id_value(i, 1);
     end loop;
     return result;
end;
select sum(id * value) s from table(f_inline_non_pipe(&1));
/
set timing off echo off feed on;

[collapse]

we’ll find that the function with simple “return result” works at least twice slower than pipelined function:

Function 1 000 000 elements 100 000 elements
F_PIPE 2.46 0.20
F_NON_PIPE 4.39 0.44
F_PIPE_FOR_NONPIPE 2.61 0.26
F_UDF_PIPE 2.06 0.20
F_UDF_NON_PIPE 4.46 0.44

I was really surprised that even “COLLECTION ITERATOR PICKLER FETCH” with F_PIPE_FOR_NONPIPE that gets result of F_NON_PIPE and returns it through PIPE ROW() works almost twice faster than F_NON_PIPE, so I decided to analyze it using stapflame by Frits Hoogland.

I added “dbms_lock.sleep(1)” into both of these function after collection generation, to compare the difference only between “pipe row” in loop and “return result”:

Modified functions

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;
/

[collapse]

And stapflame showed that almost all overhead was consumed by the function “kgmpoa_Assign_Out_Arguments”:

I don’t know what this function is doing exactly, but we can see that oracle assign collection a bit later.
From other functions in this stack(pmucpkl, kopp2isize, kopp2colsize, kopp2atsize(attribute?), kopuadt) I suspect that is some type of preprocessiong of return arguments.
What do you think about it?

Full stapflame output:
stapflame_nonpipe
stapflame_pipe

oracle pipelined functions pl/sql pl/sql functions pl/sql optimization
« SQL*Plus tips #8: How to read the output of dbms_output without “serveroutput on”
Top N biggest tables (with lobs, indexes and nested table) »
photo Sayan Malakshinov

Oracle ACE Pro Oracle ACE Pro

DEVVYOracle Database Developer Choice Award winner

Oracle performance tuning expert

UK / Cambridge

LinkedIn   Twitter
sayan@orasql.org

Recent Posts

  • CBO and Partial indexing
  • Slow index access “COL=:N” where :N is NULL
  • Where does the commit or rollback happen in PL/SQL code?
  • :1 and SP2-0553: Illegal variable name “1”.
  • ORA exceptions that can’t be caught by exception handler

Recent Comments

  • Oracle SGA 값을 증가 시킬 때 발생 장애 원인 – DBA의 정석 on Example of controlling “direct path reads” decision through SQL profile hints (index_stats/table_stats)
  • Oracle SQL | Oracle diagnostic events — Cheat sheet on Where does the commit or rollback happen in PL/SQL code?
  • Functions & Subqueries | Oracle Scratchpad on Deterministic function vs scalar subquery caching. Part 3
  • Materialized views state turns into compilation_error after refresh - kranar.top - Answering users questions... on Friday prank: select from join join join
  • Exadata Catalogue | Oracle Scratchpad on When bloggers get it wrong – part 1
  • Exadata Catalogue | Oracle Scratchpad on Serial Scans failing to offload
  • lateral join – decorrelation gone wrong – svenweller on Lateral view decorrelation(VW_DCL) causes wrong results with rownum
  • 255 column catalogue | Oracle Scratchpad on Intra-block row chaining optimization in 12.2
  • 255 column catalogue | Oracle Scratchpad on row pieces, 255 columns, intra-block row chaining in details
  • opt_estimate catalogue | Oracle Scratchpad on Correct syntax for the table_stats hint

Blogroll

  • Alex Fatkulin
  • Alexander Anokhin
  • Andrey Nikolaev
  • Charles Hooper
  • Christian Antognini
  • Coskan Gundogar
  • David Fitzjarrell
  • Igor Usoltsev
  • Jonathan Lewis
  • Karl Arao
  • Mark Bobak
  • Martin Bach
  • Martin Berger
  • Neil Chandler
  • Randolf Geist
  • Richard Foote
  • Riyaj Shamsudeen
  • Tanel Poder
  • Timur Akhmadeev
  • Valentin Nikotin

Meta

  • Log in
  • Entries feed
  • Comments feed
  • WordPress.org
©Sayan Malakshinov. Oracle SQL