Extract(Read) > Transform(Test&Apply) > Load(Learn) Blog

September 30, 2008

On Formating Treasures of Execution Plan Interpretation

Filed under: Oracle Performance — H.Tonguç Yılmaz @ 4:37 pm

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.

About these ads

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

The Rubric Theme. Blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

Join 81 other followers

%d bloggers like this: