关于对db_block_gets的理解与实验
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.
<
实验
一、 自己手动创建的小表
创建一个区大小为 40kSYS@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.
<