Thanks for the question, Rajeshwaran.

Asked: January 22, 2009 - 9:39 am UTC

Last updated: February 25, 2013 - 11:23 am UTC

Version: 10.1.0

Viewed 10K+ times! This question is

You Asked

Tom,

CREATE TABLE T (x NUMBER,y VARCHAR2(4000))

INSERT INTO T(x,y) 
SELECT LEVEL, RPAD('*',LEVEL,'*')
FROM dual
CONNECT BY LEVEL <= 3999

scott@ORCL> EXPLAIN PLAN FOR SELECT * FROM T WHERE X = 859;

Explained.
scott@ORCL> SELECT * FROM TABLE (dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 749696591

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |   162 |   318K|  7980   (1)| 00:01:36 |
|*  1 |  TABLE ACCESS FULL| T    |   162 |   318K|  7980   (1)| 00:01:36 |
--------------------------------------------------------------------------

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

   1 - filter("X"=859)

Note
-----
   - dynamic sampling used for this statement

17 rows selected.



SELECT * 
FROM  T WHERE X = 859


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.09          0          1          0           0
Execute      1      0.00       0.01          0          0          0           0
Fetch        2      0.54       6.99      35214      36375          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.54       7.11      35214      36376          0           1

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 57  

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  TABLE ACCESS FULL T (cr=36375 pr=35214 pw=0 time=5994187 us)


Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                       2        0.00          0.00
  db file scattered read                       2226        0.15          6.09
  db file sequential read                        21        0.05          0.12
  SQL*Net message from client                     2        0.05          0.05
********************************************************************************

1) The Time format in the explain plan is HH:MI:SS format ( Please correct me if i am wrong).
2) The same query when used with explain plan it takes 1 minute, 36 seconds while in TKPROF it takes 7 seconds. 
   So which value is correct the one in EXPLAIN Plan or TKPROF? and which needs to be considered for Tuning a query.
3) Can you please tell me what is Time format in the explain plan denotes?

Thanks,
Rajesh.

and Tom said...

1) correct
2) explain plan, which is totally guessing, estimated it might take 1 minute, 36 seconds to execute this query. In reality - it took 7.11 seconds

explain plan also guessed "162" rows, there was only 1.

explain plan is the optimizers guess as to what will happen. It will be heavily impacted by how many IO's where physical IO's, how fast they were read in, how many other users are on the system and so on.

You did 35k physical IO's with an average response of about .000170387 seconds - that is pretty fast. Probably, it was in the file system cache or a SAN cache.

if you ask me, real world performance is the only thing you can 'tune to'. If I told you "it will take me 5 weeks to write this software for you, I charge $x.xx per hour" and in reality it took me two weeks - would you pay me for five weeks or two weeks?

Everything about explain plan is a guess, it is the optimizers guess as to what will happen

Rating

(5 ratings)

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

Comments

TIME in EXPLAIN Plan Vs Elapsed time in TKROF

Rajeshwaran Jeyabal, January 22, 2009 - 3:16 pm UTC

1) So, While Tuning it is better to make use of the Elapsed time value from the TKPROF rather than using the Time from Explain plan.

2) Also the number of rows returned by the query is correctly reflected in the TKPROF Output than the Explain plan output.

3) Tom, If you look at the explain plan from the TKPROF output time=5994187 us . what is this 'us' Tom? is it matches with the 7.11

January 22, 2009 - 4:00 pm UTC

1) think about it.... I tried to say really emphatically "explain plan = guess, reality = real". Which would you use - an estimate or reality - when writing a check.

2) in the row source operation component of a tkprof, that is what happened. You can also have an explain plan in a tkprof report, that is a guess as to what might happen.

3) it is our attempt to write microsecond - the fancy u that leans to the right
http://www.simetric.co.uk/si_time.htm
has it.

But it isn't a U

Stephan Uzzell, January 22, 2009 - 4:28 pm UTC

http://en.wikipedia.org/wiki/Mu_(letter )

It is the Greek letter mu (their version of M)

January 22, 2009 - 5:37 pm UTC

I know, it is our ascii art attempt at it

tkprof

A reader, January 10, 2010 - 10:03 pm UTC

Why would you use explain plan?

A reader, May 01, 2012 - 12:17 am UTC

>From Tom
>explain plan is the optimizers guess as to what will >happen

So why would you ever use it?
I would rather find out what the optimizer is ACTUALLY going to do

May 01, 2012 - 3:39 pm UTC

the only way to do that is to actually run it.

An explain plan is getting less and less useful. Once upon a time ago, it was the *only thing* we didn't have v$sql_plan.

Now, I would prefer everyone to post something like this:

http://jonathanlewis.wordpress.com/2006/11/09/dbms_xplan-in-10g/

using gather_plan-statistics and dbms_xplan - then I could stop asking for

a) autotrace traceonly explain
PLUS
b) a tkprof with the row source operation section

Understand rows and byte in explain plan

Spur, February 22, 2013 - 1:07 pm UTC

In following explain plain what does rows 1K mean . Does it mean 1024 rows or 1000 rows or something else.

Also , How can we use bytes for? Can we estimate temp size / undo size from it.

--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1K | 318K| 7980 (1)| 00:01:36 |
|* 1 | TABLE ACCESS FULL| T | 1K | 318K| 7980 (1)| 00:01:36 |
--------------------------------------------------------------------------

February 25, 2013 - 11:23 am UTC

it means roughly 1K - 1024, it doesn't really matter, 1024 and 1000 are basically the "same" as far as the optimizer would be concerned.

if temp space were required, the plan would show temp space as a column. You can use bytes to understand the magnitude of the data flowing from step to step.

More to Explore

Performance

Get all the information about database performance in the Database Performance guide.

Breadcrumb

Question and Answer