gram77
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_st ats( 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_st ats( 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)
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_st
PL/SQL procedure successfully completed.
SQL> create index t_idx on t(object_id);
Index created.
SQL> exec dbms_stats.gather_index_st
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)
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
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?
"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.
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.
ASKER
Short and Simple, Excellent!
http://download.oracle.com