左右db_block_size了解和实验

关于db_block_gets了解和实验

实验

一、自己手动创建的小表

创建一个区大小为 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中。

感想：

对于生产库来说，这个值一般不会太考虑究竟数字是怎么来的，由于数字都比较大，通常只关心它的尺寸大小。