Hi Tom,

I'm trying to get rid of a performance problem in APEX with a pipelined function within an db-package. Reading ora-documentations and searching the web/comunnities wasn't successful. Sorry if my question is already answered - i haven't found it.

My situation:

1) for different facilities of our company there are (physical) separated oracle databases which hold production data.
2) each of the db-installations of 1) hold the same data-model.
3) there is a central APEX-info page for requesting data for each of the db from 1). In dependence of the user(groups), the APEX-info-page should extract the data from the proper database and put them into an dynamic report (about 5.000 to 10.000 rows).
4) To meet the requirements of 3) and to avoid multiple function programming I've programmed as followed at db-site (Oracle DB 12c Enterprise Ed. Rel. 12.1.0.2.0 - 64bit Production):
4.1) created db-links to each database of 1).
4.2) created user-defined type (create or replace xyz_t AS OBJECT) and therefore an table (create or replace type xyz_ct as table of xyz_t;)
4.3) created an package with an pipelined function (FUNCTION get_prod_Data(facility varchar2) RETURN xyz_ct PIPELINED)

5) programming the function
5.1) create an ref cursor
5.2) define dynamic statement to differ between the db-links. ('Select a, b, c from prod_tbl@' || dblink || ', prod_tbl2@' || dblink || where ...').
5.3) open cursor for statement 5.2)
loop
fetch cursor into variables
exit when cursor%NOTFOUND;

pipe row (xyz_t(...));
end loop;
close cursor;

Confusion:
c1) calling the function at the database "select * from table(package.get_prod_Data('niceCity1'));" will return the datasets within 1 second.
c2) calling the function from APEX for an dynamic report, the datasets are returned > 2 minutes (!!!).
c3) changing the implementation of the function to (without dynamic statement):
for runvar1 in (
Select a, b, c
from prod_tbl@dblink_c1, prod_tbl2@dblink_c1
where ...'
)
loop
pipe row (xyz_t(runvar1.a, runvar1.b, runvar1.c))
end loop;

Effect: data is return at the databas and also at APEX within seconds (instead of minutes at APEX).
c4) extracting data for the dynamic report directly on APEX with the origin select-statement ('Select a, b, c from prod_tbl@dblink_c1, prod_tbl2@dblink_c1 where ...') there is also an good performance.

So it seems to be a subject of the cursor within the package/function called out of APEX. Why is it so fast calling the function at database-site but has rather poor performance at APEX-site?

Any hint is appreciated.

Cheers
Peter