Re: anyone use pipelined functions?

From: <AdamDonahue_at_maximus.com>
Date: Wed, 31 Dec 2003 11:14:26 -0800
Message-ID: <F001.005DB5E7.20031231111426@fatcity.com>

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.

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).

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
---------------------------------------------------------------------

--

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
---------------------------------------------------------------------