关于db_block_size的理解和实验

关于对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 management local uniform size 80k;


Tablespace created.

<