Skip to Main Content
  • Questions
  • tkprof Row Source Operation Vs. Execution Plan

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question.

Asked: March 29, 2009 - 4:41 pm UTC

Last updated: November 15, 2009 - 1:07 pm UTC

Version: 10.2.0.4

Viewed 1000+ times

You Asked

I have the query and sql-traced. The tkprof shows the un-consistance of the Row Source operation and the Execution. I thought they should be the same. Are they are be the same?

Rows Row Source Operation
------- ---------------------------------------------------
648 SORT ORDER BY (cr=9321 pr=0 pw=0 time=149289 us)
648 HASH JOIN (cr=9321 pr=0 pw=0 time=147814 us)
210 TABLE ACCESS FULL HCPEOPLE (cr=16 pr=0 pw=0 time=285 us)
648 HASH JOIN (cr=9305 pr=0 pw=0 time=145890 us)
1072 INDEX FAST FULL SCAN PRXPREVCGRP_IDX1 (cr=5 pr=0 pw=0 time=29 us)(object id 18629)
648 HASH JOIN (cr=9300 pr=0 pw=0 time=144535 us)
648 HASH JOIN (cr=8219 pr=0 pw=0 time=3783086 us)
669 NESTED LOOPS (cr=1392 pr=0 pw=0 time=47701 us)
1 TABLE ACCESS BY INDEX ROWID HCPEOPLE (cr=2 pr=0 pw=0 time=36 us)
1 INDEX UNIQUE SCAN HC32_PRIMARYKEY (cr=1 pr=0 pw=0 time=12 us)(object id 12820)
669 TABLE ACCESS BY INDEX ROWID RELMESSUSERS (cr=1390 pr=0 pw=0 time=47666 us)
10328 INDEX RANGE SCAN RMU_IDU_IDX (cr=7 pr=0 pw=0 time=29 us)(object id 17775)
217847 TABLE ACCESS FULL MESSAGES (cr=6827 pr=0 pw=0 time=65 us)
28595 TABLE ACCESS FULL PATIENTSPRAXIS (cr=1081 pr=0 pw=0 time=39 us)


Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT MODE: ALL_ROWS
648 FILTER
648 SORT (ORDER BY)
210 HASH JOIN
648 HASH JOIN
1072 TABLE ACCESS MODE: ANALYZED (FULL) OF 'HCPEOPLE'
(TABLE)
648 HASH JOIN
648 NESTED LOOPS
669 NESTED LOOPS
1 TABLE ACCESS MODE: ANALYZED (BY INDEX ROWID) OF
'HCPEOPLE' (TABLE)
1 INDEX MODE: ANALYZED (UNIQUE SCAN) OF
'HC32_PRIMARYKEY' (INDEX (UNIQUE))
669 INDEX MODE: ANALYZED (RANGE SCAN) OF
'RELMESSUSERS_IDX' (INDEX)
10328 TABLE ACCESS MODE: ANALYZED (BY INDEX ROWID) OF
'MESSAGES' (TABLE)
217847 INDEX MODE: ANALYZED (UNIQUE SCAN) OF
'ME48_PRIMARYKEY' (INDEX (UNIQUE))
28595 INDEX MODE: ANALYZED (FAST FULL SCAN) OF
'PRXPREVCGRP_IDX1' (INDEX)
0 TABLE ACCESS MODE: ANALYZED (FULL) OF 'PATIENTSPRAXIS'
(TABLE)


and we said...

http://asktom.oracle.com/Misc/when-explanation-doesn-sound-quite.html

Explain plan should not be used with tkprof, it can be extremely misleading.

o explain plan cannot, will not, bind peek

o explain plan thinks all binds are varchar2's, regardless of what you actually bind with.

o explain plan hard parses every time it is run, if your DEFAULT session settings are not what was in place with the query was run, tkprof will see a different plan. eg: if the application issues a 'alter session' to change the optimizer goal to first_rows, but the default is all_rows (or choose, choose = all_rows if it chooses to use the CBO).

o explain plan hard parses - therefore, if you are using dynamic sampling (9i and up enables this by default, 10g even more aggressively), then explain plan's sample will be as of "right now", whereas the tkprof sample will be as of "back then"

o explain plan uses the credentials you provide to parse, that might be different than the parsing user originally, that could make a difference as well.



Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

some help in understanding the plan

jv, September 29, 2009 - 10:08 am UTC

Dear Tom,

I am having some trouble in understanding the output generated using dbms_xplan.display_cursor after executing the query with gather_plan_statistics hint in the query. I was thinking of pasting the output here but it becomes difficult to understand due to limitation on number of characters per line, how do I share the same to get your help on it.

Regards,
JV
Tom Kyte
October 07, 2009 - 6:56 am UTC

<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>

time in row source operation

A reader, November 11, 2009 - 2:58 pm UTC

Hi Sir,

Can you please explain in simple example how time is calculated for row source operation. I know that each step in row source operation there is a time associated with that same as cr,pr and other. This is different for different OS also. and it's cumulative for each step. But how to start reading this step and from where do we need to start. As this is the actual time spent of each operation so that we can evaluate performance. Let's say we have 3 table we join two of the with hash and then result is then compared with last table. So, how to read this time
Tom Kyte
November 15, 2009 - 1:07 pm UTC

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).

More to Explore

Security

All of the vital components for a secure database are covered in the Security guide.