关于consitent gets 的 计算过程
简单说下定义
用set autotrace 时
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
9 consistent gets
0 physical reads
consistent gets是 一致读次数:从 内存获取数据
db block gets 是当前读次数从内存获取数据
physical reads是物理读次数:从disk获取数据
consistent gets:针对查询
db block gets：针对dml修改时要获取的 当前块次数
consistent gets+db block gets 就是 逻辑 读取总次数
逻辑读命中率计算1- physical reads/(consistent gets+db block gets)
命中率是 越高越好 当select 或dml执行时会查buffer cache看相应的 块是否读入buffer 如果没有就需要 从disk读取,如果已经在 内存中就可以直接使用 ，从内存中读取，从
内存中读取的 效率远比从disk读取效率要高
从sqltrace 跟踪后 TKPROF后看
call count cpu elapsed disk query current rows
------- ------ ------ -------- ----- ------ -------- ----
Parse 75 0.00 0.00 2 3 2 0
Execute 81 0.00 0.00 1 1 5 1
Fetch 153 0.00 0.00 21 355 0 110
------- ------ ------ -------- ----- ------ -------- ----
total 309 0.00 0.00 24 359 7 111
对应
disk:physical reads
query:consistent gets
current:db block gets
*********
SQL> select tablespace_name,segment_space_management from dba_tablespaces;
TABLESPACE_NAME SEGMEN
------------------------------ ------
SYSTEM MANUAL
UNDOTBS1 MANUAL
SYSAUX AUTO
TEMP MANUAL
USERS AUTO
EXAMPLE AUTO
RMAN_TS AUTO
XHTEST AUTO
XHTR AUTO
OUTLINE_TS AUTO
SMALLTS AUTO
TABLESPACE_NAME SEGMEN
------------------------------ ------
NOBACKUP AUTO
MSSMTS AUTO
已选择13行。
SQL> conn tr/a123
已连接。
SQL> create table t1(a int, b int);
表已创建。
SQL> declare
2 begin
3 for i in 1..1000 loop
4 insert into t1 values(i,i+1);
5 end loop;
6 commit;
7 end;
8 /
PL/SQL 过程已成功完成。
SQL> execute dbms_stats.gather_TABLe_stats('tr','T1');
PL/SQL 过程已成功完成。
SQL> select num_rows,blocks,empty_blocks from user_tab_statistics where table_name=
'T1';
NUM_ROWS BLOCKS EMPTY_BLOCKS
---------- ---------- ------------
1000 5 0
SQL> select tablespace_name from user_tables where table_name='T1';
TABLESPACE_NAME
------------------------------
SMALLTS
SQL> select object_id,data_object_id from dba_objects where object_name='T1';
OBJECT_ID DATA_OBJECT_ID
---------- --------------
56680 56680
SQL> set autotrace trace
SQL> select * from t1;
已选择1000行。
执行计划
----------------------------------------------------------
Plan hash value: 3617692013
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1000 | 7000 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| T1 | 1000 | 7000 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
统计信息
----------------------------------------------------------
1 recursive calls
0 db block gets
74 consistent gets
6 physical reads
0 redo size
21845 bytes sent via SQL*Net to client
1111 bytes received via SQL*Net from client
68 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1000 rows processed
SQL> alter system flush buffer_cache;
系统已更改。
SQL> select * from t1;
已选择1000行。
执行计划
----------------------------------------------------------
Plan hash value: 3617692013
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1000 | 7000 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| T1 | 1000 | 7000 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
74 consistent gets
6 physical reads~~~~~6个物理读,表示读取了6个block
0 redo size
21845 bytes sent via SQL*Net to client
1111 bytes received via SQL*Net from client
68 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1000 rows processed
SQL> select file#,block# from v$bh where bjd=56680;
FILE# BLOCK#
---------- ----------
10 204
10 206
10 207
10 208
10 205
10 203
已选择6行。
SQL> select count(DISTINCT dbms_rowid.rowid_block_number(ROWID)) from tr.t1;
COUNT(DISTINCTDBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID))
---------------------------------------------------
2
SQL> select DISTINCT dbms_rowid.rowid_block_number(ROWID) from tr.t1;
DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
------------------------------------
204~~~实际保存数据的块
208
SQL> select header_file,header_block from dba_segments where segment_name='T1';
HEADER_FILE HEADER_BLOCK
----------- ------------
10 203
SQL> select extent_id,file_id,block_id from dba_extents where segment_name='T1';
EXTENT_ID FILE_ID BLOCK_ID
---------- ---------- ----------
0 10 201
consistent reads计算=ceil(获取行数(card)/arraysize)+used blocks(FTS的话就是HWM下BLOCK)+1
分析:ceil(num_rows/arraysize) 例如取100行 每次显示到 屏幕10行 需要取10次,oracle 访问buffer cache 中相应的 hash chain 搜索需要的buffer时需要 持有 cache
buffers chains latch取完数据后释放,再取时再获取,这样需要获取10次才够显示完100行, cache buffers chains latch每获取一次就是一次逻辑读 (对于select来说就是).
+1 是多加一次segment header block scan
SQL> alter system dump datafile 10 block 203;
系统已更改。
Low HighWater Mark :
Highwater:: 0x028000d1 ext#: 0 blk#: 8 ext size: 8
SQL> variable blk# number
SQL> execute :blk#:=dbms_utility.data_block_address_block(to_number('8000d1','xxxxx
x'));
PL/SQL 过程已成功完成。
SQL> print blk#
BLK#
----------
209
HWM 块在209 ,FTS scan HWM下的块 从段头开始 block 203 -209中间 6个块,其中 SEGMENT HEADER block访问2次 ,所以一共为7次
SQL> select ceil(1000/15)+7 from dual;
CEIL(1000/15)+7
---------------
74
SQL> set arraysize 10~~~只针对本session
SQL> select * from t1;
已选择1000行。
执行计划
----------------------------------------------------------
Plan hash value: 3617692013
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1000 | 7000 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| T1 | 1000 | 7000 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
107 consistent gets
0 physical reads
0 redo size
26135 bytes sent via SQL*Net to client
1474 bytes received via SQL*Net from client
101 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1000 rows processed
SQL> select ceil(1000/10)+7 from dual;
CEIL(1000/10)+7
---------------
107
SQL> select * from t1 where a<=100;
已选择100行。
执行计划
----------------------------------------------------------
Plan hash value: 3617692013
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 100 | 700 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T1 | 100 | 700 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("A"<=100)
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
17 consistent gets
0 physical reads
0 redo size
2390 bytes sent via SQL*Net to client
484 bytes received via SQL*Net from client
11 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
100 rows processed
SQL> select ceil(100/10)+7 from dual;
CEIL(100/10)+7
--------------
17
SQL> alter table tr.t1 move;
清下环境 fulsh buffer把 与其相关的buffer 换出buffer cache
SQL> select * from t1 ;
已选择1000行。
表已更改。
执行计划
----------------------------------------------------------
Plan hash value: 3617692013
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1000 | 7000 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| T1 | 1000 | 7000 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
104 consistent gets
0 physical reads
0 redo size
26135 bytes sent via SQL*Net to client
1474 bytes received via SQL*Net from client
101 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1000 rows processed
SQL> select extent_id,file_id,block_id from dba_extents where segment_name='T1';
EXTENT_ID FILE_ID BLOCK_ID
---------- ---------- ----------
0 10 209
SQL> select header_file,header_block from dba_segments where segment_name='T1';
HEADER_FILE HEADER_BLOCK
----------- ------------
10 211
SQL> select DISTINCT dbms_rowid.rowid_block_number(ROWID) from tr.t1;
DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
------------------------------------
213
212
SQL> select num_rows,blocks,empty_blocks from dba_tab_statistics where table_name='
T1';
NUM_ROWS BLOCKS EMPTY_BLOCKS
---------- ---------- ------------
1000 5 0
SQL> select object_id,data_object_id from dba_objects where object_name='T1';
OBJECT_ID DATA_OBJECT_ID
---------- --------------
56680 56681
SQL> alter system flush buffer_cache;
系统已更改。
SQL> select file#,block#,status from v$bh where bjd=56681;
FILE# BLOCK# STATUS
---------- ---------- -------
10 212 free~~~~~~~~~~~~~~~~~~~~~都为空,之后在读进来的才准确
10 212 free
10 209 free
10 210 free
10 211 free
10 213 free
10 213 free
10 211 free
已选择8行。
SQL> select * from t1 ;
已选择1000行。
104 consistent gets
3 physical reads~~~~~~~~~~~~~物理读了3个块
SQL> select file#,block#,status from v$bh where bjd=56681;
FILE# BLOCK# STATUS
---------- ---------- -------
10 212 xcur~~~~~~~~~~~新读进来的
10 212 free
10 212 free
10 209 free
10 211 xcur~~~~~~~~~~~
10 210 free
10 211 free
10 213 xcur~~~~~~~~~~
10 213 free
10 213 free
10 211 free
已选择11行。
SQL> alter system dump datafile 10 block 211;
系统已更改。
--------------------------------------------------------
Low HighWater Mark :
Highwater:: 0x028000d6 ext#: 0 blk#: 5 ext size: 8
#blocks in seg. hdr's freelists: 0
SQL> variable blk# number
SQL> execute :blk#:=dbms_utility.data_block_address_block(to_number('8000d6','xxxxx
x'));
PL/SQL 过程已成功完成。
SQL> print blk#
BLK#
----------
214
可以看到实际hwm下块为3块 211,212,213 ,211 多访问一次 ,一共为4次
SQL> select ceil(1000/10)+4 from dual;
CEIL(1000/10)+4
---------------
104
参考
http://asktom.oracle.com/pls/asktom/f?p=100:11:6905789153373113::::P11_QUESTION_ID:880343948514