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

Start typing and press Enter to search