Just a quick one, to share a simple sql script in case you need to trace the optimizer computations for a single SQL statement.
As you know, Oracle Database 11g, introduced a new diagnostic events infrastructure, which greatly simplifies the task of generating a 10053 trace for a specific SQL statement.
We can capture a 10053 trace for a specific sql_id that way:
alter system set events ‘trace[RDBMS.SQL_Optimizer.*][sql:<YOUR_SQL_ID>]‘;
But the trace will be triggered after a hard parse. So, if the sql is already in the shared pool we have 2 choices:
- Wait until the statement is hard parsed again (because it has been aged out of the shared pool, because of a new child cursor creation..).
- Flush the sql from the shared pool (See Kerry Osborne’s post) so that the next execution will generate a hard parse.
So, If you can’t be patient, then you can use this script to flush the sql and enable the 10053 trace:
SQL> !cat enable_10053_sql_id.sql set serveroutput on set pagesize 9999 set linesize 155 var name varchar2(50) prompt WARNING SQL_ID WILL BE PURGED FROM THE SHARED POOL accept sql_id prompt 'Enter value for sql_id: ' BEGIN select address||','||hash_value into :name from v$sqlarea where sql_id like '&&sql_id'; dbms_shared_pool.purge(:name,'C',1); END; / alter system set events 'trace[RDBMS.SQL_Optimizer.*][sql:&&sql_id]'; undef sql_id undef name
Then just wait for the next execution and you’ll get the trace file.
To disable the trace:
SQL> !cat disable_10053_sql_id.sql prompt DISABLING 10053 trace accept sql_id prompt 'Enter value for sql_id: ' alter system set events 'trace[RDBMS.SQL_Optimizer.*][sql:&&sql_id] off';
- Starting in 11gR2 you can use DBMS_SQLDIAG.DUMP_TRACE as it doesn’t require you to re-execute the statement to get the trace (It will automatically trigger a hard parse, see MOS 225598.1).