tag:blogger.com,1999:blog-8677649049588007585.post8016067499459356139..comments2023-06-18T16:15:22.432+01:00Comments on PL/SQL Challenge: Explorations into Pipelined Functions (9398)Steven Feuersteinhttp://www.blogger.com/profile/16619706770920320550noreply@blogger.comBlogger10125tag:blogger.com,1999:blog-8677649049588007585.post-43595034660468012632011-11-04T17:22:52.966+00:002011-11-04T17:22:52.966+00:00Hello All,
All these examples are very instructiv...Hello All,<br /><br />All these examples are very instructive indeed.<br /><br />As per the most descriptions of the pipelined functions feature in various sources,<br />we would indeed expect the function to continue to work really "in parallel" with the calling SELECT.<br /><br />By the examples above it seems that this is NOT what happens, even for a non-autonomous pipelined function.<br /><br />If such a parallel work was indeed happening, then we may wonder what could happen in a case in which,<br />for example, the generating of rows by the pipelined function is "very fast", while processing them<br />in the loop is "very slow" (due to some time-consuming processing) ?<br />In such a case, the pipelined function would need to really buffer all the rows it is producing<br />"ahead of request moment", and this may go as far as having to buffer the entire collection,<br />similarly with a non-pipelined function.<br /><br />What we could reasonably expect, though, is to have the pipelined function buffering a number of rows<br />equal to the retrieval array size, that is, to "stop and wait for a new request" after having produced<br />the "n" rows for the next fetch.<br />An additional "nuance" here would be that the fetch array size itself may be changed on the fly,<br />if for example we perform "FETCH ... BULK COLLECT INTO ... LIMIT n" using different values for "n",<br />which is of course an uncommon practice.<br /><br />If we add to this also some DML processing and make the function autonomous, then<br />having such a function execute in parallel with the main processing, may cause the data processing results<br />to be effectively dependent on the array size, regarding not only performance but also the data content<br />itself, also something to be considered, though probably a scenario less used in practice.<br /><br />In summary, with such parallel work and buffering "up to one full array", we could benefit<br />at least partially from having the rows produced in parallel with having them consumed.<br /><br />If, instead, the rows production and consumption happen completely serially, as the above examples show,<br />then we remain with the other benefits of pipelined functions, that is, the streaming (chaining) of several<br />such functions, and the ease they offer for integrating PL/SQL processing within single SQL statements,<br />a.s.o.<br /><br />Maybe this blog can represent a basis for a good question that we could ask Steven to be so kind<br />and raise it with the PL/SQL development team on the next week meeting, for the benefit of all os us :) :)<br /><br />Thanks & Best Regards,<br />Iudithiudithhttps://www.blogger.com/profile/04905902445036068357noreply@blogger.comtag:blogger.com,1999:blog-8677649049588007585.post-58912824735785534972011-11-04T15:54:15.789+00:002011-11-04T15:54:15.789+00:00Part 3
-- results returning 110 rows with pipelin...Part 3<br /><br />-- results returning 110 rows with pipeline function in autonomous transaction<br /><br /> date from "select" date from CURSOR FOR loop<br />-----------------------------------------------------------<br />2, 1, 11/04/2011 09:10:59, 11/04/2011 09:19:15<br />4, 2, 11/04/2011 09:11:04, 11/04/2011 09:19:16<br />6, 3, 11/04/2011 09:11:09, 11/04/2011 09:19:17<br />8, 4, 11/04/2011 09:11:14, 11/04/2011 09:19:18<br />...<br />196, 98, 11/04/2011 09:19:04, 11/04/2011 09:20:52<br />198, 99, 11/04/2011 09:19:09, 11/04/2011 09:20:53<br />200, 100, 11/04/2011 09:19:14, 11/04/2011 09:20:54<br />202, 101, 11/04/2011 09:20:59, 11/04/2011 09:21:45<br />204, 102, 11/04/2011 09:21:04, 11/04/2011 09:21:46<br />206, 103, 11/04/2011 09:21:09, 11/04/2011 09:21:47<br />208, 104, 11/04/2011 09:21:14, 11/04/2011 09:21:48<br />210, 105, 11/04/2011 09:21:19, 11/04/2011 09:21:49<br />212, 106, 11/04/2011 09:21:24, 11/04/2011 09:21:50<br />214, 107, 11/04/2011 09:21:29, 11/04/2011 09:21:51<br />216, 108, 11/04/2011 09:21:34, 11/04/2011 09:21:52<br />218, 109, 11/04/2011 09:21:39, 11/04/2011 09:21:53<br />220, 110, 11/04/2011 09:21:44, 11/04/2011 09:21:54<br /><br />PL/SQL procedure successfully completed.<br /><br />Elapsed: 00:11:00.03<br /><br /><br />-- Remove the update/commit from the pipeline function and remove the autonomous transaction setting.<br />-- Similar results are seen when returning 110 rows from a non-autonomous version of the pipelined function. <br /><br />2, 1, 11/04/2011 10:01:43, 11/04/2011 10:09:59<br />4, 2, 11/04/2011 10:01:48, 11/04/2011 10:10:00<br />6, 3, 11/04/2011 10:01:53, 11/04/2011 10:10:01<br />8, 4, 11/04/2011 10:01:58, 11/04/2011 10:10:02<br />...<br />196, 98, 11/04/2011 10:09:48, 11/04/2011 10:11:36<br />198, 99, 11/04/2011 10:09:53, 11/04/2011 10:11:37<br />200, 100, 11/04/2011 10:09:58, 11/04/2011 10:11:38<br />202, 101, 11/04/2011 10:11:43, 11/04/2011 10:12:29<br />204, 102, 11/04/2011 10:11:48, 11/04/2011 10:12:30<br />206, 103, 11/04/2011 10:11:53, 11/04/2011 10:12:31<br />208, 104, 11/04/2011 10:11:58, 11/04/2011 10:12:32<br />210, 105, 11/04/2011 10:12:03, 11/04/2011 10:12:33<br />212, 106, 11/04/2011 10:12:08, 11/04/2011 10:12:34<br />214, 107, 11/04/2011 10:12:13, 11/04/2011 10:12:35<br />216, 108, 11/04/2011 10:12:18, 11/04/2011 10:12:36<br />218, 109, 11/04/2011 10:12:23, 11/04/2011 10:12:37<br />220, 110, 11/04/2011 10:12:28, 11/04/2011 10:12:38<br /><br />PL/SQL procedure successfully completed.<br /><br /><br />It would seem that all interaction with the pipeline function is serial in nature (both when in an autonomous transaction and when not in). It will only begin processing/generating the next row when requested by the invoker.<br /><br /><br />Thanks,<br />ChadAnonymousnoreply@blogger.comtag:blogger.com,1999:blog-8677649049588007585.post-49402031960729249182011-11-04T15:53:22.461+00:002011-11-04T15:53:22.461+00:00Make that a 3 part response.
Part 2
Looking furt...Make that a 3 part response.<br /><br />Part 2<br /><br />Looking further and constructing an example where the number of rows returned by the pipelined function > 100, it appears that point (1) may be false, at least in my understanding of what it says and how it relates to the autonomous transaction nature of this particular pipelined function (it would appear to indicate that the pipelined function would then continue operating in parallel with the invoker as the invoker works with the returned data and the pipelined function generates the next value). If my understanding of (1) is not correct, the following can be ignored. As seen in the examples with 3 rows, the 2 second delay in the pipeline function and the 3 second delay in the get_sysdate function are executed serially. If the example is extended to return 110 rows, one can see that the first 100 values from the pipelined function are materialized in accordance with the array optimization of the CURSOR FOR loop. There is a 5 second interval per row as they are returned, indicated by the times 9:10:59 - 9:19:14 for rows 1-100, and then a one second interval for the CURSOR FOR loop processing for these same 100 rows (9:19:15 - 9:20:54). If the pipelined function was continuing its work (in parallel) when control was passed to the CURSOR FOR loop after 100 rows, the time gap would not continue to be 5 seconds for rows 100+. At minimum, the time gap between the CURSOR FOR loop timestamp of row 100 and and the date from the 'select' for row 101 would be 3 seconds rather than the returned 5 seconds (9:20:54 - 9:20:59) (2 seconds from pipelined function occuring in parallel while the CURSOR FOR loop operated on the first 100 rows). It would appear that the autonomous transaction nature of this particular example enforces serialization for all interaction with the pipelined function.Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-8677649049588007585.post-21483634354432059522011-11-04T15:50:03.961+00:002011-11-04T15:50:03.961+00:00I'm posting a 2 part response
Chad
Part 1
...I'm posting a 2 part response<br /><br />Chad<br /><br /><br />Part 1<br /><br />_Nikotin - good point. I had the exact same thought after posting and realizing the small test case (and the quiz question) did not cross the default array optimization barrier of 100. I knew I had to look into this the next work day.<br /><br />Kim Berg Hansen - I agree with analysis. However, I can see the wording of "need not" in its context as meaning one of two things. 1: either/or as in materialize all rows at once or materialize only 1 row at a time or 2: something potentially other than the stated "materialize all rows" which could mean 1 row at a time or some value >= 1 potentially as high as all rows. Your analysis uses meaning 2, while I was looking at meaning 1. The default array optimization of the cursor loop would materialize 100 rows at a time.<br /><br />All - thanks for the info on array sizing, SQL Plus parms, etc. I was not able to cover all of these issues in my initial example and perhaps could have been clearer. These options and contributed examples definitely show the impact of these settings on observed behavior. As with most of the PL/SQL Blog topics, the further one investigates, the more difficult it is to address all the issues from all angles in a succinct manner.<br /><br />As such, it would seem that point (2) could be true as stated in some cases, but there could also be cases where it is false, as the back and forth communication for each row returned by the pipelined function could slow things down vs returning all rows with a single return. As stated by others, many times this is not possible as the data set involved is just too large to materialize completely. Also, point (3) could be true, where less PGA is used, but may also be false, if the data volume is small (but that would likely not be a realistic scenario or something to worry about if materializing less than 100 total rows)Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-8677649049588007585.post-77628352349101381802011-11-04T14:27:13.075+00:002011-11-04T14:27:13.075+00:00From Niels Hecker - Part 3 (sorry, Niels, for spel...From Niels Hecker - Part 3 (sorry, Niels, for spelling your name wrong in previous posts!):<br /><br />And that's one of the reasons why pipelined table functions were introduced. They are really useful for processing of ETL-operations (extract - transform - load). You can for example nest the calls for pipelined table functions, e.g. (a little bit changed from the documentation)<br /><br />SELECT *<br />FROM TABLE( PipeFunc_Outer( CURSOR(<br /> SELECT *<br /> FROM TABLE( PipeFunc_Inner()))));<br /><br />where PipeFunc_Outer() can process each record from PipeFunc_Inner() immediately when it is piped away to avoid the buffering of the complete output of PipeFunc_Inner().<br /><br />The only question is - why can't we just say:<br /><br />SELECT *<br />FROM TABLE( PipeFunc_Outer( PipeFunc_Inner()));<br /><br />But that's a question for Steven (I haven't never tried it, perhaps it is possible).<br /><br />Greetings, NielsSteven Feuersteinhttps://www.blogger.com/profile/16619706770920320550noreply@blogger.comtag:blogger.com,1999:blog-8677649049588007585.post-80916037037518042192011-11-04T14:26:33.498+00:002011-11-04T14:26:33.498+00:00From Neils Hecker - Part 2
To show this internal ...From Neils Hecker - Part 2<br /><br />To show this internal processing I have changed the code a little bit more. The first execution uses a cursor-loop:<br /><br />DECLARE<br /> CURSOR cData IS<br /> SELECT a.column_value, rownum, get_sysdate date_val<br /> FROM TABLE (plch_pipeline.double_values (<br /> CURSOR (SELECT line<br /> FROM all_source<br /> WHERE (owner = 'SYS')<br /> AND (name = 'STANDARD')<br /> AND (type = 'PACKAGE')<br /> AND (line <= 250)<br /> ORDER BY line))) a;<br />BEGIN<br /> FOR rec IN cData LOOP<br /> dbms_output.put_line(<br /> rec.column_value || ', ' || rec.rownum || ', ' || <br /> rec.date_val || ', ' || <br /> to_char(sysdate,'mm/dd/yyyy hh24:mi:ss'));<br /> END LOOP;<br />END;<br />/<br /><br />2, 1, 11/04/2011 10:50:27, 11/04/2011 10:53:45<br />4, 2, 11/04/2011 10:50:29, 11/04/2011 10:53:45<br />.....<br />198, 99, 11/04/2011 10:53:43, 11/04/2011 10:53:45<br />200, 100, 11/04/2011 10:53:45, 11/04/2011 10:53:45<br />202, 101, 11/04/2011 10:53:47, 11/04/2011 10:57:05<br />204, 102, 11/04/2011 10:53:49, 11/04/2011 10:57:05<br />.....<br />398, 199, 11/04/2011 10:57:03, 11/04/2011 10:57:05<br />400, 200, 11/04/2011 10:57:05, 11/04/2011 10:57:05<br />402, 201, 11/04/2011 10:57:07, 11/04/2011 10:58:45<br />404, 202, 11/04/2011 10:57:09, 11/04/2011 10:58:45<br />.....<br />498, 249, 11/04/2011 10:58:43, 11/04/2011 10:58:45<br />500, 250, 11/04/2011 10:58:45, 11/04/2011 10:58:45<br /><br />As you can see the internal bulk processing buffers 200 rows and then processes them.<br /><br />The second code-fragment is change accordingly:<br /><br />DECLARE<br /> CURSOR cData IS<br /> SELECT a.column_value, rownum, get_sysdate date_val<br /> FROM TABLE (plch_pipeline.double_values (<br /> CURSOR (SELECT line<br /> FROM all_source<br /> WHERE (owner = 'SYS')<br /> AND (name = 'STANDARD')<br /> AND (type = 'PACKAGE')<br /> AND (line <= 250)<br /> ORDER BY line))) a;<br /><br /> rec cData%ROWTYPE;<br />BEGIN<br /> OPEN cData;<br /> LOOP<br /> FETCH cData INTO rec;<br /> EXIT WHEN cData%NOTFOUND;<br /><br /> dbms_output.put_line(<br /> rec.column_value || ', ' || rec.rownum || ', ' || <br /> rec.date_val || ', ' || <br /> to_char(sysdate,'mm/dd/yyyy hh24:mi:ss'));<br /> END LOOP;<br />END;<br />/<br /><br />2, 1, 11/04/2011 10:50:30, 11/04/2011 10:50:30<br />4, 2, 11/04/2011 10:50:32, 11/04/2011 10:50:32<br />.....<br />198, 99, 11/04/2011 10:53:46, 11/04/2011 10:53:46<br />200, 100, 11/04/2011 10:53:48, 11/04/2011 10:53:48<br />202, 101, 11/04/2011 10:53:50, 11/04/2011 10:53:50<br />204, 102, 11/04/2011 10:53:52, 11/04/2011 10:53:52<br />.....<br />398, 199, 11/04/2011 10:57:06, 11/04/2011 10:57:06<br />400, 200, 11/04/2011 10:57:08, 11/04/2011 10:57:08<br />402, 201, 11/04/2011 10:57:10, 11/04/2011 10:57:10<br />404, 202, 11/04/2011 10:57:12, 11/04/2011 10:57:12<br />.....<br />498, 249, 11/04/2011 10:58:46, 11/04/2011 10:58:46<br />500, 250, 11/04/2011 10:58:48, 11/04/2011 10:58:48<br /><br />In the output you can easily see that each row is processed immediately when it is generated.Steven Feuersteinhttps://www.blogger.com/profile/16619706770920320550noreply@blogger.comtag:blogger.com,1999:blog-8677649049588007585.post-53934728676966161642011-11-04T14:24:55.415+00:002011-11-04T14:24:55.415+00:00I am going to post a multi-part response from Neil...I am going to post a multi-part response from Neils Hecker. Unfortunately, Blogger is very annoying regarding max length of posts and formatting. So, a heads up: we plan to move commentary into the Challenge website itself in the next release. No more blog!<br /><br />From Neils - Part 1<br /><br />Hi folks,<br /><br />I have changed the example a little bit so that it uses a cursor. In the first example you could see the same thing that Chad showed us before:<br /><br />DECLARE<br /> CURSOR cData IS<br /> SELECT a.column_value, rownum, get_sysdate date_val<br /> FROM TABLE (plch_pipeline.double_values (<br /> CURSOR (SELECT line<br /> FROM user_source<br /> WHERE (name = 'PLCH_PIPELINE')<br /> AND (type = 'PACKAGE')<br /> AND (line <= 3)<br /> ORDER BY line))) a;<br /><br />BEGIN<br /> FOR rec IN cData LOOP<br /> dbms_lock.sleep(1);<br /> dbms_output.put_line(<br /> rec.column_value || ', ' || rec.rownum || ', ' || <br /> rec.date_val || ', ' || <br /> to_char(sysdate,'mm/dd/yyyy hh24:mi:ss'));<br /> END LOOP;<br />END;<br />/<br /><br />2, 1, 11/04/2011 10:42:25, 11/04/2011 10:42:30<br />4, 2, 11/04/2011 10:42:27, 11/04/2011 10:42:31<br />6, 3, 11/04/2011 10:42:29, 11/04/2011 10:42:32<br /><br />If you instead use a normal cursor loop, all is fine and you see that each row is given back to PL/SQL as it is generated:<br /><br />DECLARE<br /> CURSOR cData IS<br /> SELECT a.column_value, rownum, get_sysdate date_val<br /> FROM TABLE (plch_pipeline.double_values (<br /> CURSOR (SELECT line<br /> FROM user_source<br /> WHERE (name = 'PLCH_PIPELINE')<br /> AND (type = 'PACKAGE')<br /> AND (line <= 3)<br /> ORDER BY line))) a;<br /><br /> rec cData%ROWTYPE;<br /><br />BEGIN<br /> OPEN cData;<br /> LOOP<br /> FETCH cData INTO rec;<br /> EXIT WHEN cData%NOTFOUND;<br /><br /> dbms_lock.sleep(1);<br /> dbms_output.put_line(<br /> rec.column_value || ', ' || rec.rownum || ', ' || <br /> rec.date_val || ', ' || <br /> to_char(sysdate,'mm/dd/yyyy hh24:mi:ss'));<br /> END LOOP;<br />END;<br />/<br /><br />2, 1, 11/04/2011 10:42:59, 11/04/2011 10:43:00<br />4, 2, 11/04/2011 10:43:02, 11/04/2011 10:43:03<br />6, 3, 11/04/2011 10:43:05, 11/04/2011 10:43:06<br /><br />The difference in the processing lies in the PL/SQL optimizer, which internally generates a BULK COLLECT INTO if you use a for-loop.Steven Feuersteinhttps://www.blogger.com/profile/16619706770920320550noreply@blogger.comtag:blogger.com,1999:blog-8677649049588007585.post-80114400066972516062011-11-04T13:56:43.728+00:002011-11-04T13:56:43.728+00:00Hello All,
In addition to setting plsql_optimize_...Hello All,<br /><br />In addition to setting plsql_optimize_level = 0, the same test can be tried by using an explicit cursor, and fetching the rows one by one, instead of the FOR LOOP, this way being closer to a real one-to-one "dialogue" between the calling statement and the pipelined function.<br /><br />Or, if directly testing the SELECT in SQL*PLUS, using SET ARRAYSIZE 1.<br />The default array size is 15, which is higher than our 3 rows returned.<br /><br />I guess that when fetching row by row, the results will be a little bit different,<br />I will also try it when I will be back to my database.<br />I don't expect this to depend on whether the pipelined function does perform DML (and thus should be defined as autonomous) or not.<br /><br />Anyway, the essential lesson of this quiz is that a pipelined function performing DML operations alternating with PIPE ROW statements should be effectively "broken" into several transactions, because each return of control to the calling statement requires its terminating COMMIT, so, in fact, our autonomous transaction becomes "several autonomous transactions".<br /><br />We should of course be aware of the data consistency implications of such a technical requirement.<br /><br />I can confirm from my own experience that the PGA memory usage decreases significantly<br />when using a pipelined function versus a non-pipelined table function, especially with very large collections.<br /><br />The pipelined functions topic is a very interesting one, I rememebr how enthusiastic I was about them when they helped me solve a tough problem of creating custom views to retrieve data from a database used by SAP, which sometimes uses some "exotic modes" of storing data, even in an Oracle database.<br /><br />Thanks & Best Regards,<br />Iudithiudithhttps://www.blogger.com/profile/04905902445036068357noreply@blogger.comtag:blogger.com,1999:blog-8677649049588007585.post-66984875981010091782011-11-04T11:28:02.220+00:002011-11-04T11:28:02.220+00:00It's simple array optimization for CURSOR FOR ...It's simple array optimization for CURSOR FOR Loop in PL/SQL. You can use plsql_optimize_level = 0 to disable it.<br /><br />And you can also use sysdate in PLSQL, not necessary by select into from dual._Nikotinhttps://www.blogger.com/profile/05173144223657420531noreply@blogger.comtag:blogger.com,1999:blog-8677649049588007585.post-89820923985901206892011-11-04T07:40:54.047+00:002011-11-04T07:40:54.047+00:00Interesting points.
But notice a couple of operat...Interesting points.<br /><br />But notice a couple of operative words:<br /><br />(2) ... collection *need not* be constructed and returned ...<br /><br />(3) ... the object cache *need not* materialize ...<br /><br />The pipelined function *need not* construct everything in one go. But of course the database *may* have to anyway depending on how and where the function is used. Or how the client fetches the data. That SQL*PLUS prompt does not show each row as it is created I suspect is array fetching kicking in.<br /><br />So point is if you create a function returning a collection non-pipelined, you *will* construct the entire thing - if you do pipelined it sometimes won't help much, but often will help a lot, depending on how and where it is used.Kim Berg Hansenhttps://www.blogger.com/profile/06491635470794828550noreply@blogger.com