We have a few reports in discoverer that are running forever in prod but the same reports complete in 5-10 mins in non prod environments.
I am not sure where to start debugging this issue.
Please provide any troubleshooting docs that I can use to debug the issue.
There are many many reasons why two environments might behave differently. The first check is whether the SQL and the SQL execution plan being run in the same on both environments. You can do this my using the SQL inspector in Discoverer. If you are using eBS then you may be using some of the eBS data security in your reports. This can be a cause of poor performance and will not necessarily show up in the SQL plans. Also check that the volumes of data in the database are the same for both environments and the database stats are up to date.