How To Generate Explain Plan In Oracle

How To Generate Explain Plan In Oracle

1.Generating explain plan for a sql query:

We will generate the explain plan for the query "select * from test.qader_t1;"

LOADING THE EXPLAIN PLAN TO PLAN_TABLE

SQL> explain plan for select * from test.qader_t1;

 DISPLAYING 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));