關于對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 |
-------------------------------------------------------------------------
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中。
感想：
對于生產庫來說，這個值一般不會太考慮到底數字是怎么來的，因為數字都比較大，一般只在乎它的大小數量級。
聲明：本網頁內容旨在傳播知識，若有侵權等問題請及時與本網聯系，我們將在第一時間刪除處理。TEL:177 7030 7066 E-MAIL:11247931@qq.com
