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));
No comments:
Post a Comment