Query to find the sql plan of running sql in oracle database

 

Use below query to find the sql plan of running sql. This is required to troubleshoot when query is taking long time to finish.

Connect to the database as sysdba(sqlplus '/as sysdba') and execute below queries.

SQL Plan using sql_id:

SQL> select sid,serial#,sql_id from gv$session where sid=&SID;

SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR('sql_id',0));

SQL Plan using SID:

SELECT  p.plan_table_output

FROM    v$session s
,  table(dbms_xplan.display_cursor(s.sql_id, s.sql_child_number)) p
where   s.sid = &sid2cplan
/

Thanks for going through the post.................