db block gets:Number of times a CURRENT block was requested.
意为处于当前状态的块被需求的块次
这应为一个与磁盘相关的概念而不是内存相关的概念,即是需求这个物理块的当前状态。
下面我们用实验验证一下
在一张空表上插入一条新数据,此时oracle会分配一个extent,这分配extent都应是db_block_gets
SYS@PROD>set autot traceonly;
SYS@PROD>create tablespace test1
datafile'/u01/app/oracle/oradata/PROD/test1.dbf' size 10m
extent management local uniform size 40k;
2 3 create tablespace test
Tablespace created.
Note: Uniform size for auto segment space managed tablespace should have atleast 5 blocks
SYS@PROD>create table t1(x int)tablespace test1;
Table created.
SYS@PROD>insert into t1 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
|
| 1 | LOAD TABLE CONVENTIONAL | T1 | | | |
|
--------------------------------------------------------------------------------
-
Statistics
----------------------------------------------------------
1 recursive calls
26 db block gets
2 consistent gets
0 physical reads
1456 redo size
842 bytes sent via SQL*Net to client
780 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
1 rows processed
与我们想的5块相去甚远,我们再做2个表空间对比一下;
SYS@PROD>create tablespace test2
datafile'/u01/app/oracle/oradata/PROD/test2.dbf' size 10m
extent management local uniform size 80k;
2 3
Tablespace created.
SYS@PROD>create table t2(x int)tablespace test2;
Table created.
SYS@PROD>insert into t2 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
|
| 1 | LOAD TABLE CONVENTIONAL | T2 | | | |
|
--------------------------------------------------------------------------------
-
Statistics
----------------------------------------------------------
1 recursive calls
31 db block gets
2 consistent gets
0 physical reads
1916 redo size
838 bytes sent via SQL*Net to client
780 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
1 rows processed
SYS@PROD>create tablespace test3
datafile'/u01/app/oracle/oradata/PROD/test3.dbf' size 10m
extent management local uniform size 120k;
2 3
Tablespace created.
SYS@PROD>create table t3(x int)tablespace test3;
Table created.
SYS@PROD>insert into t3 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
|
| 1 | LOAD TABLE CONVENTIONAL | T3 | | | |
|
--------------------------------------------------------------------------------
-
Statistics
----------------------------------------------------------
1 recursive calls
36 db block gets
2 consistent gets
0 physical reads
2376 redo size
837 bytes sent via SQL*Net to client
780 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
1 rows processed
SYS@PROD>
SYS@PROD>show parameter db_block_size;
db_block_size integer 8192
可见每增大40k,db block gets 增大5次,而5*db_block_size=40k,其原因是对空表插入数据,分配了1个extent读了一个unifrom size数量的block(即一个extent数量的块),至于那多出来的21块暂时不明,希望大家能提供资料
我们再修改&删除一行数据
修改一块数据
SYS@PROD>update t set x=2 where x=1;
1 row updated.
Execution Plan
----------------------------------------------------------
Plan hash value: 931696821
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | UPDATE STATEMENT | | 1 | 13 | 2 (0)| 00:00:01 |
| 1 | UPDATE | T | | | | |
|* 2 | TABLE ACCESS FULL| T | 1 | 13 | 2 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("X"=1)
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
16 recursive calls
1 db block gets
34 consistent gets
4 physical reads
428 redo size
838 bytes sent via SQL*Net to client
782 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
6 sorts (memory)
0 sorts (disk)
1 rows processed
SYS@PROD>
删除一块数据
SYS@PROD>delete t where x=2;
1 row deleted.
Execution Plan
----------------------------------------------------------
Plan hash value: 3335594643
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | DELETE STATEMENT | | 1 | 13 | 2 (0)| 00:00:01 |
| 1 | DELETE | T | | | | |
|* 2 | TABLE ACCESS FULL| T | 1 | 13 | 2 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("X"=2)
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
12 recursive calls
1 db block gets
20 consistent gets
0 physical reads
288 redo size
840 bytes sent via SQL*Net to client
774 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
5 sorts (memory)
0 sorts (disk)
1 rows processed
SYS@PROD>
Oracle都只做了一次db block gets,说明了update与delete操作只读取了1个当前物理块,即修改&删除多少块就读取多少块。