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).
RE: anyone use pipelined functions?
Ryan,
I use it extensively ... for some of the utilities I wrote for application support ...
here is one sample ...
This utility shows errors in the pl/sql code, what's different is not only it shows
the errors, but also shows the source lines and exact location of the error.
To test, install on test server under an account that has select privs on
sys.error$, sys.source$, sys.obj$, sys.user$ tables ...
Usage is visible in the output section ... please NOTE the SET commands, they are very
useful in this situation
start of script --
DROP TYPE UTIL$STRINGS_TBL
/
DROP TYPE UTIL$STRINGS
/
CREATE OR REPLACE TYPE UTIL$STRINGS AS OBJECT (string_text VARCHAR2(4000))
/
CREATE OR REPLACE TYPE UTIL$STRINGS_TBL AS TABLE OF UTIL$STRINGS
/
FUNCTION show_all_errors
RETURN UTIL$STRINGS_TBL pipelined AS
--
--
CURSOR cur_01 IS
WITH
o$ AS (SELECT o.obj# obj#, DECODE (o.TYPE#,
2,'TABLE ',4,'VIEW ',7,'PROCEDURE ',8,'FUNCTION ', 9,'PACKAGE ',
11,'PACKAGE ',12,'TRIGGER ',13,'TYPE ',14,'TYPE ',29,'JAVA CLASS ',
32, 'INDEXTYPE ',33,'OPERATOR ',' ') ||
'' || u.NAME || '.' || o.NAME || '' now_compiling
FROM sys.OBJ$ o, sys.USER$ u WHERE status 1 AND u.USER# = o.owner#),
s$ AS (SELECT e.obj#, e.SEQUENCE# err_seq, '[#' ||trim(TO_CHAR(e.line,'0')) ||
'] ' || REPLACE(s.SOURCE,CHR(10),' ') src_text,
LPAD('_.', (e.position#-s.ltlen)+7, '_.') || '-^ ' || e.text err_text
FROM (SELECT * FROM sys.ERROR$ WHERE text NOT LIKE 'PL/SQL% ignored%'
ORDER BY obj#, SEQUENCE#, line, position#) e,
(SELECT obj#, line, (LENGTH(REPLACE(SOURCE,CHR(9),' ')) -
LENGTH(LTRIM(REPLACE(SOURCE,CHR(9),' ' ltlen,
LTRIM(SOURCE) SOURCE FROM sys.SOURCE$) s
WHERE s.obj# = e.obj# AND s.line = e.line)
SELECT o$.now_compiling col1, s$.src_text col2, s$.err_text col3
FROM o$, s$
WHERE o$.obj# = s$.obj#
ORDER BY o$.now_compiling, s$.err_seq;
--
szLStr VARCHAR2(100);
--
PRAGMA AUTONOMOUS_TRANSACTION;
--
--
BEGIN
--
EXECUTE IMMEDIATE 'alter session set cursor_sharing=exact';
pipe ROW (UTIL$STRINGS('*'));
pipe ROW (UTIL$STRINGS('* Displaying all errors in the database..'));
pipe ROW (UTIL$STRINGS('*'));
--
FOR cErr IN cur_01
LOOP
EXIT WHEN cur_01%NOTFOUND;
IF szLStr IS NULL THEN
szLStr := cErr.col1;
END IF;
IF (szLStr cErr.col1) OR (cur_01%rowcount = 1) THEN
pipe ROW (UTIL$STRINGS(''));
pipe ROW (UTIL$STRINGS('*'));
pipe ROW (UTIL$STRINGS('* Listing Errors For ' || cErr.col1 || ''));
pipe ROW (UTIL$STRINGS('*'));
END IF;
pipe ROW (UTIL$STRINGS(cErr.col2));
pipe ROW (UTIL$STRINGS(cErr.col3));
szLStr := cErr.col1;
END LOOP;
--
IF cur_01%isopen THEN
CLOSE cur_01;
END IF;
--
pipe ROW (UTIL$STRINGS('*'));
pipe ROW (UTIL$STRINGS('* End Of Error Listing ...'));
pipe ROW (UTIL$STRINGS('*'));
--
pipe ROW (UTIL$STRINGS('Show All Errors, ESPN Oracle Utilities, © ESPN 2003'));
RETURN;
--
END show_all_errors;
/
end of script --
Output looks like this ...
output
09:56:42 SQL set line 200 trimspool on heading off pagesize 0
09:56:53 SQL select * from table(show_all_errors());-- this is how you call it
...
*
* Displaying all errors in the database..
*
*
* Listing Errors For PACKAGE SQLAB.QUEST_SL_ERRORS_PKG
*
[#00011] INSERT INTO quest_sl_errors (collector,proc_name,timestamp,
_._._._._._._._._._.-^ PL/SQL: ORA-00942: table or view does not exist
[#00022] INSERT INTO quest_sl_errors (collector,proc_name,timestamp,
_._._._._._._._._._.-^ PL/SQL: ORA-00942: table or view does not exist
*
* Listing Errors For PACKAGE SQLAB.QUEST_SL_SCHEDULED_COLLECTOR
*
[#00035] DELETE from quest_sl_errors WHERE collector=collector_name;
_._._._._._._._._._.-^ PL/SQL: ORA-00942: table or view does not exist
*
* Listing Errors For PACKAGE SQLAB.QUEST_SL_USER_MANAGER
*
[#00299] quest_sl_errors where proc_name = user_p;
_._._._.-^ PL/SQL: ORA-00942: table or view does not exist
*
* End Of Error Listing ...
*
Show All Errors, ESPN Oracle Utilities, © ESPN 2003
27 rows selected.
09:57:15 SQL
/output
In the output [#00035] is the actual source line number in the code
I have many more utilities like this, e.g.
1. a utility that compiles all invalid objects (in multiple passes)
2. a utility that locates given user in our RAC
3. utility that shows active locks in the system
4. script that displays stats for the table and its indexes in a hierarchy ...
and many more ... most of these use pipelining so data is visible instead of having to
wait.
another Plus is there are no pesky limits of dbms_output
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).
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).
Re: anyone use pipelined functions?
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 100 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
Re: anyone use pipelined functions?
In the interests of documentation, and if I have time, I could engineer a similar 'dumb' procedure, perform trace as each modification is made, and post the results here. It's pretty easy to come up with an artificial routine, though, to do this kind of analysis oneself. Use Tom Kyte's BIG_TABLE approach, and then create a procedure to populate a separate table using single-row inserts, and subsequently, an insert append. 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).
Re: anyone use pipelined functions?
That would be cool if you have time for it. Re the sequence: is it assigned in a trigger, or directly in the SQL? [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 12/31/2003 11:19 AM Please respond to ORACLE-L To:Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:Re: anyone use pipelined functions? In the interests of documentation, and if I have time, I could engineer a similar 'dumb' procedure, perform trace as each modification is made, and post the results here. It's pretty easy to come up with an artificial routine, though, to do this kind of analysis oneself. Use Tom Kyte's BIG_TABLE approach, and then create a procedure to populate a separate table using single-row inserts, and subsequently, an insert append. 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
Re: anyone use pipelined functions?
great response. questions inline. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] 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 100 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 [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
Re: anyone use pipelined functions?
Directly in the SQL. We use Designer TAPI autosequence generation for day-to-day operations, but triggers slow down inserts and of course can't be enabled for direct path inserts. Adam [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 12/31/2003 11:29 AM Please respond to [EMAIL PROTECTED] To Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc Subject Re: anyone use pipelined functions? That would be cool if you have time for it. Re the sequence: is it assigned in a trigger, or directly in the SQL? [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 12/31/2003 11:19 AM Please respond to ORACLE-L To:Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:Re: anyone use pipelined functions? In the interests of documentation, and if I have time, I could engineer a similar 'dumb' procedure, perform trace as each modification is made, and post the results here. It's pretty easy to come up with an artificial routine, though, to do this kind of analysis oneself. Use Tom Kyte's BIG_TABLE approach, and then create a procedure to populate a separate table using single-row inserts, and subsequently, an insert append. 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
Re: anyone use pipelined functions?
My responses below are below Ryan [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 12/31/2003 11:54 AM Please respond to [EMAIL PROTECTED] To Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc Subject Re: anyone use pipelined functions? great response. questions inline. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] 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 100 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? Not sure what you mean by 'this,' but the pre-pipeline version was simply a bunch of single-row inserts within the body of the procedure. The whole point of the needing to stuff this into a routine is that data manipulation requirements made it impossible to simple use an insert into ... select func1(col1), func2(col2), ... func3(col3) ... The pipelined version leveraged insert /*+ append */ ... as select, but I never tested omitting the append hint (which would have simply run slower). I'm sure, though, that is would have at least been faster than single row inserts. So, pipelining has the advantage of allowing insert into with or without an append hint (for example, if you need to keep constraints and or triggers enabled), something you couldn't do based on looped inserts. (I believe you can before similar functions with bulk inserts within the PL/SQL code.) 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? ~1400 sequence values / second with nocache. ~12000 sequence values / second with cache 100. You only seem to get marginal benefits by increasing the cache by factors of 10. A cache of 100 was, I think, only a bit better performance wise than a cache of 10. We probably could have generate even better performance by getting the initial sequence value, using a variable within the routine to generate it, and then recreating the sequence following the load. Almost a third of the ~30 minute runtime was spent generating sequence values. It's pretty easy to test the use of a cache in a 'naive' way, too. Let's compare against manually generated values (using PL/SQL variable): [EMAIL PROTECTED]/rhsac3 create sequence cache0_seq nocache; Sequence created. [EMAIL PROTECTED]/rhsac3 create sequence cache10_seq cache 10; Sequence created. [EMAIL PROTECTED]/rhsac3 create sequence cache100_seq cache 100; Sequence created. [EMAIL PROTECTED]/rhsac3 set timing on [EMAIL PROTECTED]/rhsac3 set autotrace traceonly [EMAIL PROTECTED]/rhsac3 begin 2 for i in 0..1 loop 3 [EMAIL PROTECTED]/rhsac3 declare 2 v_n number; 3 begin 4 for i in 1..1 loop 5 select cache0_seq.nextval into v_n from dual; 6 end loop; 7 end; 8 / PL/SQL procedure successfully completed. Elapsed: 00:00:07.06-- NOCACHE [EMAIL PROTECTED]/rhsac3 / PL/SQL procedure successfully completed. Elapsed: 00:00:06.58 [EMAIL PROTECTED]/rhsac3 / PL/SQL procedure successfully completed. Elapsed: 00:00:06.91 [EMAIL PROTECTED]/rhsac3 declare 2 v_n number; 3 begin 4 for i in 1..1 loop 5 select cache10_seq.nextval into v_n from dual; 6 end loop; 7 end; 8 / PL/SQL procedure successfully completed. Elapsed: 00:00:01.46-- CACHE 10 [EMAIL PROTECTED]/rhsac3 / PL/SQL procedure successfully completed. Elapsed: 00:00:01.45 [EMAIL PROTECTED]/rhsac3 / PL/SQL procedure successfully completed. Elapsed: 00:00:01.46 [EMAIL PROTECTED]/rhsac3 declare 2 v_n number; 3 begin 4 for i in 1..1 loop 5 select cache100_seq.nextval into v_n from dual; 6 end loop; 7 end; 8 / PL/SQL procedure successfully completed. Elapsed: 00:00:01.94-- CACHE 100 [EMAIL PROTECTED
Re: anyone use pipelined functions?
My responses below are below -- sigh, it's been a long day. lol [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 12/31/2003 01:59 PM Please respond to [EMAIL PROTECTED] To Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc Subject Re: anyone use pipelined functions? My responses below are below Ryan [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 12/31/2003 11:54 AM Please respond to [EMAIL PROTECTED] To Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc Subject Re: anyone use pipelined functions? great response. questions inline. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] 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 100 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? Not sure what you mean by 'this,' but the pre-pipeline version was simply a bunch of single-row inserts within the body of the procedure. The whole point of the needing to stuff this into a routine is that data manipulation requirements made it impossible to simple use an insert into .. select func1(col1), func2(col2), ... func3(col3) ... The pipelined version leveraged insert /*+ append */ ... as select, but I never tested omitting the append hint (which would have simply run slower). I'm sure, though, that is would have at least been faster than single row inserts. So, pipelining has the advantage of allowing insert into with or without an append hint (for example, if you need to keep constraints and or triggers enabled), something you couldn't do based on looped inserts. (I believe you can before similar functions with bulk inserts within the PL/SQL code.) 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? ~1400 sequence values / second with nocache. ~12000 sequence values / second with cache 100. You only seem to get marginal benefits by increasing the cache by factors of 10. A cache of 100 was, I think, only a bit better performance wise than a cache of 10. We probably could have generate even better performance by getting the initial sequence value, using a variable within the routine to generate it, and then recreating the sequence following the load. Almost a third of the ~30 minute runtime was spent generating sequence values. It's pretty easy to test the use of a cache in a 'naive' way, too. Let's compare against manually generated values (using PL/SQL variable): [EMAIL PROTECTED]/rhsac3 create sequence cache0_seq nocache; Sequence created. [EMAIL PROTECTED]/rhsac3 create sequence cache10_seq cache 10; Sequence created. [EMAIL PROTECTED]/rhsac3 create sequence cache100_seq cache 100; Sequence created. [EMAIL PROTECTED]/rhsac3 set timing on [EMAIL PROTECTED]/rhsac3 set autotrace traceonly [EMAIL PROTECTED]/rhsac3 begin 2 for i in 0..1 loop 3 [EMAIL PROTECTED]/rhsac3 declare 2 v_n number; 3 begin 4 for i in 1..1 loop 5 select cache0_seq.nextval into v_n from dual; 6 end loop; 7 end; 8 / PL/SQL procedure successfully completed. Elapsed: 00:00:07.06-- NOCACHE [EMAIL PROTECTED]/rhsac3 / PL/SQL procedure successfully completed. Elapsed: 00:00:06.58 [EMAIL PROTECTED]/rhsac3 / PL/SQL procedure successfully completed. Elapsed: 00:00:06.91 [EMAIL PROTECTED]/rhsac3 declare 2 v_n number; 3 begin 4 for i in 1..1 loop 5 select cache10_seq.nextval into v_n from dual; 6 end loop; 7 end; 8 / PL/SQL procedure successfully completed. Elapsed: 00:00:01.46-- CACHE 10 [EMAIL PROTECTED]/rhsac3 / PL/SQL procedure successfully completed. Elapsed: 00:00:01.45 [EMAIL PROTECTED]/rhsac3 / PL/SQL procedure successfully completed. Elapsed: 00:00:01.46 [EMAIL
