[SOLVED] Performance is poor of pipelined function in SQL Developer

Performance is poor of pipelined function in SQL Developer

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?

Solution

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