In order to see why the report is slow in BO, we need to identify a good run and a bad run, and trace them and see if they are using a different plan. See the action plan below:
1/ try to run Sql Tuning Advisor on it, but with a longer time_limit, when creating the task with DBMS_SQLTUNE.CREATE_TUNING_TASK. Instead of 30 minutes, run it with 2 hour limit and it should complete.
2/ let's take a 10046 trace of the bad run, when it runs from BO application:
- connect to BO application and identify the OS pid of the session from BO:
select spid from v$process...
- in another session connect as sysdba and run:
oradebug setospid <spid from above>
oradebug tracefile_name
oradebug unlimit
ORADEBUG EVENT 10046 TRACE NAME CONTEXT FOREVER, LEVEL 12;
- in the original session run the query and wait until it ends
After the query ends, exit from BO application, and upload for review the trace file generated.
3/ I will be waiting the results of the same query in sqlplus
4/ We will need an SqlT with XTRACT method for this sqlid, 0j7p0qvjqt8pc, to see if it has multiple execution plans. Assure you choose option T when asked for Licensed Oracle Pack. (T, D or N), in order for the script to look into AWR repository as well, if the sqlid is not in the memory anymore.
Note:215187.1 SQLTXPLAIN.SQL - Enhanced Explain Plan and related diagnostic info for one SQL statement