Wednesday, September 28, 2011

Tracing a Specific SQL Statement



In order to make your environment tracing compatible, see the below post first
http://bash-dba.blogspot.com/2011/09/setting-up-environment-for-sql-tracing.html

Now, if you want to find out where the database is spending most of its time during the execution of certain SQL statement, use the following steps below

Step 1:

set up the trace

SQL> alter session set events 'sql_trace level 12';

Session altered.

Step 2:

Execute the SQL statement

SQL> select count(*) from BILAL.my_table;

  COUNT(*)
----------
    303014

Step 3:

Set the tracing off

SQL> alter session set events 'sql_trace off';

Session altered.

Step 4:

Find the SQL ID of the statement issued in Step2

SQL> select sql_id,sql_text
           from v$sql
           where sql_text='select count(*) from BILAL.my_table';

SQL_ID                       SQL_TEXT
--------------------------------------------------------------------------------
8w33um44a0fh2      select count(*) from BILAL.my_table

Step 5:

Set the tracing for the SQL_ID you have pulled out from step 4

SQL> alter session set events 'sql_trace [sql:8w33um44a0fh2] level 12';

Session altered.

Step 6:

Execute the statement

SQL> select count(*) from BILAL.my_table;

  COUNT(*)
----------
    303014

Step 7:

Turn off the tracing

SQL>  alter session set events 'sql_trace[sql:8w33um44a0fh2] off';

Session altered.


No comments:

Post a Comment