Symptoms are that the page/portlet would be slow for some users, but not for others. It could be:
It usually happens suddenly with no change, and the action may be running well on lower environments
If we do a SQL trace we can see a slow query taking in some cases 10+ minutes. If we fetch the parameters in the query and run it directly on the database it's quite fast.
This meant the issue is with the database Oracle explain plan/ execution plan and the difference comes from the bind variables.
Work with the DBA team and the user running the action in real time to modify the explain/execution plan for the slow query (tune it).
This will resolve the problem