Flush a single SQL statement and capture a 10053 trace for it

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:

  1. 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..).
  2. 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';

Remark:

  • 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).
About these ads
This entry was posted in ToolKit. Bookmark the permalink.

2 Responses to Flush a single SQL statement and capture a 10053 trace for it

  1. Edu says:

    Thanks !The information about the trace enables after a hard parse is very useful!

  2. Pingback: 10053 trace | Oracle Scratchpad

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s