COST calculation of full table scan
SQL > create table test as select * from dba_objects where 1: 0
Table created.
SQL > alter table test pctfree 99 pctused 1
Table altered.
SQL > insert into test select * from dba_objects where rownum alter table test minimize records_per_block
Table altered.
SQL > insert into test select * from dba_objects where rownum'SCOTT'
Tabname = > 'TEST'
Estimate_percent = > 100
Method_opt = > 'for all columns size 1'
Degree = > DBMS_STATS.AUTO_DEGREE
Cascade = > TRUE)
END
/
PL/SQL procedure successfully completed.
SQL > select owner,blocks from dba_tables where owner='SCOTT' and table_name='TEST'
OWNER
-
BLOCKS
-
SCOTT
one thousand
SQL > alter system set db_file_multiblock_read_count=16
System altered.
SQL > set autot trace
SQL > select count (*) from test
Execution Plan
Plan hash value: 1950795681
-
| | Id | Operation | Name | Rows | Cost (% CPU) | Time |
-
| | 0 | SELECT STATEMENT | | 1 | 220 (0) | 00:00:01 |
| | 1 | SORT AGGREGATE | | 1 |
| | 2 | TABLE ACCESS FULL | TEST | 1000 | 220 (0) | 00:00:01 |
-
Statistics
38 recursive calls
0 db block gets
1043 consistent gets
0 physical reads
0 redo size
542 bytes sent via SQL*Net to client
552 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
5 sorts (memory)
0 sorts (disk)
1 rows processed
Full table scan cost:
Cost = (
# SRds * sreadtim +
# MRds * mreadtim +
CPUCycles / cpuspeed
) / sreadtime
# SRds-number of single block reads single block read times
# MRds-number of multi block reads multi-block reads
# CPUCyles-number of number of CPU cycles CPU clock cycles
Sreadtim-single block read time single block read time (in milliseconds milliseconds, 1000 milliseconds equals 1 second
Time for single block read = seek addressing + time to read a block to memory
SQL > select pname, pval1 from sys.aux_stats$ where sname='SYSSTATS_MAIN'
PNAME PVAL1
CPUSPEED
CPUSPEEDNW 3308.9701
IOSEEKTIM 10
IOTFRSPEED 4096
MAXTHR
MBRC
MREADTIM
SLAVETHR
SREADTIM
9 rows selected.
Time for reading in a single block:
Sreadtim=ioseektim+db_block_size/iotfrspeed=10+9=8192byte/4096=12
Select (select pval1 from sys.aux_stats$ where pname = 'IOSEEKTIM') +
(select value from v$parameter where name = 'db_block_size') /
(select pval1 from sys.aux_stats$ where pname = 'IOTFRSPEED') "sreadtim" from dual
Multi-block read: 10 + 16*8k/4k=42
Select (select pval1 from sys.aux_stats$ where pname = 'IOSEEKTIM') +
(select value
From v$parameter
Where name = 'db_file_multiblock_read_count') *
(select value from v$parameter where name = 'db_block_size') /
(select pval1 from sys.aux_stats$ where pname = 'IOTFRSPEED') "mreadtim"
From dual
Cpuspeed-CPU cycles per second CPU frequency (in MHZ)
# CPUCyles-number of number of CPU cycles CPU clock cycles
# CPUCyles-number of number of CPU cycles CPU clock cycles
Explain plan for select count (*) from test
SQL > select cpu_cost from plan_table
CPU_COST
-
7271440
7271440
Cost value:
SQL > select ceil ((1000 amp 16'42 '7271440) / 12) from dual
CEIL ((1000Unix 162x7271440) / 12)
-
two hundred and nineteen
