SQLT and 10046 and 10053 trace

1. Getting SQLT XTRACT report for the poor performing query (baxk1rgw5ht0c). 

You may review the notes below for additional information: 

--> Download SQLT tool from here SQLT Diagnostic Tool ( Doc ID 215187.1 ) 
--> Install SQLT as per SQLT Usage Instructions ( Doc ID 1614107.1 ) 
--> Identify your SQL ID as per document How to Determine the SQL_ID for a SQL Statement ( Doc ID 1627387.1 ) 
--> Execute sqlt XTRACT method for your SQL_ID 

# cd sqlt/run 
# sqlplus apps 
SQL> START sqltxtract.sql <your_sql_id> sqltxplain_password 

SQLT XTRACT will display all the execution plans available for this query, so we can see if there is a plan stability issue. It will also provide STA report to see if any recommendations available. 
>>> FAQ: SQLT (SQLTXPLAIN) Frequently Asked Questions ( Doc ID 1454160.1 ) 

2. 10046 trace for "GOOD" and "BAD" execution: 
alter session set tracefile_identifier='10046_&your_name'; 
alter session set timed_statistics = true; 
alter session set statistics_level=all; 
alter session set max_dump_file_size = unlimited; 
alter session set events '10046 trace name context forever,level 12'; 
-- /*Run SQL here*/ ------ 
select 'CLOSE CURSOR' close_cursor from dual; --- to ensure the previous cursor is closed (or select * from dual;) 
alter session set events '10046 trace name context off'; 

3. 10053 trace for "GOOD" and "BAD" execution: 
alter session set tracefile_identifier='10053_&your_name'; 
alter session set timed_statistics = true; 
alter session set statistics_level=all; 
alter session set max_dump_file_size = unlimited; 
alter session set events '10053 trace name context forever, level 1'; 
-- /*Run SQL here*/ ------ 
alter session set events '10053 trace name context off';