Re: 10g: chaining pipelined functions is slow
Date: Tue, 12 Oct 2010 03:24:44 -0700 (PDT)
Message-ID: <2ced7439-73f6-4ef3-9c9f-b3533b7c11ab_at_j18g2000yqd.googlegroups.com>
On Oct 11, 8:49 pm, Frank Bergemann <FBergem..._at_web.de> wrote:
> Got it - i'll have a detailed look at the enterprise manager.
> Your hints already helped me to change back implementation to make not
> too much use of chaining (at least not in combination with this OPEN
> LOOP FETCH CLOSE idiom)
> - many thanks!!!
No need to go for Enterprise Manager necessarily - you can trace your session using extended SQL trace (event 10046 or the DBMS_MONITOR package from 10g). If you need a detailed break-down of your PL/SQL activity there is the hierarchical PL/SQL profile DBMS_HPROF that has been introduced with 11g. It generates almost no overhead and is very easy to use, see http://download.oracle.com/docs/cd/E11882_01/appdev.112/e17125/adfns_profiler.htm#ADFNS02302.
> Most important for me was to get a _confirmation_, that there is no
> assignment of chained pipelined functions to different CPUs, thread or
> alike, but one "process" is executing the entire chain.
> So i don't go further into wrong direction.
If you want to build something similar to what you have in mind having different worker threads working on the different chain steps you could use Advanced Queueing together with DBMS_JOB / DBMS_SCHEDULER to submit multiple worker threads. AQ offers "buffered in-memory queues" that might come handy for that purpose. I've used such AQ / worker threads quite successful in the past to parallelize tasks that otherwise were running serially and therefore didn't make efficient use of the available hardware.
Regards,
Randolf
Oracle related stuff blog:
http://oracle-randolf.blogspot.com/
Co-author of the "OakTable Expert Oracle Practices" book: http://www.apress.com/book/view/1430226684 http://www.amazon.com/Expert-Oracle-Practices-Database-Administration/dp/1430226684 Received on Tue Oct 12 2010 - 05:24:44 CDT