Link to home
Start Free TrialLog in
Avatar of gram77
gram77Flag for India

asked on

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)
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

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

ASKER CERTIFIED SOLUTION
Avatar of Sean Stuber
Sean Stuber

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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.
Avatar of gram77

ASKER

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?
"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.
Avatar of gram77

ASKER

Short and Simple, Excellent!