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
[EMAIL PROTECTED]
Sent by: [EMAIL PROTECTED]
12/31/2003 10:14 AM
Please respond to
[EMAIL PROTECTED]
To
Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
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
[EMAIL PROTECTED]
Sent by: [EMAIL PROTECTED]
12/31/2003 09:04 AM
Please respond to ORACLE-L
To: Multiple recipients of list ORACLE-L
<[EMAIL PROTECTED]>
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
<[EMAIL PROTECTED]>
Sent by: [EMAIL PROTECTED]
12/31/2003 06:24 AM
Please respond to
[EMAIL PROTECTED]
To
Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
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: <[EMAIL PROTECTED]
INET: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
