We can also display the execution plan with sqlplus. For this we can use the explain plan tool with sqlplus.
To use the Explain plan tool, the PLAN_TABLE table must be present under the corresponding schema. Some tools, such as Toad, will automatically create this table when you first view the execution plan. You can also create this table as below.
|
1 2 3 |
SQL> @$ORACLE_HOME/rdbms/admin/utlxplan.sql Table created. |
Display the execution plan of the query:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
SQL> set autotrace traceonly explain SQL> select sysdate from dual; Execution Plan ---------------------------------------------------------- Plan hash value: 1546270724 ----------------------------------------------------------------- | Id | Operation | Name | Rows | Cost (%CPU)| Time | ----------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 2 (0)| 00:00:01 | | 1 | FAST DUAL | | 1 | 2 (0)| 00:00:01 | ----------------------------------------------------------------- |
When we run the “set autotrace traceonly explain” command before the query runs as above, the query will no longer produce output and only the execution plan will be displayed.
