<quote src = Effective Oracle by Design>

How to Read a Query Plan
Often, I am asked this question: “How exactly do we read a query plan?” Here, I will present my approach to reading the plan. We’ll take a look at a query plan resulting from a query against the SCOTT/TIGER tables. Note that I have added primary keys to the EMP and DEPT tables, so they are indexed.

scott@ORA920> delete from plan_table;
7 rows deleted.

scott@ORA920> explain plan for
  2  select ename, dname, grade
  3    from emp, dept, salgrade
  4   where emp.deptno = dept.deptno
  5     and emp.sal between salgrade.losal and salgrade.hisal
  6  /
Explained.

scott@ORA920> @?/rdbms/admin/utlxpls

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------
| Id  | Operation                    |Name     |Rows|Bytes|Cost |
-----------------------------------------------------------------
|   0 | SELECT STATEMENT             |         |    |     |     |
|   1 |  NESTED LOOPS                |         |    |     |     |
|   2 |   NESTED LOOPS               |         |    |     |     |
|   3 |    TABLE ACCESS FULL         | SALGRADE|    |     |     |
|*  4 |    TABLE ACCESS FULL         | EMP     |    |     |     |
|   5 |   TABLE ACCESS BY INDEX ROWID| DEPT    |    |     |     |
|*  6 |    INDEX UNIQUE SCAN         | DEPT_PK |    |     |     |
-----------------------------------------------------------------

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

   4 - filter("EMP"."SAL"<="SALGRADE"."HISAL" AND
              "EMP"."SAL">="SALGRADE"."LOSAL")
   6 - access("EMP"."DEPTNO"="DEPT"."DEPTNO")

Note: rule based optimization

21 rows selected.

How can we figure out what happens first, second, and so on? How does that plan actually get evaluated? First, I will show you the pseudo code for evaluation of the plan, and then we will discuss how I arrived at this conclusion.

For salgrade in (select * from salgrade)
Loop
   For emp in ( select * from emp )
   Loop
      If ( emp.sal between salgrade.losal and salgrade.hisal )
      Then
          Select * into dept_rec
            From dept
           Where dept.deptno = emp.deptno;

          OUTPUT RECORD with fields from salgrade,emp,dept
      End if;
   End loop;
End loop;

The way I read this plan is by turning it into a graph of sorts—an evaluation tree. In order to do this, you need to understand access paths. For detailed information on all of the access paths available to Oracle, see the Oracle Performance and Tuning Guide. There are quite a few access paths, and the descriptions in the guide are quite comprehensive.
To build the tree, we can start from the top, with step 1, which will be our root node in the tree. Next, we need to find the things that feed this root node. That will be accomplished in steps 2 and 5, which are at the same level of indentation, because they feed into step 1. Continuing, we can see that steps 3 and 4 feed step 2, and that step 6 feeds step 5. Putting it together iteratively, we can draw this evaluation tree:
Illustration 1
Reading the tree, we see that in order to get step 1, we need steps 2 and 5; step 2 comes first. In order to get step 2, we need steps 3 and 4; step 3 comes first. That is how we arrive at the pseudo code:

For salgrade in (select * from salgrade)
Loop
   For emp in ( select * from emp )
   Loop

The full scan of the SALGRADE table is step 3. The full scan of the EMP table is step 4. Step 2 is a nested loop, which is roughly equivalent to two FOR loops. Once we evaluate step 2 like that, we can look at step 5. Step 5 runs step 6 first. Step 6 is the index-scan step. We are taking the output of step 2 and using that to feed this part of the query plan. So, the output from step 2 is used to perform an index scan. Then that index scan output is used to access the DEPT table by ROWID. That result is the output of step 1, our result set.
Now, to make this interesting, we will run an equivalent query, but we will mix up the order of the tables in the FROM clause this time. Since I am using the rule-based optimizer (RBO), this will affect the generated query plan. (This is just one reason why you don’t want to use the RBO; we will cover some more reasons in the “Understand the AUTOTRACE Output” section later in this chapter.) The RBO is sensitive to the order of tables in the FROM clause and will use the order in which we enter them to choose a “driving table” for the query if none of the predicates do so (in the event of a “tie”, the RBO will look to the order the developer typed in table names in order to pick what table to use first!) We will use the same logic to build its query plan tree and evaluate how it processes the query.

scott@ORA920> delete from plan_table;
7 rows deleted.

scott@ORA920> explain plan for
  2  select ename, dname, grade
  3    from salgrade, dept, emp
  4   where emp.deptno = dept.deptno
  5     and emp.sal between salgrade.losal and salgrade.hisal
  6  /
