I have a stored procedure which returns a refcursor. It takes about 15 minutes to return. The same query run dynamically returns in seconds. If I open the ref cursor with a string variable containing the query, it runs in 2 seconds.
The query is very complex and uses functions within both select and where clauses. The performance seems to have changed when we migrated to 12c from 11gR2.
Has anyone seen this issue and suggest any remedies short of a total rewrite?
Procedure spMyProc( piocur in out sys_refcursor)
is
begin
open piocur for select fn(x),fn(y) ....from x where a= fn(z) ;
end;
runs 15 minutes.
select fn(x),fn(y) ....from x where a= fn(z) ;
runs 2 seconds.
Procedure spMyProc( piocur in out sys_refcursor)
is
sqlstr varchar2(200):='select fn(x),fn(y) ....from x where a= fn(z)';
begin
open piocur for sqlstr ;
end;
runs 2 seconds.