I usually use SQL*Plus’s AUTOTRACE option for a quick understanding of some query’s execution steps. But with DBMS_XPLAN’s formatting options someone can find much more detailed information which may shorten a tuning workshop.
set serveroutput off
set linesize 2500
set autotrace traceonly explain
select /*+ gather_plan_statistics */
first_name, salary, department_name
from employees e, departments d
where e.department_id = d.department_id
and d.department_name like 'A%' ;
Execution Plan
----------------------------------------------------------
Plan hash value: 1021246405
--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10 | 300 | 4 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | | | | |
| 2 | NESTED LOOPS | | 10 | 300 | 4 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL | DEPARTMENTS | 1 | 16 | 3 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | EMP_DEPARTMENT_IX | 10 | | 0 (0)| 00:00:01 |
| 5 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 10 | 140 | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("D"."DEPARTMENT_NAME" LIKE 'A%')
4 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")
set autot off
select /*+ gather_plan_statistics */
first_name, salary, department_name
from employees e, departments d
where e.department_id = d.department_id
and d.department_name like 'A%' ;
select * from table(dbms_xplan.display_cursor(null, null, 'TYPICAL IOSTATS LAST')) ;
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 134hnsg0n3tr6, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ first_name, salary,
department_name from employees e, departments d where
e.department_id = d.department_id and d.department_name like 'A%'
Plan hash value: 1021246405
-----------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | Reads |
-----------------------------------------------------------------------------------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 1 | NESTED LOOPS | | 1 | | | | | 3 |00:00:00.08 | 13 | 8 |
| 2 | NESTED LOOPS | | 1 | 10 | 300 | 4 (0)| 00:00:01 | 3 |00:00:00.08 | 11 | 7 |
|* 3 | TABLE ACCESS FULL | DEPARTMENTS | 1 | 1 | 16 | 3 (0)| 00:00:01 | 2 |00:00:00.06 | 8 | 6 |
|* 4 | INDEX RANGE SCAN | EMP_DEPARTMENT_IX | 2 | 10 | | 0 (0)| | 3 |00:00:00.02 | 3 | 1 |
| 5 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 3 | 10 | 140 | 1 (0)| 00:00:01 | 3 |00:00:00.01 | 2 | 1 |
-----------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("D"."DEPARTMENT_NAME" LIKE 'A%')
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
4 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")
24 rows selected.
In above example I used GATHER_PLAN_STATISTICS hint to force row source execution statistics(check V$STATISTICS_LEVEL) generation, the alternative is to set STATISTICS_LEVEL parameter to ALL at session level, as a result now you have very important additional information compared to the standard AUTOTRACE output, like;
– “A-Rows” – the number of rows actually produced by the corresponding row source(remember the ‘tuning by cardinality‘ paper by Mr.Wolfgang Brietling)
– “Buffers” – the number of consistent reads done by the row source
– “Starts” – indicating how many times the corresponding operation was repeated
Unfortunately in my opinion information on these options are limited in the documentation here, so for more detailed explanation reading Mr.Jonathan Lewis’s post here may be good starting point.
Extract(Read) > Transform(Test&Apply) > Load(Learn) Blog 