关于db_block_size的理解和实验

关于对db_block_gets的理解与实验 实验 一、 自己手动创建的小表 创建一个区大小为 40k SYS@ORCLshow parameter db_block_size NAME TYPE VALUE ———————————— ———– —————————— db_block_size integer 819

关于对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 |
————————————————-本文来源gaodai#ma#com搞@代~码^网+————————
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

喜欢 (0)赏

[搞代码]

分享 (0)