Oracle-DB: Create trace file for optimizer parse (event 10053)
To view how the Oracle optimizer comes to his decision, there are several ways to create a trace file like ALTER SESSION SET EVENTS '10053 TRACE NAME CONTEXT FOREVER, LEVEL 1'; ALTER SESSION SET EVENTS 'TRACE[rdbms.SQL_Optimizer.*][sql:6jksrnjx4zvuk]'; EXEC DBMS_SQLDIAG.DUMP_TRACE(p_sql_id, p_child_number, p_component, p_file_id); Especially the DBMS_SQLDIAG.DUMP_TRACE helps to quickly review the parse steps of a SQL that already has a valid plan in the SGA. Using the parameter p_component=>'Compiler' parses the SQL again even if it only needs a soft parse. Use this steps to view the optimizer trace also without filesystem access on DB server -- grab the SQL and reparse it with trace EXEC DBMS_SQLDIAG.DUMP_TRACE(p_sql_id=>'6jksrnjx4zvuk', p_child_number=>0, p_component=>'Compiler', p_file_id=>'mine'); -- find the created trace file by the used p_file_id SELECT Inst_ID, ADR_Home, Trace_Filename, ...