Performance - How To Generate Explain Plan in Oracle Database



Various methods of generating explain plan .

 
1.Explain plan for a sql query:

Query: 
SELECT COUNT(*) FROM employee;

--- LOAD THE EXPLAIN PLAN TO PLAN_TABLE

SQL> explain plan for select count(*) from employee;

Explained.

--- DISPLAY THE EXPLAIN PLAN

SQL> select * from table(dbms_xplan.display);


2. Explain plan for a sql_id from cursor
 

set lines 2000
set pagesize 2000

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

3. Explain plan of a sql_id from AWR:

SELECT * FROM table(DBMS_XPLAN.DISPLAY_AWR('&sql_id'));


Above will display the explain plan for all the plan_hash_value in AWR. If you wish to see the plan for a particular plan_hash_value.

SELECT * FROM table(DBMS_XPLAN.DISPLAY_AWR('&sql_id',&plan_hash_value));

4. Explain plan of sql baseline:

SELECT * FROM   TABLE(DBMS_XPLAN.display_sql_plan_baseline(plan_name=>'SQL_PLAN_hjkadgkjaduad3232haass'));

If you wish the see the plan for a sql_handle,then

SELECT * FROM   TABLE(DBMS_XPLAN.display_sql_plan_baseline(sql_handle =>'SYS_SQL_43jah7w2adpj29hda'));

5. Explain plan for sql id from sql tuning set:

-- Display all the explain plans of a sql_id from a sql set employee_SET, sql_id-gdjhd6asks7aad

SELECT *
FROM TABLE(dbms_xplan.display_sqlset('employee_SET', 'gdjhd6asks7aad'));


-- Display explain plan for particular plan_hash_value - 1475959015

SELECT *  
FROM TABLE(dbms_xplan.display_sqlset('employee_SET','gdjhd6asks7aad', 1475959015));




If you like please follow and comment