How to do a deep SQL tuning

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