The PLAN_TABLE is a special table that shows how a SQL statement is executed. It's automatically created when you use the EXPLAIN PLAN command, which creates a report of the execution plan and stores it in the PLAN_TABLE. This table is available to all users, and can be used to display the results of the EXPLAIN PLAN statement.
You can use the script $ORACLE_HOME/rdbms/admin/utlxplan.sql to manually create a local PLAN_TABLE in your schema.
To display the execution plan of a SQL statement stored in the PLAN_TABLE, you can use the DBMS_XPLAN package. First, use the EXPLAIN PLAN command to create a report and store it in the PLAN_TABLE. Then, use the DISPLAY function of the DBMS_XPLAN package to format and display the execution plan for the statement stored in PLAN_TABLE.
Displaying execution plan from PLAN_TABLE :
SQL> EXPLAIN PLAN SET STATEMENT_ID = 'planid1' FOR SELECT * FROM emp WHERE last_name = 'John';
Explained.
SQL> SET LINESIZE 130
SQL> SET PAGESIZE 0
SQL> select * from table(DBMS_XPLAN.DISPLAY());
Plan hash value: 1600171752
-------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time |
-------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 69 | 2 (0) | 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED | EMP | 1 | 69 | 2 (0) | 00:00:01 |
|* 2 | INDEX RANGE SCAN | EMP_NAME_IX | 1 | | 1 (0) | 00:00:01 |
-------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("LAST_NAME"='John')
14 rows selected.
SQL>
No comments:
Post a Comment