Using Autotrace

SQL> set autotrace traceonly explain
SQL> select ename from emp where sal > 500;

Execution Plan
———————————————————-
Plan hash value: 2872589290
————————————————————————-
| Id | Operation | Name | Rows | Bytes |Cost (%CPU)| Time |
————————————————————————-
| 0 | SELECT STATEMENT | | 1 | 25 | 2 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| EMP | 1 | 25 | 2 (0)| 00:00:01 |
————————————————————————-

Using DBMS_XPLAN Package

SQL> explain plan for
select ename from emp where sal > 500;
Explained.

SQL> select * from TABLE(dbms_xplan.display);

PLAN_TABLE_OUTPUT
——————————
Plan hash value: 2872589290
————————————————————————-
| Id | Operation | Name | Rows | Bytes |Cost (%CPU)| Time |
————————————————————————-
| 0 | SELECT STATEMENT | | 1 | 25 | 2 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| EMP | 1 | 25 | 2 (0)| 00:00:01 |
————————————————————————-

Using HINT 10g and Above Only

SQL> alter session set STATISTICS_LEVEL=all;
SQL> set serveroutput off
SQL> select /*+ gather_plan_statistics */ ename from emp where sal > 500;
SQL> select * from table(dbms_xplan.display_cursor(null, null, ‘ALLSTATS LAST’));

PLAN_TABLE_OUTPUT
—————————————
SQL_ID 0vxgkhh65pf8k, child number 0
————————————-
select /*+ gather_plan_statistics */ ename from emp where sal > 500

Plan hash value: 2872589290
————————————————————————–
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time |
————————————————————————–
|* 1 | TABLE ACCESS FULL| EMP | 1 | 1 | 14 |00:00:00.01 |
————————————————————————–

Using Dynamic Performance View

SQL> alter session set STATISTICS_LEVEL=all;

SQL> select hash_value,sql_id,operation,id,starts,
cardinality as “E-Rows”,last_output_rows as “A-Rows”,cost
from v$sql_plan_statistics_all
where sql_id=’ 0vxgkhh65pf8k’;

HASH_VALUE SQL_ID OPERATION ID STARTS E-Rows A-Rows COST
———- ————- ————– — —— —— —— —-
207272210 0vxgkhh65pf8k TABLE ACCESS 1 1 1 14 2

SOURCE

Leave a Reply