[Tip] View Plan for Execution of a SQL Running

[dr_gori]

This tip came from Roger Schrag.

Imagine that you have rolled a SQL that is taking too long. Trace is turned off and you want to know what might be wrong with SQL. With this tip, you see the execution plan of this SQL.

1. Choose the SQL that is turning

Select all

select address, hash_value, child_number, sql_text  
from v$sql  
where users_executing <> 0 
/

2. Rotate the SQL below, informing the address, hash_value, child_number returned from the first SQL.

Select all

SET VERIFY OFF 
COL id          FORMAT 999 
COL parent_id   FORMAT 999 HEADING "PARENT" 
COL operation   FORMAT a35 TRUNCATE 
COL object_name FORMAT a30 
SELECT  id, parent_id, LPAD (' ', LEVEL - 1) ||  
        operation || ' ' || options operation, object_name 
FROM    ( 
        SELECT id, parent_id, operation, options, object_name 
        FROM   v$sql_plan 
        WHERE  address = '&address' 
        AND    hash_value = &hash_value 
        AND    child_number = &child_number 
        ) 
START WITH id = 0 
CONNECT BY PRIOR id = parent_id;