I would like to view explain plan output in sqlplus.
One easy approach is to set autotrace. You can set autotrace on to show query statistics as well as an exection plan. You can also view only the execution plan, for example,
foo_owner@FOO> set autotrace on explain
foo_owner@FOO> SELECT COUNT(*) FROM tax_facts WHERE create_date <= TO_DATE('10-JAN-12', 'DD-MON-YY');
COUNT(*)
----------
3768447
Execution Plan
----------------------------------------------------------
Plan hash value: 3136400752
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 8 | 1988 (5)| 00:00:36 |
| 1 | SORT AGGREGATE | | 1 | 8 | | |
|* 2 | INDEX FAST FULL SCAN| TAXFCT_CRTDT | 3774K| 28M| 1988 (5)| 00:00:36 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("CREATE_DATE"<=TO_DATE('10-JAN-12','DD-MON-YY'))
foo_owner@FOO>
Alternatively, you can use EXPLAIN PLAN FOR in front of your sql-statement, but viewing the plan output is less-intuitive, e.g.,
foo_owner@FOO> EXPLAIN PLAN FOR SELECT COUNT(*) FROM tax_facts WHERE create_date <= TO_DATE('10-JAN-12', 'DD-MON-YY');
Explained.
foo_owner@FOO> SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY());
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3136400752
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 8 | 1988 (5)| 00:00:36 |
| 1 | SORT AGGREGATE | | 1 | 8 | | |
|* 2 | INDEX FAST FULL SCAN| TAXFCT_CRTDT | 3774K| 28M| 1988 (5)| 00:00:36 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("CREATE_DATE"<=TO_DATE('10-JAN-12','DD-MON-YY'))
14 rows selected.
foo_owner@FOO>