Re: 10g: chaining pipelined functions is slow

On 9 Okt., 22:58, Randolf Geist <mah..._at_web.de> wrote:
>
> I think your slow-down could be simply caused by the fact that each of
> your pipelined table functions use single-row processing by using the
> OPEN LOOP FETCH CLOSE pattern, but your collapsed one does this only
> once. May be you want to try the same when using more efficient bulk
> processing (BULK INTO ...) when fetching from the cursor.
>
> By the way, the recommended way cascaded pipelined table functions are
> supposed to be designed is to pass a cursor as parameter into the
> function - that way you can write something like
>
> select * from table(f(cursor(select * from table(g(cursor(select *
> from table(h(cursor(some_query)))))))))
>
> where each pipelined table function produces the output that is
> consumed by the next table function as input - this can be even
> performed in parallel using parallel execution and corresponding
> declaration of the table function and its input cursor
> (parallel_enable partition by ...)
>
> For more information about the concepts, see e.g. here:
> [...]

Many thanks for these hints!
I am pretty sure, it will speed up my implementation. However i am still wondering, if oracle sets up separate processing instances (what i called 'worker threads') for each stage of the chained pipelined functions? (i.e. for each pipelined function) I understand now, that the "OPEN LOOP FETCH CLOSE pattern" is slow. But it should not become slower again just by adding more stages of this kind. In the result it should be as slow, as the slowest stage is - IF oracle would use separate processing instance for each pipelined function.
About 'parallel execution' that you mention above i have the feeling this is a different kind of parallism. It's about partitioning the entire data stream from source function to consuming function. But still using a single processing instance to execute the entire chain for a set of the partitioned data - correct? My point here is, that functional decomposition might be closely related to decomposition of accessed resources. And from that point of view separate processing units per (chained) pipelined functions would be quite useful in terms of performance. But i have the feeling that such kind of parallism - separate processing units per pipelined function - is not supported(?!)

best regards,
Frank Received on Sun Oct 10 2010 - 03:36:29 CDT