1

I have a pipelined table function like

FUNCTION FOO
(
    <PARAMETERS_LIST>
) RETURN T_TAB PIPELINED
AS
BEGIN
FOR rec IN
(<A LITTLE BIT COMPLEX QUERY WITH PARAMETERS_LIST>)
  LOOP
    PIPE row(T_WF(<COLUMN_LIST>));
  END LOOP;
  RETURN;
END FOO;

I test the query in SQL Developer via select * from TABLE(FOO(<PARAMETERS_LIST>)) WHERE ROWNUM <= 200. It takes SQL Developer 9 seconds to return data.

While it take 0.9 seconds to result when running <A LITTLE BIT COMPLEX QUERY WITH PARAMETERS_LIST> directly in SQL Developer.

Why is the pipelined function so slow?

ErikL
  • 2,031
  • 6
  • 34
  • 57
comphilip
  • 500
  • 5
  • 15
  • How many rows does the query find - with the plain query does it take 0.9 seconds to return all the rows, or the first page of results, or 200 rows? Do you have the rownum limit when you run it directly? If so that might be being optimised for a stopkey count, but that won't happen in the cursor version as the function doesn't know you're limiting the results at that point. Showing the execution plan for the direct run might reveal something; if you are limiting that to 200 rows, show the execution plan without that limit too. – Alex Poole Mar 19 '15 at 12:56
  • SQL developer show first 50 rows of plain query. We also add rownum <= 50 to plain query and `select * from TABLE(FOO()) WHERE ROWNUM <= 50`, the performance result is the same. I will analysis execution plan next. – comphilip Mar 20 '15 at 06:07

1 Answers1

0

Difficult to say without knowing your database or the actual function, could be a range of things, like for example missing indexes.

What you could do is have a look at the plan oracle is using to execute your query:

explain plan for select * from TABLE(FOO(<PARAMETERS_LIST>)) WHERE ROWNUM <= 200

SELECT * 
FROM   TABLE(DBMS_XPLAN.DISPLAY);

that might give you a clue as to where the bottle neck is.

You are by the way using a loop in your function, this might cause a context switch which will definitely degrade performance (a lot). Try to prevent it if you can. Some more info about context switches: http://rajiboracle.blogspot.nl/2014/06/context-switches-and-performance-issue.html

ErikL
  • 2,031
  • 6
  • 34
  • 57