关于对db_block_gets的理解与实验

时间:2022-04-05 08:41:32
实验

一、 自己手动创建的小表

创建一个区大小为  40k 
SYS@ORCL>show parameter db_block_size

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_block_size                        integer

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.

SYS@ORCL>create table test_db2(x int) tablespace tyger2;

Table created.

SYS@ORCL>insert into test_db2 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
         29  db block gets
          1  consistent gets
         28  physical reads
       1364  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_db2 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
        288  redo size
        677  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

结论:对于新创建的表来说,因为创建的是空表就没有对表里的空间进行分配,当插入第一
条数据时,就需要对区上的块进行空间分配和对数据字典的一些操作,就会有比较大的db_block_size。如果再次插入数据的话就基本没有对空间的分
配啥的,就会有比较少的db_block_size产生。

所以对于extent指定的区大小来说  同样的空表插入同样的数据 db_block_size 可能不同。

对插入更新、删除的实验:
SYS@ORCL>update test_db1 set x=3 where x=1;

1 row updated.

Execution Plan
----------------------------------------------------------
Plan hash value: 2185639234

-------------------------------------------------------------------------------
| Id  | Operation          | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | UPDATE STATEMENT   |          |     1 |    13 |     2   (0)| 00:00:01 |
|   1 |  UPDATE            | TEST_DB1 |       |       |            |          |
|*  2 |   TABLE ACCESS FULL| TEST_DB1 |     1 |    13 |     2   (0)| 00:00:01 |
-------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - filter("X"=1)

Note
-----
   - dynamic sampling used for this statement

Statistics
----------------------------------------------------------
         28  recursive calls
          1  db block gets
         11  consistent gets
          0  physical reads
        388  redo size
        678  bytes sent via SQL*Net to client
        565  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>delete test_db1 where x=2;

1 row deleted.

Execution Plan
----------------------------------------------------------
Plan hash value: 3135214910

-------------------------------------------------------------------------------
| Id  | Operation          | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | DELETE STATEMENT   |          |     1 |    13 |     2   (0)| 00:00:01 |
|   1 |  DELETE            | TEST_DB1 |       |       |            |          |
|*  2 |   TABLE ACCESS FULL| TEST_DB1 |     1 |    13 |     2   (0)| 00:00:01 |
-------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - filter("X"=2)

Note
-----
   - dynamic sampling used for this statement

Statistics
----------------------------------------------------------
          5  recursive calls
          1  db block gets
          9  consistent gets
          0  physical reads
        288  redo size
        678  bytes sent via SQL*Net to client
        557  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(&x);
Enter value for x: 1
old   1: insert into test_db1 values(&x)
new   1: insert into test_db1 values(1)

1 row created.

。。。。
SYS@ORCL>commit;

Commit complete.

SYS@ORCL>select * from test_db1;

X
----------
         3
         1
         2
         3
         4
         5
         6
         7
         8
         9
        19
        10
         1
        11
        12
        13
        14
        15
        16
        17
        18

21 rows selected.

SYS@ORCL>alter system flush buffer_cache;

System altered.
SYS@ORCL>update test_db1 set x=21 where x=18;

1 row updated.

Execution Plan
----------------------------------------------------------
Plan hash value: 2185639234

-------------------------------------------------------------------------------
| Id  | Operation          | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | UPDATE STATEMENT   |          |     1 |    13 |     2   (0)| 00:00:01 |
|   1 |  UPDATE            | TEST_DB1 |       |       |            |          |
|*  2 |   TABLE ACCESS FULL| TEST_DB1 |     1 |    13 |     2   (0)| 00:00:01 |
-------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - filter("X"=18)

Note
-----
   - dynamic sampling used for this statement

Statistics
----------------------------------------------------------
          5  recursive calls
          1  db block gets
          9  consistent gets
          0  physical reads
        412  redo size
        678  bytes sent via SQL*Net to client
        567  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>create table test_db1 as select * from dba_objects;

Table created.
 
 
SYS@ORCL>insert into test_db1 values('tyger','tyger','tyger',22,23,'tyger','04-SEP-14','04-SEP-14','tyger','t','t','t','t');

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
         15  db block gets
          1  consistent gets
          5  physical reads
       1144  redo size
        677  bytes sent via SQL*Net to client
        646  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>alter system flush buffer_cache;

System altered.

SYS@ORCL>update test_db1 set OBJECT_NAME='tom' where owner='tyger';

3 rows updated.

Execution Plan
----------------------------------------------------------
Plan hash value: 2185639234

-------------------------------------------------------------------------------
| Id  | Operation          | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | UPDATE STATEMENT   |          |     8 |   664 |   154   (2)| 00:00:02 |
|   1 |  UPDATE            | TEST_DB1 |       |       |            |          |
|*  2 |   TABLE ACCESS FULL| TEST_DB1 |     8 |   664 |   154   (2)| 00:00:02 |
-------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - filter("OWNER"='tyger')

Note
-----
   - dynamic sampling used for this statement

Statistics
----------------------------------------------------------
          5  recursive calls
          3  db block gets
        769  consistent gets
        687  physical reads
        824  redo size
        679  bytes sent via SQL*Net to client
        589  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          3  rows processed
SYS@ORCL>delete test_db1 where owner='tyger';

3 rows deleted.

Execution Plan
----------------------------------------------------------
Plan hash value: 3135214910

-------------------------------------------------------------------------------
| Id  | Operation          | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | DELETE STATEMENT   |          |     8 |   136 |   154   (2)| 00:00:02 |
|   1 |  DELETE            | TEST_DB1 |       |       |            |          |
|*  2 |   TABLE ACCESS FULL| TEST_DB1 |     8 |   136 |   154   (2)| 00:00:02 |
-------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - filter("OWNER"='tyger')

Note
-----
   - dynamic sampling used for this statement

Statistics
----------------------------------------------------------
          4  recursive calls
          3  db block gets
        769  consistent gets
          0  physical reads
       1064  redo size
        679  bytes sent via SQL*Net to client
        567  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          3  rows processed

结论:对于占用多个段的大表来说,可能对数据修改时 对 数据字典  或者对于区、块的分配都包含在 physical reads中。

感想:

对于生产库来说,这个值一般不会太考虑到底数字是怎么来的,因为数字都比较大,一般只在乎它的大小数量级。