How to get the Execution Plan for a SQL ID In Oracle Database


1.To Display the execution plan of the last SQL statement executed by the current session 

SET LINESIZE 150 
SET PAGESIZE 2000 
SELECT * FROM table (DBMS_XPLAN.DISPLAY_CURSOR); 

2.To display the execution plan for a specific SQL ID

SET LINESIZE 150 
SET PAGESIZE 2000 
SELECT * FROM table (DBMS_XPLAN.DISPLAY_CURSOR('8cjf2rudn9una')); 

3.To display the execution plan for a specific SQL Handle from the SQL Plan Baseline.

SET LINESIZE 150 
SET PAGESIZE 2000 
SELECT t.* FROM TABLE(DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINE('SYS_SQL_b1d424fg78r295af')) t; 

4.To display the execution plan for a specific SQL ID from the SQL Tuning Set.

SET LINESIZE 150 
SET PAGESIZE 2000 
SELECT * FROM table (DBMS_XPLAN.DISPLAY_SQLSET('fsy3ubymdz500_STS','8cjf2rudn9una',367245326)); 

OR 

SELECT * FROM table (DBMS_XPLAN.DISPLAY_SQLSET('fsy3ubymdz500_STS','8cjf2rudn9una'));

5. To Display the Explain Plan from the AWR Repository (If the SQL is purged from SQL Area)

SET LINESIZE 150 
SET PAGESIZE 2000 
SELECT * FROM table (DBMS_XPLAN.DISPLAY_AWR(sql_id => '8cjf2rudn9una', plan_hash_value => 2496888546)); 
 
OR

SELECT * FROM table (DBMS_XPLAN.DISPLAY_AWR(sql_id => '8cjf2rudn9una')); 





Please do like and subscribe to my youtube channel: https://www.youtube.com/@foalabs If you like this post please follow,share and comment