oracle實驗記錄 （oracle consistent gets 計算）
關於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
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/12020513/viewspace-615945/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- oracle buffer gets=db block gets+consistent getsOracleBloC
- oracle實驗記錄 （計算hash join cost）Oracle
- 淺談consistent gets的計算
- 【Oracle-記憶體管理】－DB Blocks gets,Consistent gets And Physical readsOracle記憶體BloC
- oracle實驗記錄 (oracle b*tree index訪問Cost計算(1))OracleIndex
- oracle實驗記錄 （FTS的cost與基數計算）Oracle
- oracle實驗記錄 （全表掃描COST計算方法）Oracle
- oracle實驗記錄（INDEX fast full scan 的成本計算）OracleIndexAST
- oracle實驗記錄 (oracle單表選擇率與基數計算(1))Oracle
- oracle實驗記錄 (oracle單表選擇率與基數計算(2))Oracle
- oracle實驗記錄 (oracle單表選擇率與基數計算(3))Oracle
- consistent gets
- oracle實驗記錄 (oracle reset parameter)Oracle
- oracle實驗記錄 （flashback）Oracle
- oracle實驗記錄 （OMF）Oracle
- oracle實驗記錄 （NET）Oracle
- oracle實驗記錄 （audit）Oracle
- oracle實驗記錄 （分割槽表,掃描基數的計算）Oracle
- oracle實驗記錄 (oracle 資料字典)Oracle
- Oracle Data Redaction實驗記錄Oracle
- oracle實驗記錄 （block cleanout）OracleBloC
- oracle實驗記錄 （dump undo）Oracle
- oracle實驗記錄 （inlist card）Oracle
- DB Bocks gets & Consistent gets 區別
- oracle計算表的記錄數Oracle
- Oracle檔案改名實驗記錄Oracle
- oracle實驗記錄 （選擇率）Oracle
- oracle實驗記錄 （dump logfile）Oracle
- oracle實驗記錄 （事務控制）Oracle
- oracle實驗記錄 （函式index）Oracle函式Index
- oracle實驗記錄 （bigfile tablespace）Oracle
- oracle實驗記錄 （恢復-redo）Oracle
- oracle實驗記錄 （expdp/impdp使用）Oracle
- oracle實驗記錄 （transport tablespace(Rman)）Oracle
- oracle實驗記錄 （使用exp/imp）Oracle
- oracle實驗記錄 (oracle 分析shared pool(1))Oracle
- oracle實驗記錄 （oracle 分析shared pool(2)）Oracle
- oracle實驗記錄 (oracle 詳細分析redo（1）)Oracle