溫馨提示×
您好，登錄后才能下訂單哦！
點(diǎn)擊 登錄注冊(cè) 即表示同意《億速云用戶服務(wù)條款》
您好，登錄后才能下訂單哦！
怎樣淺析Oracle的物理讀 邏輯讀 一致性讀 當(dāng)前模式讀，針對(duì)這個(gè)問(wèn)題，這篇文章詳細(xì)介紹了相對(duì)應(yīng)的分析和解答，希望可以幫助更多想解決這個(gè)問(wèn)題的小伙伴找到更簡(jiǎn)單易行的方法。
在ORACLE數(shù)據(jù)庫(kù)中有物理讀（Physical Reads）、邏輯讀（Logical Reads）、一致性讀（Consistant Get）、當(dāng)前模式讀（DB Block Gets）等諸多概念，如果不理解或混淆這些概念的話，對(duì)你深入理解一些知識(shí)無(wú)疑是一個(gè)障礙，但是這些概念確實(shí)挺讓讓人犯暈的。下面我們總結(jié)、學(xué)習(xí)一下這方面的知識(shí)點(diǎn)。捋一捋他們的關(guān)系和特點(diǎn)，希望對(duì)你有所幫助。
物理讀（Physical Reads）
從磁盤讀取數(shù)據(jù)塊到內(nèi)存的操作叫物理讀，當(dāng)SGA里的高速緩存（Cache Buffer）里面不存在這些數(shù)據(jù)塊時(shí)，就會(huì)產(chǎn)生物理讀，另外。像全表掃描、磁盤排序等操作也可能產(chǎn)生物理讀，原因也是因?yàn)镺RACLE數(shù)據(jù)庫(kù)需要訪問(wèn)的數(shù)據(jù)塊較多，而有些數(shù)據(jù)塊不在內(nèi)存當(dāng)中，需要從磁盤讀取。
邏輯讀（Logical Reads）
概念1：邏輯讀指ORACLE從內(nèi)存讀到的數(shù)據(jù)塊數(shù)量。一般來(lái)說(shuō)， logical reads = db block gets + consistent gets
概念2：邏輯讀指的就是從Buffer Cache中讀取數(shù)據(jù)塊。按照訪問(wèn)數(shù)據(jù)塊的模式不同，可以分為當(dāng)前模式讀（Current Read）和一致性讀（Consistent Read）。
這兩個(gè)概念本質(zhì)是一樣的，只是措辭不一樣。
一致性讀（Consistant Get）
ORACLE是一個(gè)多用戶系統(tǒng)。當(dāng)一個(gè)會(huì)話開始讀取數(shù)據(jù)還未結(jié)束讀取之前，可能會(huì)有其他會(huì)話修改了它將要讀取的數(shù)據(jù)。如果會(huì)話讀取到修改后的數(shù)據(jù)，就會(huì)造成數(shù)據(jù)的不一致。一致性讀就是為了保證數(shù)據(jù)的一致性。在Buffer Cache中的數(shù)據(jù)塊上都會(huì)有最后一次修改數(shù)據(jù)塊時(shí)的SCN。如果一個(gè)事務(wù)需要修改數(shù)據(jù)塊中數(shù)據(jù)，會(huì)先在回滾段中保存一份修改前數(shù)據(jù)和SCN的數(shù)據(jù)塊，然后再更新Buffer Cache中的數(shù)據(jù)塊的數(shù)據(jù)及其SCN，并標(biāo)識(shí)其為“臟”數(shù)據(jù)。當(dāng)其他進(jìn)程讀取數(shù)據(jù)塊時(shí)，會(huì)先比較數(shù)據(jù)塊上的SCN和進(jìn)程自己的SCN。如果數(shù)據(jù)塊上的SCN小于等于進(jìn)程本身的SCN，則直接讀取數(shù)據(jù)塊上的數(shù)據(jù)；如果數(shù)據(jù)塊上的SCN大于進(jìn)程本身的SCN，則會(huì)從回滾段中找出修改前的數(shù)據(jù)塊讀取數(shù)據(jù)。通常，普通查詢都是一致性讀。
當(dāng)前模式讀（DB Block Gets）
個(gè)人覺(jué)得當(dāng)前模式讀（db block gets）是最難理解的一個(gè)概念，通常情況下db block gets 可以理解為是DML操作才會(huì)產(chǎn)生的.
當(dāng)前模式讀（db block gets）即讀取數(shù)據(jù)塊是當(dāng)前的最新數(shù)據(jù)。任何時(shí)候在Buffer Cache中都只有一份當(dāng)前數(shù)據(jù)塊。當(dāng)前讀通常發(fā)生在對(duì)數(shù)據(jù)進(jìn)行修改、刪除操作時(shí)。這時(shí)，進(jìn)程會(huì)給數(shù)據(jù)加上行級(jí)鎖，并且標(biāo)識(shí)數(shù)據(jù)為“臟”數(shù)據(jù)。current mode產(chǎn)生db block gets，一般在DML操作時(shí)產(chǎn)生，query mode產(chǎn)生consistent gets（一致性讀），一般在查詢時(shí)產(chǎn)生。他們兩個(gè)總和一般稱為邏輯讀，logical read。
有個(gè)有意思的現(xiàn)象，在ask tom或一些資料中，你會(huì)發(fā)現(xiàn)Oracle 8i在SELECT查詢當(dāng)中還能看到db block gets，但是ORACLE 10以及以上版本在SELECT語(yǔ)句中db block gets一般為0。
了解完了概念，如果你還是有一些疑問(wèn)和不解，那我們結(jié)合實(shí)際例子來(lái)理解一下這些概念吧。如下所示:
SQL> show user;
USER is "SYS"
SQL> create table test
2 as
3 select * from dba_objects;
Table created.
SQL> alter session set sql_trace=true;
System altered.
SQL> set autotrace on;
SQL> select object_type, count(1) from test
2 group by object_type;
OBJECT_TYPE COUNT(1)
------------------- ----------
EDITION 1
INDEX PARTITION 264
CONSUMER GROUP 25
SEQUENCE 223
TABLE PARTITION 240
SCHEDULE 3
QUEUE 35
RULE 1
JAVA DATA 328
...............................
...............................
43 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1435881708
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 75101 | 806K| 284 (2)| 00:00:04 |
| 1 | HASH GROUP BY | | 75101 | 806K| 284 (2)| 00:00:04 |
| 2 | TABLE ACCESS FULL| TEST | 75101 | 806K| 281 (1)| 00:00:04 |
---------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
48 recursive calls
0 db block gets
1109 consistent gets
1029 physical reads
0 redo size
1694 bytes sent via SQL*Net to client
545 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
43 rows processed
SQL> select object_type, count(1) from test
2 group by object_type;
OBJECT_TYPE COUNT(1)
------------------- ----------
EDITION 1
INDEX PARTITION 264
CONSUMER GROUP 25
SEQUENCE 223
TABLE PARTITION 240
..............................
..............................
43 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1435881708
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 75101 | 806K| 284 (2)| 00:00:04 |
| 1 | HASH GROUP BY | | 75101 | 806K| 284 (2)| 00:00:04 |
| 2 | TABLE ACCESS FULL| TEST | 75101 | 806K| 281 (1)| 00:00:04 |
---------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
1034 consistent gets
0 physical reads
0 redo size
1694 bytes sent via SQL*Net to client
545 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
43 rows processed
SQL> set autotrace off
SQL> alter session set sql_trace =false;
Session altered.
SQL> SELECT T.value
2 || '/'
3 || Lower(Rtrim(I.INSTANCE, Chr(0)))
4 || '_ora_'
5 || P.spid
6 || '.trc' TRACE_FILE_NAME
7 FROM (SELECT P.spid
8 FROM v$mystat M,
9 v$session S,
10 v$process P
11 WHERE M.statistic# = 1
12 AND S.sid = M.sid
13 AND P.addr = S.paddr) P,
14 (SELECT T.INSTANCE
15 FROM v$thread T,
16 v$parameter V
17 WHERE V.name = 'thread'
18 AND ( V.value = 0
19 OR T.thread# = To_number(V.value) )) I,
20 (SELECT value
21 FROM v$parameter
22 WHERE name = 'user_dump_dest') T;
TRACE_FILE_NAME
--------------------------------------------------------------------------------
/u01/app/oracle/diag/rdbms/gsp/gsp/trace/gsp_ora_24900.trc
SQL語(yǔ)句第一次執(zhí)行時(shí)，一致性讀（consistent gets）為1109， 物理讀（physical reads）為1029，當(dāng)前模式讀（db block gets）為0. 如果你再執(zhí)行一次上面SQL語(yǔ)句，你會(huì)發(fā)現(xiàn)物理讀（physical reads）會(huì)降低為0了，因?yàn)樯弦淮尾樵?，ORACLE已經(jīng)將表test的所有數(shù)據(jù)塊讀取到buffer cache里面了。當(dāng)然生產(chǎn)環(huán)境實(shí)際情況會(huì)復(fù)雜很多。
我們先用tkprof工具格式化一下trace文件，然后我們分析一下 out_24900.prf文件。
[oracle@DB-Server trace]$ tkprof gsp_ora_24900.trc out_24900.prf aggregate=no;
TKPROF: Release 11.2.0.1.0 - Development on Thu Sep 22 10:12:15 2016
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
在分析之前，我們先了解一下一些概念、術(shù)語(yǔ)
count = number of times OCI procedure was executed
cpu = cpu time in seconds executing
elapsed = elapsed time in seconds executing
disk = number of physical reads of buffers from disk # 物理讀
query = number of buffers gotten for consistent read # 一致性讀
current = number of buffers gotten in current mode (usually for update) # 當(dāng)前模式讀
rows = number of rows processed by the fetch or execute call
call：每次SQL語(yǔ)句的處理都分成三個(gè)部分
Parse：這步包括語(yǔ)法檢查和語(yǔ)義檢查（包括檢查是否有正確的授權(quán)和所需要用到的表、列以及其他引用到的對(duì)象是否存在）、以及將SQL語(yǔ)句轉(zhuǎn)換、生成執(zhí)行計(jì)劃等。
Execute：這步是真正的由ORACLE來(lái)執(zhí)行語(yǔ)句。對(duì)于insert、update、delete操作，這步會(huì)修改數(shù)據(jù)，對(duì)于select操作，這步就只是確定選擇的記錄。
Fetch：返回查詢語(yǔ)句中所獲得的記錄，這步只有select語(yǔ)句會(huì)被執(zhí)行。
count : 這個(gè)語(yǔ)句被parse、execute、fetch的次數(shù)。
cpu ：這個(gè)語(yǔ)句對(duì)于所有的parse、execute、fetch所消耗的cpu的時(shí)間，以秒為單位。
elapsed ：這個(gè)語(yǔ)句所有消耗在parse、execute、fetch的總的時(shí)間。
disk ：從磁盤上的數(shù)據(jù)文件中物理讀取的數(shù)據(jù)塊的數(shù)量。
query ：在一致性讀模式下，一致性讀的數(shù)量。
current ：在current模式下，即當(dāng)前模式讀下db blocks gets的數(shù)量。
rows : 所有SQL語(yǔ)句返回的記錄數(shù)目，但是不包括子查詢中返回的記錄數(shù)目。對(duì)于select語(yǔ)句，返回記錄是在fetch這步，對(duì)于insert、update、delete操作，返回記錄則是在execute這步。
如下截圖所示（圖1與圖2本是連接在一起的，由于太長(zhǎng)，分開截圖，兩張圖片有相同部分），由于我們實(shí)驗(yàn)過(guò)程中，并沒(méi)有采集統(tǒng)計(jì)信息，你會(huì)看到trac文件里面有一個(gè)動(dòng)態(tài)采樣（如果你在創(chuàng)建表，做一次統(tǒng)計(jì)信息收集，結(jié)果會(huì)有一些差別），另外，物理讀和一致性讀如下，跟上面的執(zhí)行計(jì)劃中的數(shù)據(jù)一致。
disk(物理讀) = 747+282 = 1029
query(一致性讀) = 1035+74 = 1109
繼續(xù)分析格式化的prf文件，我們會(huì)看到第二次查詢的query(一致性讀)為1034， disk(物理讀)為0上面例子，讓我們了解了物理讀、一致性讀，那么接下來(lái)看看當(dāng)前模式讀（db block gets）的例子
SQL> create table t
2 ( id number(10)
3 );
Table created.
SQL> set autotrace on;
SQL> insert into t
2 values(1000);
1 row created.
Execution Plan
----------------------------------------------------------
---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | 1 | 100 | 1 (0)| 00:00:01 |
| 1 | LOAD TABLE CONVENTIONAL | T | | | | |
---------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
1 recursive calls
7 db block gets
1 consistent gets
0 physical reads
748 redo size
836 bytes sent via SQL*Net to client
783 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> insert into t
2 values(1001);
1 row created.
Execution Plan
----------------------------------------------------------
---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | 1 | 100 | 1 (0)| 00:00:01 |
| 1 | LOAD TABLE CONVENTIONAL | T | | | | |
---------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
1 recursive calls
1 db block gets
1 consistent gets
0 physical reads
308 redo size
837 bytes sent via SQL*Net to client
783 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
1 rows processed
一致性讀如何計(jì)算呢？
關(guān)于一致性讀如何計(jì)算呢？ 我查了一下資料，一般一致性讀consistent gets ~= numrows/arraysize + blocks ，確切的說(shuō)是consistent reads計(jì)算 ~=ceil(獲取行數(shù)(card)/arraysize)+used blocks， 而且這個(gè)不是絕對(duì)等于，而是約等于的關(guān)系。 但是這個(gè)不是官方資料，而是asktom和一些技術(shù)博客的介紹，我們來(lái)驗(yàn)證看看吧
SQL> exec dbms_stats.gather_table_stats(user, 'TEST');
PL/SQL procedure successfully completed.
SQL>
SQL> set autotrace traceonly stat
SQL> select * from test;
72271 rows selected.
Statistics
----------------------------------------------------------
448 recursive calls
0 db block gets
5846 consistent gets
1031 physical reads
0 redo size
8296071 bytes sent via SQL*Net to client
53521 bytes received via SQL*Net from client
4820 SQL*Net roundtrips to/from client
3 sorts (memory)
0 sorts (disk)
72271 rows processed
SQL> /
72271 rows selected.
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
5789 consistent gets
0 physical reads
0 redo size
8296071 bytes sent via SQL*Net to client
53521 bytes received via SQL*Net from client
4820 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
72271 rows processed
SQL> set autotrace off;
SQL> set serveroutput on;
SQL> exec show_space('TEST',USER);Free Blocks............................. 0
Total Blocks............................ 1,152
Total Bytes............................. 9,437,184
Total MBytes............................ 9
Unused Blocks........................... 121
Unused Bytes............................ 991,232
Last Used Ext FileId.................... 1
Last Used Ext BlockId................... 89,344
Last Used Block......................... 7
PL/SQL procedure successfully completed.
SQL> show arraysize ;
arraysize 15
SQL> select ceil(72271/15) + 1152-121 from dual;
CEIL(72271/15)+1152-121
-----------------------
5850
SQL> SELECT COUNT(DISTINCT dbms_rowid.rowid_block_number(ROWID)) AS blocks FROM TEST;
BLOCKS
----------
1030
SQL> SELECT ceil(72271/15) + 1030 FROM DUAL;
CEIL(72271/15)+1030
-------------------
5849
不管是5849還是5850，都和5879 或5846有一點(diǎn)的出入？也就是說(shuō)上面那個(gè)公式不能用等于號(hào)，關(guān)于這個(gè)，其實(shí)在https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:880343948514 里面，你會(huì)看到這里介紹的也是一個(gè)約等于關(guān)系，而不是一個(gè)絕對(duì)等于的關(guān)系。在這里我想深入一點(diǎn)，無(wú)奈知識(shí)有限。 從上面的公式， 我們可以看到一致性讀跟arraysize的關(guān)系是蠻大的。那么我們來(lái)測(cè)試驗(yàn)證一下，先將araraysize調(diào)整為50
SQL> set autotrace off;
SQL> set arraysize 50
SQL> set autotrace traceonly stat;
SQL> select * from test;
72271 rows selected.
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
2456 consistent gets
0 physical reads
0 redo size
7668743 bytes sent via SQL*Net to client
16418 bytes received via SQL*Net from client
1447 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
72271 rows processed
SQL>
SQL> SELECT ceil(72271/50) + 1030 FROM DUAL;
CEIL(72271/50)+1030
-------------------
2476
SQL>
如上所示，一致性讀從5789降為了2456，有興趣的可以做一下實(shí)驗(yàn)。另外，由于在Oracle中，取數(shù)據(jù)最后都是從buffer cache中取，所以每出現(xiàn)一個(gè)physical reads必然會(huì)出現(xiàn)一次 logical reads. 也就是說(shuō)物理讀（physical reads）一定小于邏輯讀（logical reads=db block gets + consistent gets），也就是說(shuō)物理讀一定小于一致性讀，但是也有物理讀大于邏輯讀的情況。
PURPOSE
In some circumstances, you can find that tkprof report shows more physical reads than logical reads, which isn't the current result as the physical reads are normally included in logical reads.
SCOPE & APPLICATION
This article will be useful for the DBA's and customers who are concerned by the tuning of Requests.
Why Physical reads are greater than Logical reads
Sometimes, you can find the following content in tkprof report:
· Physical Reads = Disk (total)
· Logical Reads = Query (total) + Current (total)
call | count | cpu | elapsed | disk | query | current | rows |
------- | ------ | -------- | ---------- | ---------- | ---------- | ---------- | ---------- |
Parse | 1 | 0.67 | 1.10 | 0 | 0 | 0 | 0 |
Execute | 1 | 0.00 | 0.00 | 0 | 0 | 0 | 0 |
Fetch | 2202 | 167.48 | 678.70 | 579441 | 283473 | 17418 | 33014 |
------- | ------ | -------- | ---------- | ---------- | ---------- | ---------- | ---------- |
total | 2204 | 168.15 | 679.81 | 579441 | 283473 | 17418 | 33014 |
The 'disk' column is then greater than the 'query' + 'current' columns. This isn't usual.
To find the root cause of the problem, you must generate a 10046 event trace file level 8 and check for direct read waits in it.
In 10046 raw trace, you will find "direct path read" and "direct path write" waits like the example below:
WAIT #1: nam='direct path read' ela= 10076 p1=4 p2=29035 p3=1
with P1 = file#, P2 = start block#, P3 = num blocks
The "direct path read" waits account explains the difference between logical and physical reads.
In Oracle 9.2 and above, TKProf will print waits associated with each SQL statement in the output file.
Explanation:
The reason for more physical reads than logical reads is due to the number of direct reads block access. Direct path reads are generally used by Oracle when reading directly into PGA memory (as opposed to into the buffer cache).
They may happen on different actions:
· Sort IO on disk.
· Read by parallel Query Slaves when scanning is done.
· Blocks read by anticipation (readahead).
Such reads are done without loading blocks into the Buffer Cache. They can be single or multiblock reads.
Utilizing Direct Path Reads in this manner prevents the Oracle Buffer cache from beeing overloaded.
Oracle uses this optimisation when it considers that its not necessary to share the blocks between different sessions.
最后我們來(lái)看一個(gè)，熱表上一致性讀飆漲的案例，其實(shí)這個(gè)是Oracle 9i&10g編程藝術(shù):深入數(shù)據(jù)庫(kù)體系結(jié)構(gòu)這本書籍里面的一個(gè)案例，我們?cè)诖酥匮菀槐?，希望能加深大家?duì)一致性讀的理解，首先準(zhǔn)備測(cè)試數(shù)據(jù)環(huán)境
SQL> show user;
USER is "TEST"
SQL> create table t( x int);
Table created.
SQL> insert into t values(1);
1 row created.
SQL> commit;
Commit complete.
SQL> exec dbms_stats.gather_table_stats(user, 'T');
PL/SQL procedure successfully completed.
SQL> set autotrace on statistics;
SQL> select * from t;
X
----------
1
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
7 consistent gets
0 physical reads
0 redo size
523 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL>
如上所示，一般情況下一致性讀為7，但是我們?cè)谝粋€(gè)會(huì)話窗口準(zhǔn)備執(zhí)行下面SQL，頻繁修改表T
SQL> begin
2 for i in 1 .. 100000
3 loop
4 update t set x=x+1;
5 commit;
6 end loop;
7 end;
8 /
PL/SQL procedure successfully completed.
在上面會(huì)話執(zhí)行的同時(shí)，我們?cè)诹硗庖粋€(gè)會(huì)話窗口馬上執(zhí)行下面SQL，你會(huì)看到一致性讀飆漲。
SQL> alter session set isolation_level=serializable;
Session altered.
SQL> set autotrace on statistics;
SQL> select * from t;
X
----------
1
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
23681 consistent gets
0 physical reads
0 redo size
523 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
將會(huì)話設(shè)置為使用SERIALIZABLE 隔離級(jí)別，這樣無(wú)論在會(huì)話中運(yùn)行多少次查詢，都將得到事務(wù)開始時(shí)刻的查詢結(jié)果。
Oracle 9i&10g編程藝術(shù):深入數(shù)據(jù)庫(kù)體系結(jié)構(gòu)
在sql調(diào)優(yōu)的時(shí)候，一個(gè)關(guān)鍵指標(biāo)就是consistent gets，如果這個(gè)指標(biāo)很低，一般認(rèn)為sql語(yǔ)句執(zhí)行還是很高效的，反之效率會(huì)很低。但是這個(gè)指標(biāo)我們知之甚少，對(duì)于這個(gè)指標(biāo)的計(jì)算方式我們也是懵懵懂懂。對(duì)于邏輯讀來(lái)說(shuō)，一般都是基于Logical Reads= Consistent Gets + DB Block Gets
如果我們知道logical reads是1000，我們可能錯(cuò)誤地認(rèn)為查詢讀取了1000*8k(約為8M)
看了博客https://viveklsharma.wordpress.com/2010/03/04/consistent-gets-myth/后，發(fā)現(xiàn)自己的認(rèn)識(shí)是錯(cuò)誤的，也按捺不住在本地測(cè)試了一把，受益匪淺。
首先我們來(lái)創(chuàng)建一個(gè)表，數(shù)據(jù)量為2000條。
n1@TEST11G> create table test_consistent_get as select * from all_objects where rownum between 1 and 2000;
Table created.
然后收集統(tǒng)計(jì)信息
n1@TEST11G> exec dbms_stats.gather_table_stats(user,'TEST_CONSISTENT_GET');
PL/SQL procedure successfully completed.
查看相應(yīng)的數(shù)據(jù)塊為30個(gè)
n1@TEST11G> select num_rows,blocks,table_name,last_analyzed,global_stats from user_tables where table_name='TEST_CONSISTENT_GET';
NUM_ROWS BLOCKS TABLE_NAME LAST_ANAL GLO
---------- ---------- ------------------------------ --------- ---
2000 30 TEST_CONSISTENT_GET 20-APR-15 YES
n1@TEST11G> set autot trace
我們來(lái)看看執(zhí)行計(jì)劃，很明顯走了一個(gè)全表掃描。但是我們需要關(guān)注的是統(tǒng)計(jì)信息中的consistent gets
n1@TEST11G> select * from test_consistent_get;
Execution Plan
----------------------------------------------------------
Plan hash value: 1444268095
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2000 | 164K| 10 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| TEST_CONSISTENT_GET | 2000 | 164K| 10 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
163 consistent gets
0 physical reads
0 redo size
199754 bytes sent via SQL*Net to client
1883 bytes received via SQL*Net from client
135 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
2000 rows processed
可以看到這個(gè)表占用的數(shù)據(jù)塊為30，但是consistent gets卻為163，很顯然不是說(shuō)這個(gè)全表掃描向cache里讀入了163*8k的數(shù)據(jù)
我們可以通過(guò)rowid來(lái)得到對(duì)應(yīng)的數(shù)據(jù)塊和其中的數(shù)據(jù)情況
n1@TEST11G> select dbms_rowid.ROWID_BLOCK_NUMBER(rowid) blkno, count(*) cnt
from test_consistent_get
group by dbms_rowid.ROWID_BLOCK_NUMBER(rowid) order by 1;
BLKNO CNT
---------- ----------
263827 88
263828 84
263829 81
263830 76
263831 81
263832 80
263833 82
263834 77
263835 73
263836 78
263837 79
263838 79
263839 81
263841 82
263842 77
263843 81
263844 80
263845 81
263846 78
263847 78
263848 76
263849 78
263850 78
263851 76
263852 81
263853 15
26 rows selected.
可以通過(guò)rowid得到相關(guān)的數(shù)據(jù)塊為26個(gè)。查看段頭，發(fā)現(xiàn)對(duì)應(yīng)的數(shù)據(jù)塊是263826是不在上面的rowid對(duì)應(yīng)的數(shù)據(jù)塊范圍內(nèi)的。
n1@TEST11G> select header_block,blocks ,extents from dba_segments where segment_name='TEST_CONSISTENT_GET';
HEADER_BLOCK BLOCKS EXTENTS
------------ ---------- ----------
263826 32 4
對(duì)應(yīng)的區(qū)和數(shù)據(jù)塊信息如下：
n1@TEST11G> select EXTENT_ID, FILE_ID, BLOCK_ID, BLOCKS from dba_extents where SEGMENT_NAME='TEST_CONSISTENT_GET';
EXTENT_ID FILE_ID BLOCK_ID BLOCKS
---------- ---------- ---------- ----------
0 4 263824 8
1 4 263832 8
2 4 263840 8
3 4 263848 8
下面的語(yǔ)句可以算出對(duì)于每個(gè)數(shù)據(jù)塊對(duì)應(yīng)的consistent gets的值。
n1@TEST11G>
variable b1 number;
exec :b1:=15;
compute sum of total_cnt on report
compute sum of touch_cnt on report
break on report
select blkno, total_cnt, final_cnt, rows_remaining,
case when rows_remaining=0 then touch_cnt+1 else touch_cnt end touch_cnt
from (
select blkno, total_cnt, final_cnt, rows_remaining,
case when total_cnt = final_cnt then ceil(final_cnt/:b1) else ceil(final_cnt/:b1)+1 end touch_cnt
from (
select blkno, cnt total_cnt,
case when rownum=1 or lag(rows_remaining) over (order by blkno)=0
then cnt else (cnt-(:b1-lag(rows_remaining) over (order by blkno))) end final_cnt,
rows_remaining
from (
select blkno, cnt, rr,
lead(rr) over(order by blkno) next_rr,
lead(blkno) over(order by blkno) next_blk,
ceil(rr/:b1) touch_cnt,
mod(rr,:b1) rows_remaining
from (
select dbms_rowid.ROWID_BLOCK_NUMBER(rowid) blkno, count(*) cnt,
sum(count(*)) over(order by dbms_rowid.ROWID_BLOCK_NUMBER(rowid)) rr
from test_consistent_get
group by dbms_rowid.ROWID_BLOCK_NUMBER(rowid) order by 1))));
BLKNO TOTAL_CNT FINAL_CNT ROWS_REMAINING TOUCH_CNT
---------- ---------- ---------- -------------- ----------
263827 88 88 13 6
263828 84 82 7 7
263829 81 73 13 6
263830 76 74 14 6
263831 81 80 5 7
263832 80 70 10 6
263833 82 77 2 7
263834 77 64 4 6
263835 73 62 2 6
263836 78 65 5 6
263837 79 69 9 6
263838 79 73 13 6
263839 81 79 4 7
263841 82 71 11 6
263842 77 73 13 6
263843 81 79 4 7
263844 80 69 9 6
263845 81 75 0 7
263846 78 78 3 6
263847 78 66 6 6
263848 76 67 7 6
263849 78 70 10 6
263850 78 73 13 6
263851 76 74 14 6
263852 81 80 5 7
263853 15 5 5 2
---------- ----------
sum 2000 159
可以看到對(duì)于這個(gè)全表掃描的場(chǎng)景，consistent gets不是衡量對(duì)于cache的數(shù)據(jù)塊數(shù)而是次數(shù)。
比如對(duì)于上面的數(shù)據(jù)塊263827 ，數(shù)據(jù)條數(shù)為88條，arraysize為15，則可以簡(jiǎn)單說(shuō)明一下是如何計(jì)算這個(gè)consistent gets值的。
對(duì)于數(shù)據(jù)塊263827,放入PGA中，得到了15行，這個(gè)時(shí)候可以理解為consistent gets=1
對(duì)于數(shù)據(jù)塊263827,再次從PGA中得到，得到了15行，這個(gè)時(shí)候consistent gets=2
依次類推
對(duì)于數(shù)據(jù)塊263827,再次從PGA中得到，得到了13行，這個(gè)時(shí)候consistent gets=6
或者也可以基本按照這個(gè)公式來(lái)計(jì)算,數(shù)據(jù)行數(shù)/arraysize+數(shù)據(jù)塊數(shù)=consistent gets
比如這個(gè)例子，2000/15+30 大概是163.3左右，所以163還是靠譜的。
對(duì)于arraysize未20,30,的情況下，相應(yīng)的consistent gets也會(huì)減少。簡(jiǎn)單模擬一下。
n1@TEST11G> set arraysize 20
n1@TEST11G> set autot trace exp stat
n1@TEST11G> select *from test_consistent_get;
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
128 consistent gets
0 physical reads
0 redo size
195334 bytes sent via SQL*Net to client
1509 bytes received via SQL*Net from client
101 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
2000 rows processed
n1@TEST11G> set autot off
n1@TEST11G> select 2000/20+30 from dual;
2000/20+30
----------
130
n1@TEST11G> set arraysize 30
n1@TEST11G> set autot trace stat
n1@TEST11G> select *from test_consistent_get;
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
96 consistent gets
0 physical reads
0 redo size
191044 bytes sent via SQL*Net to client
1146 bytes received via SQL*Net from client
68 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
2000 rows processed
n1@TEST11G> set autot off
n1@TEST11G> select 2000/30+30 from dual;
2000/30+30
----------
96.6666667
關(guān)于怎樣淺析Oracle的物理讀 邏輯讀 一致性讀 當(dāng)前模式讀問(wèn)題的解答就分享到這里了，希望以上內(nèi)容可以對(duì)大家有一定的幫助，如果你還有很多疑惑沒(méi)有解開，可以關(guān)注億速云行業(yè)資訊頻道了解更多相關(guān)知識(shí)。
免責(zé)聲明：本站發(fā)布的內(nèi)容（圖片、視頻和文字）以原創(chuàng)、轉(zhuǎn)載和分享為主，文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng)，如果涉及侵權(quán)請(qǐng)聯(lián)系站長(zhǎng)郵箱：is@yisu.com進(jìn)行舉報(bào)，并提供相關(guān)證據(jù)，一經(jīng)查實(shí)，將立刻刪除涉嫌侵權(quán)內(nèi)容。
億速云公眾號(hào)
手機(jī)網(wǎng)站二維碼