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;’
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 | --- 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
1 2 3 4 5 6 7 | set lines 2000 set pagesize 2000 SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR('&sql_id')); |
3. Explain plan of a sql_id from AWR:
1 2 3 4 | 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.
1 2 3 4 | SELECT * FROM table(DBMS_XPLAN.DISPLAY_AWR('&sql_id',&plan_hash_value)); |
4. Explain plan of sql baseline:
1 2 3 4 | 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
1 2 3 4 5 | <span style="color: #000000;">SELECT * FROM TABLE(DBMS_XPLAN.display_sql_plan_baseline(sql_handle =>'SYS_SQL_71e1abffb11f9833')); </span> |
5. Explain plan for sql id from sql tuning set:
1 2 3 4 5 6 7 8 9 10 11 12 13 | -- 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)); |
