oracle 物理读,逻辑读的理解
1.物理讀(physical read)
當數(shù)據(jù)塊第一次讀取到,就會緩存到buffer cache 中,而第二次讀取和修改該數(shù)據(jù)塊時就在內(nèi)存buffer cache 了 以下是例子:
1.1? 第一次讀取:
C:\Documents and Settings\Paul Yi>sqlplus "/as sysdba"
SQL*Plus: Release 9.2.0.4.0 - Production on Thu Feb 28 09:32:04?2008
Copyright (c) 1982, 2002, Oracle Corporation.? All rights reserved.
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production
SQL> set autotrace traceonly
SQL> select * from test;
Execution Plan
----------------------------------------------------------
?? 0????? SELECT STATEMENT ptimizer=CHOOSE (Cost=2 Card=4 Bytes=8)
?? 1??? 0?? TABLE ACCESS (FULL) OF 'TEST' (Cost=2 Card=4 Bytes=8)
?
Statistics
----------------------------------------------------------
??????? 175? recursive calls
????????? 0? db block gets
???????? 24? consistent gets
?????????9? physical reads???????????? --9個物理讀
????????? 0? redo size
??????? 373? bytes sent via SQL*Net to?client
??????? 503? bytes received via SQL*Net from client
????????? 2? SQL*Net roundtrips to/from client
????????? 2? sorts (memory)
????????? 0? sorts (disk)
????????? 1? rows processed
1.2? 第二次讀取
SQL> select * from test;
Execution Plan
----------------------------------------------------------
?? 0????? SELECT STATEMENT ptimizer=CHOOSE (Cost=2 Card=4 Bytes=8)
?? 1??? 0?? TABLE ACCESS (FULL) OF 'TEST' (Cost=2 Card=4 Bytes=8)
?
Statistics
----------------------------------------------------------
????????? 0? recursive calls
????????? 0? db block gets
????????? 7? consistent gets
????????? 0? physical reads???? --沒有發(fā)生物理讀了,直接從buffer cache 中讀取了
????????? 0? redo size
??????? 373? bytes sent via SQL*Net to client
??????? 503? bytes received via SQL*Net from client
????????? 2? SQL*Net roundtrips to/from client
????????? 0? sorts (memory)
????????? 0? sorts (disk)
????????? 1? rows processed
1.3? 數(shù)據(jù)塊被重新讀入buffer cache ,這種發(fā)生在
如果有新的數(shù)據(jù)需要被讀入Buffer Cache中，而Buffer Cache又沒有足夠的空閑空間，Oracle就根據(jù)LRU算法將LRU鏈表中LRU端的數(shù)據(jù)置換出去。當這些數(shù)據(jù)被再次訪問到時，需要重新從磁盤讀入。
SQL> alter session set events 'immediate trace name flush_cache';--清空數(shù)據(jù)緩沖區(qū)
Session altered.
SQL> select * from test;
Execution Plan
----------------------------------------------------------
?? 0????? SELECT STATEMENT ptimizer=CHOOSE (Cost=2 Card=4 Bytes=8)
?? 1??? 0?? TABLE ACCESS (FULL) OF 'TEST' (Cost=2 Card=4 Bytes=8)
?
Statistics
----------------------------------------------------------
????????? 0? recursive calls
????????? 0? db block gets
????????? 7? consistent gets
????????? 6? physical reads?? --又重新發(fā)生了物理讀
????????? 0? redo size
??????? 373? bytes sent via SQL*Net to client
??????? 503? bytes received via SQL*Net from client
????????? 2? SQL*Net roundtrips to/from client
????????? 0? sorts (memory)
????????? 0? sorts (disk)
????????? 1? rows processed
2.邏輯讀(buffer read)
邏輯讀指的就是從（或者視圖從）Buffer Cache中讀取數(shù)據(jù)塊。按照訪問數(shù)據(jù)塊的模式不同，可以分為即時讀（Current Read）和一致性讀（Consistent Read）。注意：邏輯IO只有邏輯讀，沒有邏輯寫。
- 即時讀
即時讀即讀取數(shù)據(jù)塊當前的最新數(shù)據(jù)。任何時候在Buffer Cache中都只有一份當前數(shù)據(jù)塊。即時讀通常發(fā)生在對數(shù)據(jù)進行修改、刪除操作時。這時，進程會給數(shù)據(jù)加上行級鎖，并且標識數(shù)據(jù)為“臟”數(shù)據(jù)。
SQL> select * from test for update;
Execution Plan
----------------------------------------------------------
?? 0????? SELECT STATEMENT ptimizer=CHOOSE (Cost=2 Card=4 Bytes=8)
?? 1??? 0?? FOR UPDATE
?? 2??? 1???? TABLE ACCESS (FULL) OF 'TEST' (Cost=2 Card=4 Bytes=8)
?
Statistics
----------------------------------------------------------
????????? 0? recursive calls
?????????1? db block gets
???????? 14? consistent gets
????????? 0? physical reads
??????? 252? redo size
??????? 386? bytes sent via SQL*Net to client
??????? 503? bytes received via SQL*Net from client
????????? 2? SQL*Net roundtrips to/from client
????????? 0? sorts (memory)
????????? 0? sorts (disk)
????????? 1? rows processed
SQL>
- 一致性讀
Oracle是一個多用戶系統(tǒng)。當一個會話開始讀取數(shù)據(jù)還未結(jié)束讀取之前，可能會有其他會話修改它將要讀取的數(shù)據(jù)。如果會話讀取到修改后的數(shù)據(jù)，就會造成數(shù)據(jù)的不一致。一致性讀就是為了保證數(shù)據(jù)的一致性。在Buffer Cache中的數(shù)據(jù)塊上都會有最后一次修改數(shù)據(jù)塊時的SCN。如果一個事務(wù)需要修改數(shù)據(jù)塊中數(shù)據(jù)，會先在回滾段中保存一份修改前數(shù)據(jù)和SCN的數(shù)據(jù)塊，然后再更新Buffer Cache中的數(shù)據(jù)塊的數(shù)據(jù)及其SCN，并標識其為“臟”數(shù)據(jù)。當其他進程讀取數(shù)據(jù)塊時，會先比較數(shù)據(jù)塊上的SCN和自己的SCN。如果數(shù)據(jù)塊上的SCN小于等于進程本身的SCN，則直接讀取數(shù)據(jù)塊上的數(shù)據(jù)；如果數(shù)據(jù)塊上的SCN大于進程本身的SCN，則會從回滾段中找出修改前的數(shù)據(jù)塊讀取數(shù)據(jù)。通常，普通查詢都是一致性讀。
下面這個例子幫助大家理解一下一致性讀：
會話1中：
SQL> select * from test;
??????? ID
??????? ----------
????????? 1000
SQL> update test set id=2000;
1 row updated.
會話2中:
SQL> set autotrace on
?? SQL> select * from test;
??????? ID
?????? ----------
????????? 1000
Execution Plan
----------------------------------------------------------
?? 0????? SELECT STATEMENT ptimizer=CHOOSE (Cost=2 Card=4 Bytes=8)
?? 1??? 0?? TABLE ACCESS (FULL) OF 'TEST' (Cost=2 Card=4 Bytes=8)
?
Statistics
----------------------------------------------------------
????????? 0? recursive calls
????????? 0? db block gets
?????????9? consistent gets??? 沒有事物做update時 是 7 consistent gets?說明多了2個? consistent gets????這2個是要從回滾段中獲取的?????
????????? 0? physical reads
???????? 52? redo size
??????? 373? bytes sent via SQL*Net to client
??????? 503? bytes received via SQL*Net from client
????????? 2? SQL*Net roundtrips to/from client
????????? 0? sorts (memory)
????????? 0? sorts (disk)
????????? 1? rows processed
SQL>
?
與50位技術(shù)專家面對面20年技術(shù)見證，附贈技術(shù)全景圖總結(jié)
以上是生活随笔為你收集整理的oracle 物理读,逻辑读的理解的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。
- 上一篇： oracle转义字符
- 下一篇： 如何恢复XP系统中原来的Administ