Oracle: Explain plan & Tracing a particular SQL

Scenario:
You are on a mission to fix majority of database related performance issues in production environment - so, you are actively taking snapshots of the database during peak hours and generating AWR reports for the performance data.

Now you have the list of long running SQLs under SQL ordered by Elapsed Time section of the report. One of the next steps is to trace such SQLs to see what is happening when they get executed. Since we can extract the SQL identifier (SQL Id) from the AWR report for all top SQLs, tracing can be enabled as shown below.

1. Get the session id (sid) and serial# for the sql_id from active sessions.
   

   % sqlplus / as sysdba
   SQL> select sid, serial# from v$session where sql_id='<sql_id>';

   
   If you wish to see the corresponding SQL text, run the following:
   
   SQL> select sql_text from v$sql where sql_id='<sql_id>';
   
   
   
2. Enable SQL tracing for any session as follows:
   
   SQL> exec dbms_system.set_ev(<sid>, <serial#>, 10046, <level>, '');
   
   Event 10046 generates detailed information on statement parsing, values of bind variables, and wait events occurred during a particular session.
   
   Level = 1, 4, 8 or 12. Check Diagnostic event 10046 for more information about these levels.
   
   To disable tracing:
   
   SQL> exec dbms_system.set_ev(<sid>, <serial#>, 10046, 0, '');
   
   
   
3. Check the trace file(s) under udump directory.

Note:
The above steps may not make much sense with short lived sessions. An alternate option is to enable system wide tracing for all sessions as shown here:

% sqlplus / as sysdba
SQL> alter system set events '10046 trace name context forever, level level';

To disable:

SQL> alter system set events '10046 trace name context off';

I'm pretty sure that there might be better ways to collect this information. I'll update this blog entry when I find simple alternative ways.

Generating explain plan for a SQL

Explain plan will have details related to Oracle's decisions about certain things like whether to use indexes or not, or which one to use if there are more than one index. Such a plan can be generated as shown here:

SQL> set pages 100
SQL> set lines 132
SQL> select plan_table_output from table(dbms_xplan.display_cursor('<sql_id>',0)); 

The generated output will be something similar to:

--------------------------------------------------------------------------------------------------------------                      
| Id  | Operation                        | Name                      | Rows  | Bytes | Cost (%CPU)| Time     |                      
--------------------------------------------------------------------------------------------------------------                      
|   0 | SELECT STATEMENT                 |                           |       |       |    10 (100)|          |                      
|   1 |  SORT ORDER BY                   |                           |     2 |   448 |     9  (56)| 00:00:01 |                      
|   2 |   UNION-ALL                      |                           |       |       |            |          |                      
|   3 |    NESTED LOOPS                  |                           |     1 |   191 |     4   (0)| 00:00:01 |                      
|*  4 |     TABLE ACCESS BY INDEX ROWID  | WF_EVENT_SUBSCRIPTIONS    |     1 |   118 |     3   (0)| 00:00:01 |                      
|*  5 |      INDEX RANGE SCAN            | WF_EVENT_SUBSCRIPTIONS_N1 |     1 |       |     2   (0)| 00:00:01 |                      
|*  6 |     TABLE ACCESS BY INDEX ROWID  | WF_EVENTS                 |     1 |    73 |     1   (0)| 00:00:01 |                      
|*  7 |      INDEX UNIQUE SCAN           | WF_EVENTS_U1              |     1 |       |     0   (0)|          |                      
|   8 |    NESTED LOOPS                  |                           |     1 |   257 |     5   (0)| 00:00:01 |                      
|   9 |     NESTED LOOPS                 |                           |     1 |   223 |     5   (0)| 00:00:01 |                      
|  10 |      NESTED LOOPS                |                           |     1 |   191 |     4   (0)| 00:00:01 |                      
|* 11 |       TABLE ACCESS BY INDEX ROWID| WF_EVENTS                 |     1 |    73 |     2   (0)| 00:00:01 |                      
|* 12 |        INDEX UNIQUE SCAN         | WF_EVENTS_U2              |     1 |       |     1   (0)| 00:00:01 |                      
|* 13 |       TABLE ACCESS BY INDEX ROWID| WF_EVENT_SUBSCRIPTIONS    |     1 |   118 |     2   (0)| 00:00:01 |                      
|* 14 |        INDEX RANGE SCAN          | WF_EVENT_SUBSCRIPTIONS_N1 |     1 |       |     1   (0)| 00:00:01 |                      
|* 15 |      TABLE ACCESS BY INDEX ROWID | WF_EVENTS                 |     1 |    32 |     1   (0)| 00:00:01 |                      
|* 16 |       INDEX UNIQUE SCAN          | WF_EVENTS_U1              |     1 |       |     0   (0)|          |                      
|* 17 |     INDEX UNIQUE SCAN            | WF_EVENT_GROUPS_U1        |     1 |    34 |     0   (0)|          |                      
--------------------------------------------------------------------------------------------------------------                      

Acknowledgements:
Ahmed Alomari

_______________
Technorati tags:
Oracle | Performance