Meaning of Cost, Cardinality/Rows,Bytes and time in Explain Plan.

What does the following terms in the Explain Plan mean:
1. Cost
2. Cardinality/Rows
3. Bytes
4. Time
Please give examples.


SQL> create table t as select * from all_objects;
Table created.
 
SQL> exec dbms_stats.gather_table_stats( user, 'T', method_opt=>'for all indexed columns');
PL/SQL procedure successfully completed.
 
SQL> create index t_idx on t(object_id);
Index created.
 
SQL> exec dbms_stats.gather_index_stats( user, 'T_IDX' );
PL/SQL procedure successfully completed.
 
SQL> select * from t where object_id = 42;
Execution Plan
----------------------------------------------------------
Plan hash value: 470836197

-------------------------------------------------------------------------------------
| Id  | Operation                                            | Name  | Rows  | Bytes | Cost (%CPU)| Time        |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                          |          |   557     | 52915 |            9   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID   | T        |   557     | 52915 |            9   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN                       | T_IDX |   223     |            |            1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("OBJECT_ID"=42)

or in 9i
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=2 Card=482 Bytes=44826)
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'T' (TABLE) (Cost=2 Card=482 Bytes=44826)
   2    1     INDEX (RANGE SCAN) OF 'T_IDX' (INDEX) (Cost=1 Card=193)
gram77Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

slightwv (䄆 Netminder) Commented:
The docs can probably explain it better than we can (it's a 9.2 doc link but these metrics really haven't changed much):

http://download.oracle.com/docs/cd/B10500_01/server.920/a96533/ex_plan.htm#22727

0
sdstuberCommented:
1. Cost   - this is an internal number,  many people mistaken believe lower cost = faster.  Ignore this.


2. Cardinality/Rows - this is an estimate by the optimizer of how many rows a particular operation will return

3. Bytes  - this is an estimate by the optimizer of how many bytes a particular operation will return

4. Time - this is an estimate of how long, in seconds, a particular operation will return
1

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
sdstuberCommented:
if you tkprof a tracefile the row source operations (not the explain plan) will contain the actual values of execution.  Using the EXPLAIN option in tkprof adds little to no value.
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

gram77Author Commented:
sdstuber:
"many people mistaken believe lower cost = faster.  Ignore this"

If cost is not a proof for faster execution of a query what is a proof of a query tuned in the explain plan?
0
sdstuberCommented:
"what is a proof"?

In a certain sense, the plan will never constitute a "proof".

The real "proof" comes from running the query and measuring the actual time and resource consumption (cpu,memory,io, locks, etc)

A plan is always an estimate. It's never guaranteed to be completely accurate. Even if you have perfect stats and constraints that provide 100% information, the plan might be accurate exactly then but as soon as you modify the data the plan is now subject to error. So it will never be a hard "proof"

but, you can generally tell a superior plan by

looking at rows and bytes processed and minimize these.
if two plans appear to do the same io, then look at the number of steps, particularly sorts for memory/cpu consumption.

the best plan is the lazy plan. If plan A does more work than plan B. Then plan B will scale better.
0
gram77Author Commented:
Short and Simple, Excellent!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.