We have a BI Publisher report that calls a function. When running the function in SQL Developer with
select * from table (function(parameters)) it is completing within 7 minutes. We have the same query in BI Publisher and its running 2 hrs which is a lot.
For more context.
Data came from an older version of the application we are upgrading, so the data is imported and upgraded to the new version of the schema.
database is a controlled enviroment we only got a READ Only user in SQL Developer
BI Publisher data source user (i dont have the password for this so cannot test) is the schema owner where the function resides ( I tried creating a datasource with the Read only user used in SQL Developer but still the same 2 hr run time)
What things can I check to identify the issue ?
I am trying to track down every SQL used in the function for the execution plan. to try and capture it from the old version and apply it to the new one.
Thanks in advance!
I tried to use the user that we are using in SQL Developer as the data source of the report but still the same run time, I am expecting that it would be the same but its not.