Execution plan of a running SQL statement

Lot of time there is a requirement to find out the access path (execution plan) of a SQL which is currently running. Normally, we get the long running SQL from V$Sql and then simulate the execution in our environment to find the execution plan.

This may (or may not) be the correct information because by that time either the statistics are changed or data is different (at the time of investigation).

An ideal case would be when you can get the execution plan of the SQL which is currently running. Although this information is viewable if you see from DBA console (OEM), but you may require to write a shell script which captures all the top SQL’s with their execution plans. The following understanding will help you in finding out the execution plan using command line (SQL*Plus).

Assuming a developer/user comes to you about a SQL which is running for past few hours, you first action will be to find out the session and SQL from v$session and v$sql

Let’s see this:

SQL> select sid,serial#,status from v$session where username='VINEET';

SID SERIAL# STATUS
---------- ---------- --------
138 35417 ACTIVE

You may have multiple rows here because of many sessions using the same username. In that case you have to get the SID by using Top SQL note or by your own methods.

Once you get the SID, lets find out the SQL.

1 select b.address,b.hash_value,b.child_number,b.plan_hash_value,b.sql_text
2 from v$session a, v$sql b
3 where a.SQL_ADDRESS=b.ADDRESS
4* and a.sid=138
SQL> /

ADDRESS HASH_VALUE CHILD_NUMBER PLAN_HASH_VALUE
-------- ---------- ------------ ---------------
SQL_TEXT
------------------------------------------------------------------------------

177F7178 1778505282 0 1671218824
DELETE FROM TEST WHERE ID = :1

Now we have the SQL and other attributes of it.

Last and final step is to find the execution plan of it. Lets see whether it is using the index on column ID or not.


All you need to do is to put ADDRESS, HASH_VALUE, CHILD_NUMBER in the following SQL (you know those from above SQL)

SQL> COL id FORMAT 999
SQL> COL parent_id FORMAT 999 HEADING "PARENT"
SQL> COL operation FORMAT a35 TRUNCATE
SQL> COL object_name FORMAT a30
SQL> ed
Wrote file afiedt.buf

1 SELECT id, parent_id, LPAD (' ', LEVEL - 1) || operation || ' ' ||
2 options operation, object_name
3 FROM (
4 SELECT id, parent_id, operation, options, object_name
5 FROM v$sql_plan
6 WHERE address = '&address'
7 AND hash_value = &hash_value
8 AND child_number = &child_number
9 )
10 START WITH id = 0
11* CONNECT BY PRIOR id = parent_id
SQL> /
Enter value for address: 177F7178
old 6: WHERE address = '&address'
new 6: WHERE address = '177F7178'
Enter value for hash_value: 1778505282
old 7: AND hash_value = &hash_value
new 7: AND hash_value = 1778505282
Enter value for child_number: 0
old 8: AND child_number = &child_number
new 8: AND child_number = 0

0 DELETE STATEMENT
1 0 DELETE
2 1 INDEX UNIQUE SCAN SYS_C00227769


And there you go, The Explain plan is right here.

You may want to automate this in a shell script which captures all the top sql’s and their respective plans and mail to you even when you are not online.