mysql block size_关于db_block_size的理解和实验

关于对db_block_gets的理解与实验 实验 一、 自己手动创建的小表 创建一个区大小为 40k SYS@ORCLshow parameter db_block_size NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_block_size integer 819

关于对db_block_gets的理解与实验

实验

一、 自己手动创建的小表

创建一个区大小为 40k

SYS@ORCL>show parameter db_block_size

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

db_block_size integer 8192

SYS@ORCL>create tablespace tyger1 datafile '/u01/app/oracle/oradata/ORCL/tyger1.dbf' size 10m

2 extent management local uniform size 40k;

Tablespace created.

SYS@ORCL>create table test_db1(x int) tablespace tyger1;

Table created.

SYS@ORCL>set autotrace on

SYS@ORCL>insert into test_db1 values(1);

1 row created.

Execution Plan

----------------------------------------------------------

-------------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

-------------------------------------------------------------------------

| 0 | INSERT STATEMENT | | 1 | 100 | 1 (0)| 00:00:01 |

-------------------------------------------------------------------------

Statistics

----------------------------------------------------------

1 recursive calls

19 db block gets

1 consistent gets

3 physical reads

964 redo size

675 bytes sent via SQL*Net to client

562 bytes received via SQL*Net from client

4 SQL*Net roundtrips to/from client

1 sorts (memory)

0 sorts (disk)

1 rows processed

SYS@ORCL>insert into test_db1 values(2);

1 row created.

Execution Plan

----------------------------------------------------------

-------------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

-------------------------------------------------------------------------

| 0 | INSERT STATEMENT | | 1 | 100 | 1 (0)| 00:00:01 |

-------------------------------------------------------------------------

Statistics

----------------------------------------------------------

1 recursive calls

3 db block gets

1 consistent gets

0 physical reads

244 redo size

675 bytes sent via SQL*Net to client

562 bytes received via SQL*Net from client

4 SQL*Net roundtrips to/from client

1 sorts (memory)

0 sorts (disk)

1 rows processed

2. 创建一个区 大小为80k

SYS@ORCL>create tablespace tyger2 datafile '/u01/app/oracle/oradata/ORCL/tyger2.dbf' size 10m

2 extent