10g: chaining pipelined functions is slow

From: Frank Bergemann <FBergemann_at_web.de>
Date: Fri, 8 Oct 2010 13:42:17 -0700 (PDT)
Message-ID: <af3135e7-6111-4c38-8707-48e467363825_at_j18g2000yqd.googlegroups.com>

Hi,

i use a chain of pipelined functions to separate an entire task into a sequence of processing stages - like this: (abstract, just pseudo-code, i'll try to post some working pl/sql code next - but i hope you get the point)

function fConsumer
begin

	cursor cur is select * from table(f1);
	rec cur%rowtype;
begin
	open cur;
	loop
		fetch cur into rec;
      		exit when cur%NOTFOUND;
		-- do something with rec
	end loop;
	close cur;
	return;

end;

function Calc1(rec in out nocopy ctx)
is
begin

• do some calculation on ctx
• or enrich with additional data
• (from other tables, etc) end;

function f1
return ctx_list pipelined
is

	cursor cur is select * from table(f2);
	rec cur%rowtype;
begin
	open cur;
	loop
		fetch cur into rec;
      		exit when cur%NOTFOUND;
		Calc1(rec);
		pipe row(rec);
	end loop;
	close cur;
	return;

end;

function Calc2(rec in out nocopy ctx)
is
begin

• do some calculation on ctx
• or enrich with additional data
• (from other tables, etc) end;

function f2
return ctx_list pipelined
is

	cursor cur is select * from table(f3);
	rec cur%rowtype;
begin
	open cur;
	loop
		fetch cur into rec;
      		exit when cur%NOTFOUND;
		Calc2(rec);
		pipe row(rec);
	end loop;
	close cur;
	return;

end;

[...] f3, f4, f5, ...

function CalcX(rec in out nocopy ctx)
is
begin

• do some calculation on ctx
• or enrich with additional data
• (from other tables, etc) end;

function fX
return ctx_list pipelined
is

	cursor cur is select * from table(fDataSource);
	rec cur%rowtype;
begin
	open cur;
	loop
		fetch cur into rec;
      		exit when cur%NOTFOUND;
		CalcX(rec);
		pipe row(rec);
	end loop;
	close cur;
	return;

end;

function fDataSource
return ctx_list pipelined
is

	rec ctx;
	number i;

begin

	for i in 1..10000 loop
		rec.x := i;
		rec.y := 2*i;
		pipe row(rec);
	end loop;
	return;

end;

The sequence of processing stages starts with fDataSource. Then X, X-1, X-2, ... 3, 2, 1.
And it ends up in fConsumer.
(Don't get confused by revers order for 1..X)

What i expected, was oracle being able to use separate kind of working threads for all the stages (f1, f2, f3, ..., fX). And i thought it's a quite good model to localize access to different resources (tables) to the fX/CalcX function pairs - so they don't interfere. Actually something set-up similar to a message passing I/F like CSP (communicating sequential processes). But it seems oracle does not make use of such. It doesn't scale by this. Actually is slows down significantly for each additional fX / CalcX that i introduce! It speeds up again, if i collapse functions together into this:

function fConsumer
begin

	cursor cur is select * from table(fDataSource);
	rec cur%rowtype;
begin
	open cur;
	loop
		fetch cur into rec;
      		exit when cur%NOTFOUND;
		CalcX(rec);
		[...]
		Calc2(rec);
		Calc1(rec);
		-- do something with rec
	end loop;
	close cur;
	return;

end;

Could s.o. confirm this?
Do i miss some additional option/flag/parameter for setting up pipelined functions?
Or is this something not supported by 10g but will be by > 10g?

Or just the wrong approach(?!)

• many thanks!

cheers,
Frank Received on Fri Oct 08 2010 - 15:42:17 CDT