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