Saturday, May 31, 2008

How to see Explain Plan from SQL*Plus

Method 1: Autotrace Facility

When the AUTOTRACE setting of SQL*Plus is enabled, SQL*Plus will print an EXPLAIN PLAN and execution statistics after each SQL statement.
Here is an example, where AUTOT is abbreviation of AUTOTRACE

SQL>SET AUTOT ON
SQL>SELECT * FROM TEST WHERE A=1;


A B
---------- ----------
1 USA


Execution Plan
----------------------------------------------------------
Plan hash value: 2109870067

--------------------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
Time |

--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 8 | 1 (0)|
00:00:01 |

| 1 | TABLE ACCESS BY INDEX ROWID| TEST | 1 | 8 | 1 (0)|
00:00:01 |

|* 2 | INDEX UNIQUE SCAN | SYS_C005994 | 1 | | 0 (0)|
00:00:01 |

-------------------------------------------------------------------------------


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

2 - access("A"=1)


Statistics
----------------------------------------------------------
147 recursive calls
0 db block gets
26 consistent gets
6 physical reads
0 redo size
439 bytes sent via SQL*Net to client
458 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

To trace the same statement without displaying the query data, enter:

SQL>SET AUTOTRACE TRACEONLY

To off trace issue,
SET AUTOT OFF
Method 2: DBMS_XPLAN Package
SQL>EXPLAIN PLAN FOR select * from test where a=1;
Explained.

SQL>set linesize 140
SQL>SELECT * FROM TABLE( dbms_xplan.display);


PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------
Plan hash value: 2109870067

----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 8 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST | 1 | 8 | 1 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | SYS_C005994 | 1 | | 0 (0)| 00:00:01 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

PLAN_TABLE_OUTPUT
-----------------------------------------------------

2 - access("A"=1)

14 rows selected.

No comments:

Post a Comment