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@maximus.com
Sent by: ml-errors@fatcity.com
12/31/2003 09:04 AM
Please respond to ORACLE-L
|
To: Multiple recipients of list ORACLE-L <ORACLE-L@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@cox.net>
Sent by: ml-errors@fatcity.com
12/31/2003 06:24 AM
Please respond to
ORACLE-L@fatcity.com
To
Multiple recipients of list ORACLE-L <ORACLE-L@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@cox.net
INET: ryan_oracle@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@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@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@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: Jared.Still_at_radisys.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).
Received on Wed Dec 31 2003 - 12:14:27 CST
