Below are the few methods of generating explain plan .

 

1.Generating explain plan for a sql query:

We will generate the explain plan for the query ‘SELECT COUNT(*) FROM DBACLASS;’

--- LOAD THE EXPLAIN PLAN TO PLAN_TABLE
SQL> explain plan for
2 select count(*) from dbaclass;

Explained.

--- DISPLAY THE EXPLAIN PLAN

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

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3047708711

-----------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-----------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 428 (1)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| DBACLASS | 93966 | 428 (1)| 00:00:01 |
-----------------------------------------------------------------------

Note

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
-----
- dynamic statistics used: dynamic sampling (level=2)

13 rows selected.
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_agz791au8s6jg30a4b3a6'));

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_71e1abffb11f9833'));
5. Explain plan for sql id from sql tuning set:
-- Display all the explain plans of a sql_id from a sql set DBACLASS_SET, sql_id-dwdx28sdfsdf5

SELECT *
FROM TABLE(dbms_xplan.display_sqlset('DBACLASS_SET', 'dwdx28sdfsdf5'));


-- Display explain plan for particular plan_hash_value - 983987987

SELECT *  
FROM TABLE(dbms_xplan.display_sqlset('DBACLASS_SET','dwdx28sdfsdf5', 983987987));