Oracle SQL

    Tag Archives: pipelined functions

    “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

    Simple Android Oracle client

    Get it on Google Play

    About us

    photo Sayan Malakshinov

    Oracle ACE Associate Oracle ACE Associate
    Oracle performance tuning expert
    Russia / Moscow / Transmedia Dynamics
    photo Bair Malakshinov

    Ph.d candidate
    Senior Oracle Developer
    Poland / Cracow / Luxoft

    Popular Posts

    • Differences between integer(int) in SQL and PL/SQL 0 comments
    • Deterministic function vs scalar subquery caching. Part 1 8 comments
    • Amazing optimization of getting distinct values from the index, and TopN for each of them 4 comments
    • SQL*Plus tips #6: Colorizing output 4 comments
    • SQL*Plus tips #5: sql_text/sql_fulltext formatting(sql beatifier) 13 comments
    • SQL*Plus tips. #1 5 comments
    • A couple of well-known but often forgotten things for PL/SQL developers 2 comments
    • SYS_OP_MAP_NONNULL is in the documentation now 0 comments
    • SQL*Plus tips #4: Branching execution 0 comments
    • Oracle 12c: Lateral, row_limiting_clause 3 comments

    Recent Posts

    • Top-N again: fetch first N rows only vs rownum
    • Docker with Oracle database: install patches automatically
    • Top N biggest tables (with lobs, indexes and nested table)
    • “Collection iterator pickler fetch”: pipelined vs simple table functions
    • SQL*Plus tips #8: How to read the output of dbms_output without “serveroutput on”

    Email Subscription

    Recent Comments

    • SQL*Plus 256 Colours in terminal | EDUARDO CLARO on SQL*Plus tips #6: Colorizing output
    • A simple SQL*Plus parameter parser | EDUARDO CLARO on SQL*Plus tips. #1
    • Sayan Malakshinov on “Collection iterator pickler fetch”: pipelined vs simple table functions
    • Frits on “Collection iterator pickler fetch”: pipelined vs simple table functions
    • SQL*Plus tips #8: How to read the output of dbms_output without 'serveroutput on' - SSWUG.ORG on SQL*Plus tips #8: How to read the output of dbms_output without “serveroutput on”
    • Adaptive serial direct path read decision ignores object statistics since 12.1 - SSWUG.ORG on Adaptive serial direct path read decision ignores object statistics since 12.1
    • Oracle issues after upgrade to 12.2 - SSWUG.ORG on Oracle issues after upgrade to 12.2
    • Ampersand instead of colon for bind variables - SSWUG.ORG on Ampersand instead of colon for bind variables
    • Евгений Бабин on Oracle issues after upgrade to 12.2
    • Oracle SQL | How even empty trigger increases redo generation on Triggers and Redo: changes on 12.2

    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

    Categories

    Aggregated by OraNA Aggregated by OraFAQ

    Meta

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