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('atfg645km3ykp'));
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','7r0kgzntdn7sq',367245326));
OR
SELECT * FROM table (DBMS_XPLAN.DISPLAY_SQLSET('fsy3ubymdz500_STS','7r0kgzntdn7sq'));
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 => '7r0kgzntdn7sq', plan_hash_value => 2496888546));
OR
SELECT * FROM table (DBMS_XPLAN.DISPLAY_AWR(sql_id => '7r0kgzntdn7sq'));
Recommended Posts