Re: anyone use pipelined functions?

great response. questions inline.
----- Original Message -----
To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com> Sent: Wednesday, December 31, 2003 2:14 PM

> At the time, I did: I used simple sql_tracing for much of the analysis,
> and definitely analyzed in stages. Unfortunately, most of the trace data
> was lost. I have a couple of the files, from which I started with 10,000
> row inserts (with commit batches of 2000) vs. 10,000 directly appended
> rows.
>
> For 10,000 single row inserts (non-pipelined function), the average was
> about 370 rows/second.
> For 10,000 appended rows (pipelined), the average was about 2100
> rows/second; this scaled mostly linearly to 1000000 rows (in further
> testing), and to the total number of rows in the table. Clearly this was
> a big improvement to the original function, although the query/index was
> probably the best performance improvement overall.

did you test this with an 'insert select'? or was it not possible given your requirements?

>
> Another not insignificant contributor to the overall time of the original
> (and tuned) procedure was the target table sequence. Adding even a small
> cache (10) to the sequence dropped its overall contribution to the runtime
> significantly, but it was still the second-largest contributor to the
> tuned function, following the insert. I also removed redundant calls to
> USER with a single call and variable (an obvious programming flaw).

how do you know how much time was spent on sequences?

>
> It's interesting that such a 'little' procedure can be tuned in so many
> ways, and so quickly. There must be thousands of these problems out
> there. And this procedure was written by an Oracle consultant!
>
> Finally (ignorance disclaimer) I'd consider this a quick-and-dirty
> analysis, as I was mainly looking for obvious quick wins. (The nature of
> the problem and the time constraints warranted this.) I'm sure the
> readers here would have had much more to say.
>
> Adam
>
>
>
>
>
> Jared.Still_at_radisys.com
> Sent by: ml-errors_at_fatcity.com
> 12/31/2003 10:14 AM
> Please respond to
> ORACLE-L_at_fatcity.com
>
>
> To
> Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
> cc
>
> Subject
> Re: anyone use pipelined functions?
>
>
>
>
>
>
>
> Fantastic results Adam.
>
> You didn't perhaps do interim testing did you, so that you
> know how much of the benefit was due to the pipelined functions?
>
> You made quite a few changes, and a breakdown of the
> the benefits of each would be interesting to see.
>
> Jared
>
>
>
>
>
>
> AdamDonahue_at_maximus.com
> Sent by: ml-errors_at_fatcity.com
> 12/31/2003 09:04 AM
> Please respond to ORACLE-L
>
> To: Multiple recipients of list ORACLE-L
> <ORACLE-L_at_fatcity.com>
> cc:
> Subject: Re: anyone use pipelined functions?
>
>
>
> I recently rewrote a poor-performing data load procedure (with single row
> inserts, commit batches of 2000) to a pipelined table function, which
> enabled insert /*+ append */ into the target table, which greatly enhanced
>
> performance. The original routine contained an embedded select, a second
> select using a top-level select key, and then a large loop with data
> operations culminating with an insert of each row (and sequence value
> generation). The routine took about four hours to run.
>
> I joined the queries into a single inner join select, parallelized; added
> a cache to the sequence (which had been set to zero); added a second index
>
> to the source table to enable FFS; engineered the function to leverage
> pipelining (moving the to an insert /*+ append */ into ... select * from
> table( function ); made the requisite modifications to the target table,
> and reran the load. The time came down to about 25 minutes. The total
> work spent in engineering the procedure was about 2 hours, so the work put
>
> into tuning it + its improved runtime came in at less than the total
> original runtime! (Of course, this does not include reenabling
> constraints, triggers, etc., but these things bring the total runtime up
> to about ~1 hour in this case, still an improvement.)
>
> The benefit-cost ratio here was quite high!
>
> Adam
>
>
>
>
> <ryan_oracle_at_cox.net>
> Sent by: ml-errors_at_fatcity.com
> 12/31/2003 06:24 AM
> Please respond to
> ORACLE-L_at_fatcity.com
>
>
> To
> Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
> cc
>
> Subject
> anyone use pipelined functions?
>
>
>
>
>
>
> I read the little blurb in the 9i new features on it. The example there
> doesnt seem very useful. What have people used it for?
>
> any good articles with good examples on this?
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: <ryan_oracle_at_cox.net
> INET: ryan_oracle_at_cox.net
>
> Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> San Diego, California -- Mailing list and web hosting services
> ---------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
>
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author:
> INET: AdamDonahue_at_maximus.com
>
> Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> San Diego, California -- Mailing list and web hosting services
> ---------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
>
>
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author:
> INET: AdamDonahue_at_maximus.com
>
> Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> San Diego, California -- Mailing list and web hosting services
> ---------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
>

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Ryan
  INET: ryan_oracle_at_cox.net

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).