AUTOTRACE - Execution Plan for SQL statement

Note: Using Analyze is obsolete. Use dbms_stats features for CBO - Cost based Optimizer

STEPS FOR ENABLING AUTOTRACE

SQL> @utlxplan
SQL> create public synonym plan_table for plan_table
SQL> grant all on plan_table to public
exit sqlplus and cd $oracle_home/sqlplus/admin
log into sqlplus as SYS
SQL> @plustrce
SQL> grant plustrace to public

SQL> set autotrace traceonly explain

SQL> set autotrace traceonly explain

SQL> select * from np_trial.test1 where name like '%AHE%';

Execution Plan
----------------------------------------------------------
Plan hash value: 4122059633

---------------------------------------------------------------------------
| Id  | Operation         | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |       |  5075 | 55825 |   262   (1)| 00:00:04 |
|*  1 |  TABLE ACCESS FULL| TEST1 |  5075 | 55825 |   262   (1)| 00:00:04 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("NAME" LIKE '%AHE%')

SQL> set autotrace OFF

WITHOUT AUTOTRACE

SQL> EXPLAIN PLAN FOR select * from np_trial.test1 where name like '%AHE%';

SQL> @?/rdbms/admin/utlxpls

   ##or - use the following manual query of plan_table##

COLUMN "SQL" FORMAT a56  
SELECT lpad(' ',2*level)||operation||''  
        ||options ||' '||object_name||
       decode(OBJECT_TYPE, '', '', 
           '('||object_type||')') "SQL",
       cost "Cost", cardinality "Num Rows"
  FROM   plan_table WHERE plan_id = (select max(plan_id) from plan_table)
CONNECT BY prior id = parent_id
  START WITH id = 0;