Shulou(Shulou.com)06/01 Report--

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

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.