文章/答案/技术大牛

发布

社区首页 >专栏 >左右db_block_size了解和实验

左右db_block_size了解和实验

全栈程序员站长

发布于 2022-07-06 08:59:19

3110

文章被收录于专栏：全栈程序员必看全栈程序员必看

大家好，又见面了，我是全栈君

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

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

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

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

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

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

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

感想：

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

发布者：全栈程序员栈长，转载请注明出处：https://javaforall.cn/116932.html原文链接：https://javaforall.cn

本文参与腾讯云自媒体同步曝光计划，分享自作者个人站点/博客。

原始发表：2022年1月1，如有侵权请联系 cloudcommunity@tencent.com 删除

sql