Oracle Database 11g -- How To Enable Optimizer Trace (10053 Event)

Problem

You need to enable Optimizer trace (Event 10053) for a session.

The session might be your current session or another user's session

Solution

1) Optimizer will not generate trace for a parsed statement, so first purge the cursor from shared pool:

SQL> SELECT address, hash_value FROM v$sqlarea where ...
SQL> EXEC sys.DBMS_SHARED_POOL.purge('<address>','<hash_value>', 'C');

2) To enable optimizer trace in the current session, use:

SQL> ALTER SESSION SET EVENTS='10053 trace name context forever, level 1';

To disable optimizer trace in the current session, use:

SQL> ALTER SESSION SET EVENTS '10053 trace name context off';

3) To enable optimizer trace in another session, use:

SQL> oradebug { setospid <SPID> [<STID>] | setorapid <PID> }
SQL> oradebug unlimit
SQL> oradebug tracefile_name
SQL> oradebug event EVENT 10053 trace name context forever, level 1

To stop tracing:

SQL> oradebug event 10053 trace name context off

4) To enable optimizer trace for a particular SQL statement: 

SQL> ALTER SESSION SET EVENTS 'trace[rdbms.SQL_Optimizer.*][sql:<sql_id>]';

Or for another session:

SQL> oradebug EVENT trace[rdbms.SQL_Optimizer.*][sql:<sql_id>]

To disable:

SQL> ALTER SESSION SET EVENTS 'trace[rdbms.SQL_Optimizer.*] off';

Or for another session:

SQL> oradebug EVENT trace[rdbms.SQL_Optimizer.*] off