Home » SQL & PL/SQL » SQL & PL/SQL » How to read correctly the execution plan in oracle, both in tree and tabular mode?
icon5.gif  How to read correctly the execution plan in oracle, both in tree and tabular mode? [message #672166] Thu, 04 October 2018 20:03 Go to next message
TheMatrix
Messages: 4
Registered: September 2018
Location: France
Junior Member
Hello,

Environment:
OS: CentOS 7 X86_64     
Oracle version: Oracle 18c Oracle Database 18c Enterprise Edition 
Release 18.0.0.0.0 - Production Version 18.3.0.0.0

Test case: HR sample schema
set pagesize 500
set heading on
column first_name format a30
column last_name format a30
column job_title format a50
column operation format a40
column object_name format a40
column id format 999
set linesize 300
set sqlblanklines on
set serveroutput off
--
--
--
select
    /*+ gather_plan_statistics */
    t1.first_name,
    t1.last_name,
    t2.job_title
from
    hr.employees t1    
        inner join
    hr.jobs t2
    	on (t1.job_id = t2.job_id)
where
    (t1.salary > 10000);
--
--
--
select * from table(dbms_xplan.display_cursor);


Requirement:
Understanding and learning how to read an execution plan correctly based on the
right order in the generated execution plan tree.


Details:
Currently I'm reading about execution plans and how they are evaluated in oracle in
order to learn how to read correcly a given execution plan and understand how oracle
interprets each operation and in what order. I found the following document
(white paper) about the topic:

h_t_t_p_s://www.oracle.com/technetwork/database/bi-datawarehousing/twp-explain-the-explain-plan-052011-393674.pdf

The Oracle Optimizer Explain the Explain Plan

At the page 4 the author says:
Quote:

The tabular representation is a top down, left to right traversal of the execution tree.
When you read a plan tree you should start from the bottom left and work across and then up.

Here is the output of the display_cursor for the above-mentioned test case:
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  cbccsqby5py31, child number 0
-------------------------------------
select /*+ gather_plan_statistics */     t1.first_name,
t1.last_name,     t2.job_title from     hr.employees t1 inner join
hr.jobs t2     on (t1.job_id = t2.job_id) where (t1.salary > 10000)

Plan hash value: 303035560

------------------------------------------------------------------------------------------
| Id  | Operation                    | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |           |       |       |     6 (100)|          |
|   1 |  MERGE JOIN                  |           |    14 |   770 |     6  (17)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID| JOBS      |    19 |   513 |     2   (0)| 00:00:01 |
|   3 |    INDEX FULL SCAN           | JOB_ID_PK |    19 |       |     1   (0)| 00:00:01 |
|*  4 |   SORT JOIN                  |           |    14 |   392 |     4  (25)| 00:00:01 |
|*  5 |    TABLE ACCESS FULL         | EMPLOYEES |    14 |   392 |     3   (0)| 00:00:01 |
------------------------------------------------------------------------------------------

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

   4 - access("T1"."JOB_ID"="T2"."JOB_ID")
       filter("T1"."JOB_ID"="T2"."JOB_ID")
   5 - filter("T1"."SALARY">10000)


26 rows selected.

SQL> 


As an exercise, I made the following tree based on the above output and what the
author said in the white paper:
                  (6) SELECT STATEMENT
                             |
		             |
			     |
                             v
		     (5) MERGE JOIN
                        /         \
                       /           \
                      /             \
                     v               v
(2) TABLE ACCESS BY INDEX ROWID      (4) SORT JOIN
                   /                   \
                  /                     \
                 /                       \
                v                         v
(1) INDEX FULL SCAN               (3) TABLE ACCESS FULL


The numbers that I put in round brackets show how I understand the order based on which the
operations in the execution plans are evaluated when I read the output of the display_cursor.
So:

  • First of all INDEX FULL SCAN is evaluated (1)
  • Then TABLE ACCESS BY INDEX ROWID is evaluated (2)
  • Then TABLE ACCESS FULL is evaluated (3)
  • Then SORT JOIN is evaluated (4)
  • Then the MERGE JOIN is evaluated (5)
  • And finally the SELECT STATEMENT is evaluated (6)

So my question is: Is this tree correct and according to the six steps that
I wrote above, did I understand what the author said correctly? That is,

Algorithm for tree mode:
I have to start from the left-most leaf in the tree, evaluating each child at the same level (siblings)
from left to right, once there is no more child, go up to evaluate the parent node and repeat the very
same procedure until I finally reach the root of the tree where the whole query will have been evaluated?



And If the above (= my understanding of the algorithm) is correct, then is this translation of the very same
algorithm in the following way is correct when reading the execution plan in tabular mode (= display_cursor's output)?

Algorithm for tabular mode:
Find the right-most indented operation. If there are several on the same right-most level, start with the first
one on the top, evaluate it and then evaluate its parent, then repeat the same for the following
right-most operations until the first line of the output (= the root of the tree) is evaluated.


Later, I'll show you a query on which I'm currently working for manually generating a display
plan (PLAN_TABLE). But before asking my question about it, which is related to the very same topic, I prefer to
stop here for now, to have your opinions about the above mentioned algorithms, to make sure that at
least, I've understood the concept correctly before going any further.


Thank you very much for your help and your attention.

Regards
Re: How to read correctly the execution plan in oracle, both in tree and tabular mode? [message #672168 is a reply to message #672166] Fri, 05 October 2018 01:29 Go to previous messageGo to next message
John Watson
Messages: 8344
Registered: January 2010
Location: Global Village
Senior Member
Your order is correct.
Re: How to read correctly the execution plan in oracle, both in tree and tabular mode? [message #672182 is a reply to message #672168] Fri, 05 October 2018 07:46 Go to previous messageGo to next message
TheMatrix
Messages: 4
Registered: September 2018
Location: France
Junior Member
Thank you very much for your help and your attention to my question.

As I said earlier, there is also a 2nd part in my question: I'm working on a query
that simulates parts of the output of dbms_xplan.display_plan. It's just an
exercise for learning and understanding how the information is organized by
oracle. Also I wish to include the order numbers that I indicated above
in the tree (numbers that I put in round brackets in my previous comment beside the
tree nodes). Actually for long and big plans it can somewhat facilitate reading the plan.


So far, this is what I have written (The column named eval_order is the order that I'm talking about):
set pagesize 500
set heading on
column first_name format a30
column last_name format a30
column job_title format a50
column operation format a40
column object_name format a40
column id format 999
column rn format 999
column eval_order format 99
set linesize 300
set sqlblanklines on
set serveroutput off
--
--
--
truncate table plan_table;
--
--
--
explain plan for
select
    /*+ gather_plan_statistics */
    t1.first_name,
    t1.last_name,
    t2.job_title
from
    hr.employees t1    
	inner join
    hr.jobs t2
    	on (t1.job_id = t2.job_id)
where
    (t1.salary > 10000);
--
--
--
select
    t2.id,
    t2.parent_id,
    t2.depth,
    row_number() over (order by t2.depth desc) as eval_order,
    t2.operation
from
(
    select
        t1.id,
        t1.parent_id,
        t1.depth,
        rownum as rn,
        lpad
        (
            t1.operation || ' ' || t1.options,
            t1.depth + length(t1.operation || ' ' || t1.options),
            ' '
        ) as operation
    from
        plan_table t1
    connect by
        prior t1.id = t1.parent_id
    start with
        t1.parent_id is null
    order siblings by
        t1.id asc
) t2
where
    t2.rn <= (select count(*) from plan_table)
order by
    t2.rn asc;
--
--
--


And the output is:
Table truncated.


Explained.


  ID  PARENT_ID      DEPTH EVAL_ORDER OPERATION
---- ---------- ---------- ---------- ----------------------------------------
   0                     0          6 SELECT STATEMENT
   1          0          1          5  MERGE JOIN
   2          1          2          3   TABLE ACCESS BY INDEX ROWID
   3          2          3          1    INDEX FULL SCAN
   4          1          2          4   SORT JOIN
   5          4          3          2    TABLE ACCESS FULL

6 rows selected.

SQL> 


Unfortunately, my query is wrong. I don't know how to make the analytic function row_number,
assign numbers correcly, that is, after numbering siblings go up to the parent instead of continue
traversing the tree on the same level.

If in your opinion, other parts of my query are correct, could you kindly give me some hint about how to modify my query so
that I might be able to obtain the correct sequence of numbers illustrating the order of the
execution?


Thanks in advance,
Re: How to read correctly the execution plan in oracle, both in tree and tabular mode? [message #672183 is a reply to message #672182] Fri, 05 October 2018 09:21 Go to previous messageGo to next message
John Watson
Messages: 8344
Registered: January 2010
Location: Global Village
Senior Member
I suspect that what you wish to do is impossible, in part because the algorithms you describe are too simplistic. How, for example, would you apply them to this:
pdby1> select /*+ no_query_transformation */ dname,(select sum(sal) from emp where emp.deptno=dept.deptno) from dept;

DNAME          (SELECTSUM(SAL)FROMEMPWHEREEMP.DEPTNO=DEPT.DEPTNO)
-------------- --------------------------------------------------
ACCOUNTING                                                   8750
RESEARCH                                                    10875
SALES                                                        9400
OPERATIONS


Execution Plan
----------------------------------------------------------
Plan hash value: 2018188441

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     4 |    52 |     9   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE    |      |     1 |     7 |            |          |
|*  2 |   TABLE ACCESS FULL| EMP  |     5 |    35 |     3   (0)| 00:00:01 |
|   3 |  TABLE ACCESS FULL | DEPT |     4 |    52 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------

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

   2 - filter("EMP"."DEPTNO"=:B1)

pdby1>

[Updated on: Fri, 05 October 2018 09:21]

Report message to a moderator

Re: How to read correctly the execution plan in oracle, both in tree and tabular mode? [message #672187 is a reply to message #672183] Fri, 05 October 2018 14:53 Go to previous messageGo to next message
TheMatrix
Messages: 4
Registered: September 2018
Location: France
Junior Member
Sorry, I'm not sure to have understood your point. When you say :


Quote:
... in part because the algorithms you describe are too simplistic ...

Are you talking about the query that I posted above or are you
talking about the algorithms (both tabular and tree mode) in my
previous comment last night, that I posted to ask about how to read
correctly a query plan? You mean the latter is wrong?
Re: How to read correctly the execution plan in oracle, both in tree and tabular mode? [message #672190 is a reply to message #672187] Sat, 06 October 2018 01:35 Go to previous messageGo to next message
John Watson
Messages: 8344
Registered: January 2010
Location: Global Village
Senior Member
So have you applied your algorithms to the plan I posted?
Re: How to read correctly the execution plan in oracle, both in tree and tabular mode? [message #672194 is a reply to message #672190] Sat, 06 October 2018 07:49 Go to previous message
TheMatrix
Messages: 4
Registered: September 2018
Location: France
Junior Member
I think I might have understood what you mean (please correct me if I'm wrong)


If I apply my algorithm literally to your example, I have to conclude like this:


- First, the TABLE ACCESS FULL (id = 2) is evaluated

- Then the operation SORT AGGREGATE (id = 1) is evaluated (sum(salary))

- Then the operation TABLE ACCESS FULL (id = 3) is evaluated

- And finally the SELECT STATEMENT (id = 0) is evaluated


However this is wrong, because this is a scalar subquery and therefore SORT AGGREGATE
(sum(salary)) is evaluated once for each row of the outer query (each row of the
department table). So my algorithm is wrong in a sense that it assumes that
the execution and the evaluation of each operation shown in an execution plan,
is always sequential, whereas as, the example above shows, this can indeed be simultaneously
and in parallel.

So yes, in order to start reading an execution plan, we should start looking for the leafs
(rigth-most indented operations and the top-most one if the are several of them)
but then
all depends on the context and the type of the operations that oracle would decide
how exactly and in what order these operatios would be evaluated.

And therefore trying to find this order by a simple SQL query which I tried to
do above, is meaninggless and impossible.

Was that your point?
Previous Topic: If the sting value more then 3 separated need to split into 2nd line
Next Topic: HELP NEEDED IN COMPILING PACKAGE
Goto Forum:
  


Current Time: Wed Sep 23 02:46:49 CDT 2020