Skip to Main Content

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Amit.

Asked: November 05, 2012 - 1:14 pm UTC

Last updated: November 05, 2012 - 2:53 pm UTC

Version: 10.2.1

Viewed 1000+ times

You Asked

HI Tom,

Can you please tell me how can i use the explain plan for query optimization.
There are lot's of parameters in it like cardinality,cost etc with labels like hash join , merge join etc.
Could you plz elaborate on these parameters and labels.

Thanks,
Amit D.

and Tom said...

see http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1590579200346924503#1998166800346592594


cardinality/rows is just the estimate of how many rows will flow through a step in a plan, for example:

ops$tkyte%ORA11GR2> select * from scott.emp where empno = 1234;

Execution Plan
----------------------------------------------------------
Plan hash value: 3956160932

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |    38 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| EMP  |     1 |    38 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------

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

   1 - filter("EMPNO"=1234)

ops$tkyte%ORA11GR2> select * from scott.emp;

Execution Plan
----------------------------------------------------------
Plan hash value: 3956160932

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |    14 |   532 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| EMP  |    14 |   532 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------



the optimizer guesses that about 1 row will return from the first query and about 14 rows from the second.


Cost is the - well - cost. It is a number computed based on the number of IO's we think we have to perform as well as the cpu needed to process the results.


As for the labels, they are access methods, join methods - names for the operations performed on the data. this chapter covers many of them:

http://docs.oracle.com/cd/E11882_01/server.112/e16638/optimops.htm#i21299





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

More to Explore

Performance

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