H.Tonguç YILMAZ Blog

This is a demostration of what is mentioned on this post of Mr.Lewis; http://jonathanlewis.wordpress.com/2006/12/11/cost-is-time/
First you must start using system statistics as mentioned in my previous post.

SELECT pname, pval1 FROM sys.aux_stats$ WHERE sname = ‘SYSSTATS_MAIN’;

PNAME PVAL1
—————————— ———-
CPUSPEEDNW 491,366552
IOSEEKTIM 10
IOTFRSPEED 4096
SREADTIM 10,204
MREADTIM 12,883
CPUSPEED 236
MBRC 28
MAXTHR 6822912
SLAVETHR

9 rows selected.

execute dbms_random.seed(0)

drop table t1 purge;
create table t1 as select
rownum id,
trunc(dbms_random.value(1,50)) n1,
rpad(’x’,100) padding
from dba_source ;
create unique index ui_t1_id on t1(id);

exec dbms_stats.gather_table_stats(user,tabname =>’T1′,cascade => true,estimate_percent => null,granularity => ‘default’,method_opt => ‘for all columns size 1′ );

alter system flush shared_pool ;

explain plan set statement_id ‘xyz’ for select n1, padding from t1 where id > 100;

select * from table(dbms_xplan.display(’plan_table’,’xyz’));

————————————————————————–
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
————————————————————————–
| 0 | SELECT STATEMENT | | 154K| 15M| 137 (17)| 00:00:02 |
|* 1 | TABLE ACCESS FULL| T1 | 154K| 15M| 137 (17)| 00:00:02 |
————————————————————————–

cost : 137
SREADTIM : 10.204
time : 2

in the post time is mentioned to be calculated as -> round(cost * SREADTIM / 1000, 3) and ;

select round(137 * 10.204 / 1000, 3) time from dual ;

TIME
———-
1,398

Mr.Lewis explains this difference between 2 and 1,398 as; “Oracle got as close as it could because it’s reporting to the second - and it’s rounding up, rather than rounding to the nearest second.”

References

http://tonguc.oracleturk.org/index.php?s=cost+estimated+time