Wednesday, December 27, 2006

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.

5 comments:

Unknown said...

Thanks, it works.

Luis Santos said...

This will not work if a same SQL has multiple children. In this case there are several lines in v$sql, with the same sql address and hash_number, but with different child_number and child_address.

In 10g the v$session includes the child SQL info (SQL_CHILD_NUMBER column). But this column does not exists in 9i.

Until now I could not discoverer a way to join v$session to the CHILD_NUMBER or CHILD_ADDRESS columns in V$SQL.

Sachin said...

agreed - but at one time only one session could be running one version of sql and that child no should be good to get its execution plan.

Did i get your question correct?
If no - please elaborate what you want to explain.

Luis Santos said...

Finally I discovered a way to join correclty V$SESSION with V$SQL. This join correctly join the exact child_number currently used by the session.

You have to use an aux view called DBA_KGLLOCK. Join VS (V$SESSION) SADDR column with DK (DBA_KGLLOCK) KGLLKUSE column. And join DK.KGLLKHDL column with V$SQL.CHILD_ADDRESS column.

This works fine in 9i, but not in 8, as Oracle 8 V$SQL don´t have the CHILD_ADDRESS column.

Look this complete query:

select distinct vs.sid, vs.serial#, vp.spid, vsql.child_number CN, vs.process pai, vs.username, vs.osuser, vs.machine, to_char(floor(vs.last_call_et/3600),'fm0000')||':'||
to_char(floor(mod(vs.last_call_et,3600)/60),'fm00')||':'||
to_char(mod(mod(vs.last_call_et,3600),60),'fm00')||' Hs' "ACTIVE SINCE", to_char(vs.logon_time,'DD-MON-YY HH24:MI:SS') Logon
from v$session vs, v$process vp, dba_kgllock dk, v$sql vsql
where vs.paddr=vp.addr
AND SQL_HASH_VALUE='&1'
AND DK.KGLLKUSE = VS.SADDR
AND DK.KGLLKHDL = VSQL.CHILD_ADDRESS
AND VS.SQL_ADDRESS = VSQL.ADDRESS
order by machine;

Karan Kukreja ( Oracle Apps DBA) said...

Hi Sachin,

Thanks it worked. i was struggling to find one smooth query to obtain the explain plan.. And I found exactly what i was looking for.

Cheers
Karan