Explained.

scott@ORA920> @?/rdbms/admin/utlxpls

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------
| Id  | Operation                     |  Name       | Rows  | Bytes
-------------------------------------------------------------------
|   0 | SELECT STATEMENT              |             |       |      
|   1 |  NESTED LOOPS                 |             |       |      
|   2 |   NESTED LOOPS                |             |       |      
|   3 |    TABLE ACCESS FULL          | EMP         |       |       
|   4 |    TABLE ACCESS BY INDEX ROWID| DEPT        |       |       
|*  5 |     INDEX UNIQUE SCAN         | DEPT_PK     |       |       
|*  6 |   TABLE ACCESS FULL           | SALGRADE    |       |       
-------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   5 - access("EMP"."DEPTNO"="DEPT"."DEPTNO")
   6 - filter("EMP"."SAL"<="SALGRADE"."HISAL" AND 
              "EMP"."SAL">="SALGRADE"."LOSAL")
Note: rule based optimization

21 rows selected.

Here, we see that steps 2 and 6 feed step 1, steps 3 and 4 feed step 2, and step 5 feeds step 4. The evaluation tree looks like this:
Illustration 2
So, starting with steps 3 and 4, the pseudo code logic here is:

For emp in ( select * from emp )
Loop
   -- using the index
   Select * from dept where dept.deptno = emp.deptno

   For salgrade in (select * from salgrade )
   Loop
      If ( emp.sal between salgrade.losal and salgrade.hisal )
      Then
         OUTPUT RECORD;
      End if;
   End loop
End loop;

And that is it. If you draw a graphical tree, and then read it from the bottom up, left to right, you will get a good understanding of the flow of the data.
</quote>

you already seem to know?

... and it's cumulative for each step ... .

the top of the row source, the thing highest in the tree, is the sum - the total - of everything.

If that has two children, each of them is the sum of their independent children - and their sum should be close to the value associated with their parent.


but - since the numbers are computed at different levels, do not be surprised if they do not add up PRECISELY.


for example, take a query like:

select *
  from emp, dept
 where emp.deptno = dept.deptno
   and dept.deptno = 10



Rows     Row Source Operation
-------  ---------------------------------------------------
      3  NESTED LOOPS  (cr=6 pr=0 pw=0 time=65 us)
      1   TABLE ACCESS BY INDEX ROWID DEPT (cr=2 pr=0 pw=0 time=33 us)
      1    INDEX UNIQUE SCAN DEPT_PK (cr=1 pr=0 pw=0 time=14 us)(object id 62111)
      3   TABLE ACCESS BY INDEX ROWID EMP (cr=4 pr=0 pw=0 time=25 us)
      3    INDEX RANGE SCAN EMP_DEPTNO_IDX (cr=2 pr=0 pw=0 time=27 us)(object id 62110)

procedurally, that would be like:


cursor c1 is select * from dept where deptno = 10;
cursor c2(p_deptno) is select * from emp where deptno = p_deptno;

open c1;
loop
    fetch c1 into l_dept_rec;
    exit when c1%notfound;
    open c2(l_dept_rec.deptno);
    loop
        fetch c2 into l_emp_rec;
        exit when c2%notfound;
        output C1+C2;
    end loop;
    close c2;
end loop;
close c1;

Now, the timing of each thing would be done like this conceptually:

total_c2_time := 0;
total_start_time := gettime();

c1_start_time := gettime();
open c1;
total_c1_time := gettime() - c1_start_time;

loop

    c1_start_time := gettime();
    fetch c1 into l_dept_rec;
    total_c1_time := total_c1_time + (gettime()-c1_start_time)

    exit when c1%notfound;

    c2_start_time := gettime();
    open c2(l_dept_rec.deptno);
    total_c2_time := total_c2_time + (gettime()-c2_start_time);

    loop
        c2_start_time := gettime();
        fetch c2 into l_emp_rec;
        total_c2_time := total_c2_time + (gettime()-c2_start_time);

        exit when c2%notfound;
        output C1+C2;
    end loop;

    c2_start_time := gettime();
    close c2;
    total_c2_time := total_c2_time + (gettime()-c2_start_time);

end loop;

c1_start_time := gettime();
close c1;
total_c1_time := total_c1_time + (gettime()-c1_start_time)

total_elapsed := gettime() - total_start_time;

Now, the total_elapsed time will be the MOST ACCURATE (because time is analog, not digital). Each of the C1/C2 observations could be off by plus or minus one unit of time each (they under or over count). If you loop a lot - these times could be off by "a lot" (randomness helps here, you will usually almost zero out any error).