左右db_block_size了解和实验

转载

mb5fd33ffac6eab 2015-09-24 12:01:00

文章标签 sql sed ide 数据 oracle 文章分类 代码人生

关于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

----------------------------------------------------------

-------------------------------------------------------------------------

-------------------------------------------------------------------------

| 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

----------------------------------------------------------

-------------------------------------------------------------------------

-------------------------------------------------------------------------

| 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

----------------------------------------------------------

-------------------------------------------------------------------------

-------------------------------------------------------------------------

| 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

----------------------------------------------------------

-------------------------------------------------------------------------

-------------------------------------------------------------------------

| 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

-------------------------------------------------------------------------------

-------------------------------------------------------------------------------

| 0 | UPDATE STATEMENT | | 1 | 13 | 2 (0)| 00:00:01 |

|* 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

-------------------------------------------------------------------------------

-------------------------------------------------------------------------------

| 0 | DELETE STATEMENT | | 1 | 13 | 2 (0)| 00:00:01 |

|* 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;

----------

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

-------------------------------------------------------------------------------

-------------------------------------------------------------------------------

| 0 | UPDATE STATEMENT | | 1 | 13 | 2 (0)| 00:00:01 |

|* 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

----------------------------------------------------------

-------------------------------------------------------------------------

-------------------------------------------------------------------------

| 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

-------------------------------------------------------------------------------

-------------------------------------------------------------------------------

| 0 | UPDATE STATEMENT | | 8 | 664 | 154 (2)| 00:00:02 |

|* 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

-------------------------------------------------------------------------------

-------------------------------------------------------------------------------

| 0 | DELETE STATEMENT | | 8 | 136 | 154 (2)| 00:00:02 |

|* 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中。

感想：

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

本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。

上一篇：经常使用的android弹出对话框

下一篇：【转】UBUNTU 下GIT的安装

提问和评论都可以，用心的回复会被更多人看到评论

发布评论

相关文章

官方博客	全部文章	热门标签	班级博客
了解我们	网站地图	意见反馈

鸿蒙开发者社区	51CTO学堂
51CTO	软考资讯

左右db_block_size了解和实验

左右db_block_size了解和实验

51CTO博客