Different ways to get the Explain Plan for A SQL ID

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('atfwcg8anrykp'));

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_b1d49f6074ab95af')) t;


3. 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','fsy3ubymdz500',367652949));

OR

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

4. 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 => 'ghdgm4dampwd5', plan_hash_value => 2495666596));

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