Execution Plan Utilities

1) Set statement_id

explain plan

set statement_id = 'plan_contract' for

select SYS_ID,CONTRACT_ID,LIAB_AMT

 from MYAPPS.contract

 where SYS_ID >10;

select plan_table_output from table(dbms_xplan.display('plan_table','plan_contract','serial'));

2) Querying the AWR to get the stored plans

select EMP_ID,CONTRACT_ID,MAXI_LIAB_AMT

from app.my_contract where  EMP_ID > 15

select sql_id, sql_text

from v$sql

where sql_text like '%EMP_ID > 15%';

SQL_ID

-------------

SQL_TEXT

--------------------------------------------------------------------------------

gkbhq6v23bdvm

select sql_id, sql_text from v$sql where sql_text like '%EMP_ID > 15%'

SQL> select plan_table_output from table(dbms_xplan.display_awr('gkbhq6v23bdvm'));

no rows selected ==> this indicate the statement has not yet been loaded to AWR.  You can force all SQLs to be captured by changing topnsql to MAXIMUM

SQL> exec dbms_workload_repository.modify_snapshot_settings(topnsql =>'MAXIMUM');

PL/SQL procedure successfully completed.

SQL> exec dbms_workload_repository.create_snapshot();

PL/SQL procedure successfully completed.

SQL> exec dbms_workload_repository.modify_snapshot_settings(topnsql =>'DEFAULT');

PL/SQL procedure successfully completed.

SQL> select plan_table_output from table(dbms_xplan.display_awr('gkbhq6v23bdvm'));

3) set autotrace on     

set autotrace traceonly

set autotrace traceonly explain

set autotrace traceonly statistics

Enable/Disable SQL Trace

For your own session: 

exec dbms_monitor.session_trace_enable;  exec dbms_session.set_sql_trace(true);

exec dbms_monitor.session_trace_disable; exec dbms_session.set_sql_trace(false);

You can also enable SQL for other session

Execute dbms_monitor.session_trace_enable(session_id, serial_id);

Execute dbms_monitor.session_trace_enable(session_id, serial_id, waits=>TRUE, binds=>TRUE);

tkprof command to format

Tkprof tracefile output sys=no sort=excpu print=3 (sort:  order in which to sort the cpu time spending 3  statements )